Query to get a specific word....

  • Hello,

    Can anyone send me one query so i can get a specific word inside a table :blink:

    I've got one SQL Server engine that is new "for me" and we virtualize that environment so we can make any tests to migrate this environment.

    The point is:

    I think that anywhere in the code/data/whatever they store the machine name :sick: i export all the objects of the database to see if i can catch the name inside that code but .....there's no indication.

    So i must check inside all tables if that name appears inside any field :w00t:

    Please send me any query so i can check this.....please!!!

    Thanks and regards,

    JMSM 😉

  • Here is some code that I wrote several years ago to search every field in every table on a given database. It is not pretty and it is NOT FAST!!!! Make sure you don't run this during a high peak time. If you can copy your db to a dev server that is even better. This is a really slow cursor loaded with dynamic sql but it does a pretty solid job of finding what you are looking for.

    declare @table_name varchar(2000)

    declare @sSQL nvarchar(4000)

    declare @result varchar(20)

    declare @column_name varchar(2000)

    declare @SearchVal varchar(200)

    set @SearchVal = '%your search val here%'

    declare @ColName varchar (250)

    set @ColName = '%use this if you want to limit to a naming convention on the columns to search (i.e. email)%'

    declare SearchList cursor for

    select distinct so.name,sc.name from syscolumns sc

    inner join sysobjects so on sc.id = so.id

    where sc.name like @ColName

    and so.type = 'U'

    open SearchList

    fetch next from SearchList into @table_name, @column_name

    while(@@fetch_status = 0)

    begin

    select @sSQL = 'if exists (select ''' + @table_name + ''' as TableName,' + @column_name + ' from ' + @table_name + ' where ' + @column_name + ' like ''' + @SearchVal + ''') select ''' + @table_name + ''' as TableName,' + @column_name + ' from ' + @table_name + ' where ' + @column_name + ' like ''' + @SearchVal + ''''

    exec sp_executesql @sSQL

    --select @ssql

    fetch next from SearchList into @table_name, @column_name

    end

    close SearchList

    deallocate SearchList

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Hi Sean,

    I'm sorry but i'm using your code to test and ......i really dont know if i'm using it in the right way.

    I create a table in a specific database and name it as 'TblMyName', after create it i insert in this table the following value 'MyNameJMSM', then i pass into the variable @SearchVal = 'MyNameJMSM' value, when i execute the following code it doesn't return to me any value. Am i using your code in the right way?

    Sorry!!!

    Thanks and regards,

    JMSM 😉

    declare @table_name varchar(2000)

    declare @sSQL nvarchar(4000)

    declare @result varchar(20)

    declare @column_name varchar(2000)

    declare @SearchVal varchar(200)

    set @SearchVal = 'MyNameJMSM'

    declare @ColName varchar (250)

    set @ColName = '%use this if you want to limit to a naming convention on the columns to search (i.e. email)%'

    declare SearchList cursor for

    select distinct so.name,sc.name from syscolumns sc

    inner join sysobjects so on sc.id = so.id

    where sc.name like @ColName

    and so.type = 'U'

    open SearchList

    fetch next from SearchList into @table_name, @column_name

    while(@@fetch_status = 0)

    begin

    select @sSQL = 'if exists (select ''' + @table_name + ''' as TableName,' + @column_name + ' from ' + @table_name + ' where ' + @column_name + ' like ''' + @SearchVal + ''') select ''' + @table_name + ''' as TableName,' + @column_name + ' from ' + @table_name + ' where ' + @column_name + ' like ''' + @SearchVal + ''''

    exec sp_executesql @sSQL

    --select @ssql

    fetch next from SearchList into @table_name, @column_name

    end

    close SearchList

    deallocate SearchList

  • If you want to search every column without a filter just remove the "where sc.name like @ColName" in the cursor defintion. With the way you have it you are only looking in tables with a column named "like '%use this if you want to limit to a naming convention on the columns to search (i.e. email)%'". Make sense?

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Sean you beat me to it.

    here is my version anyway figured I'd post it since I spent the time. this may come in handy for searching our SSIS config tables for certain values anyway:

    I also filter out the non-character fields

    Just enter your DB Name and Search Value

    USE MyDB

    go

    DECLARE @s-2 SYSNAME

    ,@t SYSNAME

    ,@c SYSNAME

    ,@sql NVARCHAR(2000)

    ,@Search NVARCHAR(200) = 'Search Value'

    DECLARE c CURSOR

    FOR

    SELECT s.name AS [Schema]

    ,t.name AS [Table]

    ,c.name AS [Column]

    FROM sys.schemas s

    JOIN sys.tables t ON s.schema_id = t.schema_id

    JOIN sys.columns c ON t.object_id = c.object_id

    -- grab all columns that could contain text

    WHERE c.system_type_id IN (98, 167, 175, 231, 239, 231)

    ORDER BY s.name

    ,t.name

    ,c.column_id

    OPEN c

    FETCH NEXT FROM c INTO @s-2, @t, @C

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @sql = 'If exists(select 1 from ' + @s-2 + '.' + @t + ' where ' + @C

    + ' like ' + '''%' + @Search + '%'') select ''' + @s-2 + '.' + @t

    + ' - ' + @C + ''', * from ' + @s-2 + '.' + @t + ' where ' + @C

    + ' like ' + '''%' + @Search + '%'''

    --PRINT @sql

    EXECUTE sp_executesql @sql

    FETCH NEXT FROM c INTO @s-2, @t, @C

    END

    CLOSE c

    DEALLOCATE c

  • Hello again,

    Sean,

    I've comment the line as you tell me to do it but and uncomment the line select @sSQL but i really dont have any usefull results.

    I only want that the final output be:

    TblMyNameJMSM MyNameJMSM

    Is there anything that i should change in the code.

    Thanks and regards,

    JMSM 😉

    declare @table_name varchar(2000)

    declare @sSQL nvarchar(4000)

    declare @result varchar(20)

    declare @column_name varchar(2000)

    declare @SearchVal varchar(200)

    set @SearchVal = 'MyNameJMSM'

    declare @ColName varchar (250)

    /*

    set @ColName = '%use this if you want to limit to a naming convention on the columns to search (i.e. email)%'

    */

    declare SearchList cursor for

    select distinct so.name,sc.name from syscolumns sc

    inner join sysobjects so on sc.id = so.id

    -- where sc.name like @ColName

    -- and so.type = 'U'

    open SearchList

    fetch next from SearchList into @table_name, @column_name

    while(@@fetch_status = 0)

    begin

    -- select @sSQL = 'if exists (select ''' + @table_name + ''' as TableName,' + @column_name + ' from ' + @table_name + ' where ' + @column_name + ' like ''' + @SearchVal + ''') select ''' + @table_name + ''' as TableName,' + @column_name + ' from ' + @table_name + ' where ' + @column_name + ' like ''' + @SearchVal + ''''

    exec sp_executesql @sSQL

    select @sSQL

    fetch next from SearchList into @table_name, @column_name

    end

    close SearchList

    deallocate SearchList

  • You commented out the line that set @sSQL. You also commented out the the so.type = 'U' in the cursor declaration. This second one won't actually cause any problems but you will have a LOT more columns to sift through because you only want to search User Tables and not system tables. In putting together my example for you I realized that you may need to exclude some datatypes that can't be compared using like. I wrote this originally in sql2000 and we never used the text datatype so I didn't have to worry about it.

    See if this make some sense.

    create table myTest

    (

    col1 varchar(50)

    )

    go

    insert myTest select 'MyNameJMSM'

    go

    declare @table_name varchar(2000)

    declare @sSQL nvarchar(4000)

    declare @result varchar(20)

    declare @column_name varchar(2000)

    declare @SearchVal varchar(200)

    set @SearchVal = 'MyNameJMSM'

    declare @ColName varchar (250)

    /*

    set @ColName = '%use this if you want to limit to a naming convention on the columns to search (i.e. email)%'

    */

    --select * from systypes --you can use this if you find other datatypes you want to exclude

    declare SearchList cursor for

    select distinct so.name,sc.name from syscolumns sc

    inner join sysobjects so on sc.id = so.id

    -- where sc.name like @ColName

    and so.type = 'U'

    and sc.xtype <> 241 --xml

    open SearchList

    fetch next from SearchList into @table_name, @column_name

    while(@@fetch_status = 0)

    begin

    select @sSQL = 'if exists (select ''' + @table_name + ''' as TableName,' + @column_name + ' from ' + @table_name + ' where ' + @column_name + ' like ''' + @SearchVal + ''') select ''' + @table_name + ''' as TableName,' + @column_name + ' from ' + @table_name + ' where ' + @column_name + ' like ''' + @SearchVal + ''''

    exec sp_executesql @sSQL

    --select @sSQL

    fetch next from SearchList into @table_name, @column_name

    end

    close SearchList

    deallocate SearchList

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • forgot to mention that his assumes only a single schema. If you have multiple schemas you will need to tweak this a bit to accomodate.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • ok since we are showing off all our cursor abilities, here's my contribution to the same issue:

    mine is designed to search the db, and provide you with follow up SQL's to drill down into where the offending searched word occurs.

    mine's got an optional parameter, which makes an ugly, slow cursor even slower, but sometimes you need that level of detail.

    it produces results like this for any table that the search term occurred in, with no results for tables that did not contain the term:

    EXEC sp_UGLYSEARCH 'TEST'

    -- SELECT * FROM [ACACTSCR] WHERE [DESCRIP] LIKE '%TEST%' OR [TITLE] LIKE '%TEST%'

    --optional parameter SEARCHBYCOLUMN

    EXEC sp_UGLYSEARCH 'TEST',1

    -- creates one SQL for each Column that actually has a match for the searched value i.e.

    -- SELECT * FROM [dbo].[ACACTSCR] WHERE [DESCRIP] LIKE '%TEST%'

    -- SELECT * FROM [dbo].[ACACTSCR] WHERE [TITLE] LIKE '%TEST%'

    do not use this on some production database ! it creates cursos full of tablescans, one for each varchar type columns...on a MillionBillionRow table with 50 varchar columns, that's 50 slow table scans that slows down the server, which falls under the "bad thing" category!

    this version does handle tables in schemas other than dbo:

    CREATE PROCEDURE sp_UGLYSEARCH

    /*

    --Purpose: to search every string column in a databasefor a specific word

    --returns sql statement as a string which idnetifies the matching table

    -- or when the optional parameter is used, the sql statement for the specific matching column.

    --usage:

    -- EXEC sp_UGLYSEARCH 'Provisional'

    -- EXEC sp_UGLYSEARCH 'TEST'

    -- creates one SQL for each table that actually has a match for the searched value i.e.

    -- SELECT * FROM [ACACTSCR] WHERE [DESCRIP] LIKE '%TEST%' OR [TITLE] LIKE '%TEST%'

    --optional parameter SEARCHBYCOLUMN

    -- EXEC sp_UGLYSEARCH 'TEST',1

    -- creates one SQL for each Column that actually has a match for the searched value i.e.

    -- SELECT * FROM [dbo].[ACACTSCR] WHERE [DESCRIP] LIKE '%TEST%'

    -- SELECT * FROM [dbo].[ACACTSCR] WHERE [TITLE] LIKE '%TEST%'

    */

    @SEARCHSTRING VARCHAR(50),

    @SEARCHBYCOLUMN INT = 0

    AS

    BEGIN

    SET NOCOUNT ON

    DECLARE @SQL VARCHAR(max),

    @SCHEMANAME VARCHAR(100),

    @TABLENAME VARCHAR(100),

    @COLUMNNAME VARCHAR(100),

    @COLZ VARCHAR(max)

    CREATE TABLE #RESULTS(SCHEMANAME VARCHAR(100), TBLNAME VARCHAR(100),COLNAME VARCHAR(100),SQL VARCHAR(max))

    SELECT

    SCHEMA_NAME(schema_id) AS SCHEMANAME,

    objz.name AS TBLNAME,

    colz.name AS COLNAME,

    TYPE_NAME(colz.user_type_id) AS DATATYPE

    INTO #TEMP

    FROM sys.objects objz

    INNER JOIN sys.columns colz ON objz.object_id = colz.object_id

    WHERE objz.type='U'

    AND TYPE_NAME(colz.user_type_id) IN ('VARCHAR','NVARCHAR','CHAR','NCHAR')

    AND colz.max_length >= LEN(@SEARCHSTRING) --smart: don't search varchar(1) columns for 'TEST' 4xmpl

    ORDER BY TBLNAME,COLNAME

    IF @SEARCHBYCOLUMN = 0

    BEGIN

    DECLARE C1 CURSOR FOR

    SELECT SCHEMANAME,TBLNAME,COLNAME FROM #TEMP ORDER BY SCHEMANAME,TBLNAME,COLNAME

    OPEN C1

    FETCH NEXT FROM C1 INTO @SCHEMANAME,@TABLENAME,@COLUMNNAME

    WHILE @@FETCH_STATUS <> -1

    BEGIN

    SET @COLZ=''

    SELECT @COLZ = @COLZ + QUOTENAME(COLNAME) + ' LIKE ''%' + @SEARCHSTRING + '%'' OR ' FROM #TEMP WHERE TBLNAME=@TABLENAME

    --@COLZ has a trailing 'OR ' which must be removed

    SET @COLZ = SUBSTRING(@COLZ,1,LEN(@COLZ) -3)

    --PRINT @COLZ

    SET @SQL = 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@SCHEMANAME) + '.' + QUOTENAME(@TABLENAME) + ' WHERE ' + @COLZ + ') INSERT INTO #RESULTS(TBLNAME,COLNAME,SQL) VALUES(''' + @TABLENAME + ''',''-'','' SELECT * FROM ' + QUOTENAME(@TABLENAME) + ' WHERE ' + REPLACE(@COLZ,'''','''''') + ''') ;'

    --PRINT @SQL

    EXEC (@SQL)

    FETCH NEXT FROM C1 INTO @SCHEMANAME,@TABLENAME,@COLUMNNAME

    END

    CLOSE C1

    DEALLOCATE C1

    END

    ELSE --@SEARCHBYCOLUMN <> 0

    BEGIN

    DECLARE C2 CURSOR FOR

    SELECT SCHEMANAME,TBLNAME,COLNAME FROM #TEMP ORDER BY SCHEMANAME,TBLNAME,COLNAME

    OPEN C2

    FETCH NEXT FROM C2 INTO @SCHEMANAME,@TABLENAME,@COLUMNNAME

    WHILE @@FETCH_STATUS <> -1

    BEGIN

    SET @SQL = 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@SCHEMANAME) + '.' + QUOTENAME(@TABLENAME) + ' WHERE ' + QUOTENAME(@COLUMNNAME) + ' LIKE ''%' + @SEARCHSTRING + '%'')

    INSERT INTO #RESULTS(SCHEMANAME,TBLNAME,COLNAME,SQL) VALUES(''' + @SCHEMANAME + ''',''' + @TABLENAME + ''',''' + @COLUMNNAME + ''',''

    SELECT * FROM ' + QUOTENAME(@SCHEMANAME) + '.' + QUOTENAME(@TABLENAME) + ' WHERE ' + QUOTENAME(@COLUMNNAME) + ' LIKE ''''%' + @SEARCHSTRING + '%'''' '') ;'

    PRINT @SQL

    EXEC (@SQL)

    FETCH NEXT FROM C2 INTO @SCHEMANAME,@TABLENAME,@COLUMNNAME

    END

    CLOSE C2

    DEALLOCATE C2

    END --@SEARCHBYCOLUMN <> 0

    SELECT * FROM #RESULTS ORDER BY TBLNAME,COLNAME

    END --PROC

    GO

    SELECT * FROM #RESULTS ORDER BY TBLNAME,COLNAME

    END --PROC

    GO

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 9 posts - 1 through 8 (of 8 total)

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