Find Text in all columns of all tables in a Database

  • Comments posted to this topic are about the item Find Text in all columns of all tables in a Database

  • You helped me out a bunch with this! Thank you much!

  • i was really hoping that i would find an article on this subject in SQLServerCentral and thank goodness i found your article, great!

    you just saved me many hours of hunting!

    excellent post!

  • I made some minor changes to the code since i had two problems. First, if the schema where not dbo it did not work and if the text i searched for where to long or to long in an varchar field it did not find anything: The changed code looks like this:

    set nocount on

    DECLARE @TEXT VARCHAR(500)

    SET @TEXT = 'test nisse, test malte, hejsan på dig du galde . vad gör dunu då'

    DECLARE @TABLES TABLE([id] INT IDENTITY(1,1), TableName VARCHAR(500),

    SchemaName varchar(255),ColumnName VARCHAR(500))

    INSERT INTO @TABLES(TableName, SchemaName,ColumnName)

    SELECT O.[NAME], ss.name,C.[NAME]

    FROM SYSOBJECTS O

    JOIN SYSCOLUMNS C

    ON C.ID = O.ID

    join sys.objects SO

    on so.object_id=o.id

    join sys.schemas SS

    on ss.schema_id= so.schema_id

    WHERE O.XTYPE = 'U'

    AND C.XTYPE NOT IN

    (

    127 --bigint

    , 173 --binary

    , 104 --bit

    , 61 --datetime

    , 106 --decimal

    , 62 --float

    , 34 --image

    , 56 --int

    , 60 --money

    , 108 --numeric

    , 59 --real

    , 58 --smalldatetime

    , 52 --smallint

    , 122 --smallmoney

    , 189 --timestamp

    , 48 --tinyint

    , 36 --uniqueidentifier

    , 165 --varbinary

    )

    ORDER BY O.[NAME], C.[NAME]

    IF EXISTS (SELECT NAME FROM TEMPDB.DBO.SYSOBJECTS

    WHERE NAME LIKE '#TMPREPORT%')

    BEGIN

    DROP TABLE #TMPREPORT

    END

    CREATE TABLE #TMPREPORT(COUNTER INT, TABLENAME VARCHAR(500),

    COLUMNNAME VARCHAR(500))

    DECLARE @CNTR bigINT, @POS bigINT, @TableName VARCHAR(500),

    @SchemaName varchar(255),@ColumnName VARCHAR(500), @SQL VARCHAR(8000)

    SELECT @POS = 1, @CNTR = MAX([ID]), @TableName = '', @ColumnName = ''

    FROM @TABLES

    WHILE @POS 0

    DROP TABLE #TMPREPORT

    Thanks for the code! Really nice and it did the trick fo rme when trying to figure out how VM ware really link their tables.

  • Thanks for the script.

  • ...I'm hoping this will be very useful. Thanks again.

Viewing 6 posts - 1 through 5 (of 5 total)

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