Post reply

Searching for an ID in all my tables

  • June 23, 2003 at 12:58 pm

    #82391

    I'm trying to locate one ID in all my tables within a database for instance

    SELECT * FROM 'ALL MY TABLES' WHERE MembID = 1234

  • June 23, 2003 at 3:42 pm

    #462709

    one way would be to use the sp_MSForEachTable stored proc :

    exec sp_MSForEachTable 'SELECT * FROM ? where MembId=1234'

    this will execute the select statement against all the tables in the database (if any table does not contain the specified column then an error is generated)...

    the other way is to use a cursor to get the names of all the tables with the specified columns and use dynamic SQL to get the results :

    Declare @sql nvarchar(500)

    Declare @Param_TabName nvarchar(100)

    DECLARE Table_CUR CURSOR FOR

    Select ISC.Table_Name from INFORMATION_SCHEMA.COLUMNS ISC where

    ISC.Column_Name = 'MembID'

    FOR READ ONLY

    OPEN Table_CUR

    FETCH NEXT FROM Table_CUR INTO @Param_TabName

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @sql = N'Select * from '+@Param_TabName+' where MembID = 1234'

    exec sp_executesql @sql

    FETCH NEXT FROM Table_CUR INTO @Param_TabName

    END

    CLOSE Table_CUR

    DEALLOCATE Table_CUR

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply