Post reply

Help! Script to select tables and fields with a specific value

  • July 26, 2007 at 12:45 pm

    #177685

    I need a script that will find all the tables with a specific column name that contains a specific value

    ie.  this column "XYZ" shows up in 280 tables in my database, I want to find which tables contain column "XYZ" with the value of "23".

    Any Ideas??

  • July 27, 2007 at 5:50 am

    #722823

    How is this:

    SET NOCOUNT ON

    DECLARE @Schema VARCHAR(100), @Table VARCHAR(100), @Column VARCHAR(100), @Value VARCHAR(100), @sql VARCHAR(1000)

    SET @Column = 'CountryName'

    SET @Value = 'United States of America'

    DECLARE ColCur CURSOR FOR

     SELECT Table_Schema, Table_Name FROM Information_Schema.Columns WHERE Column_Name = @Column

    OPEN ColCur

    FETCH NEXT FROM ColCur INTO @Schema, @Table

    WHILE @@Fetch_Status = 0

    BEGIN

     SET @sql = 'IF EXISTS(SELECT [' + @Column + '] FROM [' + @Schema + '].[' + @Table + '] WHERE [' + @Column + '] = ''' + @Value + ''') PRINT ''[' +  @Schema + '].[' + @Table + ']'''

     EXEC(@SQL)

     FETCH NEXT FROM ColCur INTO @Schema, @Table

    END

    CLOSE ColCur

    DEALLOCATE ColCur

  • July 27, 2007 at 7:37 am

    #722863

    I don't really understand it but it works.  Thanks a ton!!

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

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