Post reply

Searching Database tables for a specific value within a database field

  • April 16, 2013 at 6:51 am

    #271072

    Hi,

    I'm new to SQL Server and I need to find out if the value 'Criminal' is stored in any of the Column/Fields within the database. Is there a way to do this with a Select statement?

    This is as far as I have gotten but this Select will only give me columns name Criminal not the value within the column name.

    select colmun_name, table_name

    from Information_Schema.columns

    where column_name has 'Criminal' in it.

    Thanks Much, Jim

  • April 16, 2013 at 7:37 am

    #1606807

    jimoa312 (4/16/2013)

    Hi,

    I'm new to SQL Server and I need to find out if the value 'Criminal' is stored in any of the Column/Fields within the database. Is there a way to do this with a Select statement?

    This is as far as I have gotten but this Select will only give me columns name Criminal not the value within the column name.

    select colmun_name, table_name

    from Information_Schema.columns

    where column_name has 'Criminal' in it.

    Thanks Much, Jim

    This request comes up around here about twice a month. I will post some code I wrote for this very thing a number of years ago. There is VERY VERY VERY IMPORTANT condition with this code. DO NOT RUN THIS IN PRODUCTION!!!!!!! The logic of what you have to do here means that we have to look in every single column of every single row of every single table. This is horribly slow and incredibly inefficient. There is no good way to do this. I have seen this run for over 24 hours on a database in the past. Run this ONLY ON A DEV/TEST server!!!!

    One last thing...did I mention that you should not run this in production???

    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

    Lowell another long time poster around here has a stored proc that does this same kind of thing. His does some things mine doesn't. Depending on your requirements one or the other may be better suited. The same caveat about not running in production applies here too.

    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

    Happy hunting!!!

    _______________________________________________________________

    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/

  • April 16, 2013 at 7:44 am

    #1606812

    Sean Lange (4/16/2013)

    jimoa312 (4/16/2013)

    ... DO NOT RUN THIS IN PRODUCTION!!!!!!!

    ...Run this ONLY ON A DEV/TEST server!!!!

    One last thing...did I mention that you should not run this in production???

    Happy hunting!!!

    you should probably mention he shouldn't run this on production 🙂

    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!

  • April 16, 2013 at 7:46 am

    #1606813

    Thanks for the infomation. I will not run it in Production. 🙂

  • April 16, 2013 at 7:52 am

    #1606814

    --Variable Delaration

    Declare @keytosearch varchar(max), @Database_Selected varchar(50)

    set @keytosearch ='%london%'

    set @Database_Selected= 'Northwind'

    Declare @Table varchar(100), @Table_Name Cursor, @Count_Column int,

    @Result nvarchar(4000),@ID int,@ID_inserted int,@Count_Table int, @data_type varchar(10)

    set @ID_inserted=0

    set @Count_Table=0

    DECLARE @column varchar(max), @Column_Name CURSOR

    --Variable Delaration end

    --Second Cursor start

    declare @informationName varchar(50), @SysName varchar(50), @Var varchar(5)

    set @informationName=@Database_Selected+'.'+'information_schema.COLUMNS'

    Set @SysName=@Database_Selected+'.'+'sys.objects'

    Set @Var='u'

    --Database Selected start

    Create Table #SearchResult(ID int identity(1,1) not null, TableName varchar(50), ColumnName varchar(50))

    Create Table #SearchTestResult(ID int identity(1,1) not null, ColumnName varchar(max))

    Create Table #Table_Name(ID int identity(1,1) not null, table_name varchar(50))

    IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME='Column_Nam')

    drop table Column_Nam

    insert into #Table_Name exec ('Select name from '+@SysName +' where type= '''+@Var+'''')

    --First Cursor open

    SET @Table_Name = CURSOR FOR Select table_name from #Table_Name

    open @Table_Name

    Fetch Next from @Table_Name into @Table

    WHILE @@FETCH_STATUS = 0

    BEGIN

    set @Count_Table =@Count_Table+1

    --Second cursor opened

    --print 'Select column_name from '+@informationName +' where table_name= '''+@Table+''''s

    Exec('SELECT column_name,data_type INTO Column_Nam FROM '+ @informationName +' where table_name = '''+@Table+'''')

    SET @Column_Name = CURSOR FOR (select column_name from Column_Nam )

    OPEN @Column_Name FETCH NEXT FROM @Column_Name INTO @column

    set @Table=@Database_Selected+'.[dbo].['+@Table+']'

    WHILE @@FETCH_STATUS = 0

    BEGIN

    set @data_type= (SELECT top 1 data_type FROM Column_Nam Where column_name= @column)

    if @data_type is not null and (@data_type='varchar' or @data_type='nvarchar' or @data_type='Text')

    begin

    set @Result=null

    if @column like '%-%' begin set @Result =('SELECT ''' + @column +''' FROM ' + @Table

    +' Where ''' + @column + ''' Like '''+@keytosearch+'''') end

    else

    set @Result =('SELECT ' + @column +' FROM ' + @Table +' Where ' + @column + ' Like '''+@keytosearch+'''')

    insert into #SearchTestResult exec(@Result)

    set @ID=0

    set @ID=(Select ID from #SearchTestResult where ID=Scope_identity())

    if @ID is not null

    begin

    set @ID_inserted=(select top 1 ID from #SearchResult order by ID desc)

    if @ID_inserted = @ID

    begin

    print ''

    end

    else

    insert into #SearchResult values (@Table,@column)

    end

    end

    FETCH NEXT FROM @Column_Name INTO @column

    END

    CLOSE @Column_Name

    DEALLOCATE @Column_Name

    --Second cursor closed

    drop table Column_Nam

    Fetch Next from @Table_Name into @Table

    End

    close @Table_Name

    Deallocate @Table_Name

    --First Cursor Closed

    Select * from #SearchResult

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • April 16, 2013 at 7:52 am

    #1606815

    Lowell (4/16/2013)

    Sean Lange (4/16/2013)

    jimoa312 (4/16/2013)

    ... DO NOT RUN THIS IN PRODUCTION!!!!!!!

    ...Run this ONLY ON A DEV/TEST server!!!!

    One last thing...did I mention that you should not run this in production???

    Happy hunting!!!

    you should probably mention he shouldn't run this on production 🙂

    😛

    One time somewhat recently when I posted this I suggested they don't run it in production. Then a day or so later I got a PM from the OP asking what to do because this thing was running and running and their production server was unable to process anything. Figured I would be a little over the top this time.

    _______________________________________________________________

    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/

  • April 16, 2013 at 8:00 am

    #1606819

    Sean Lange (4/16/2013)

    Lowell (4/16/2013)

    Sean Lange (4/16/2013)

    jimoa312 (4/16/2013)

    ... DO NOT RUN THIS IN PRODUCTION!!!!!!!

    ...Run this ONLY ON A DEV/TEST server!!!!

    One last thing...did I mention that you should not run this in production???

    Happy hunting!!!

    you should probably mention he shouldn't run this on production 🙂

    😛

    One time somewhat recently when I posted this I suggested they don't run it in production. Then a day or so later I got a PM from the OP asking what to do because this thing was running and running and their production server was unable to process anything. Figured I would be a little over the top this time.

    Not over the top at all! This is one of those "learn from the experience of others" items they need to really be aware of.

    Something like this needs to be mentioned a little more than once; You PM from another poster proves it; any MillionBillowRow tables will eat up all the CPU and diskspace in temp just to provide the results.

    I've run this myself and seen it take hours when getting data to change the company web site from mycompany.net to mycompany.com.

    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!

  • April 16, 2013 at 8:33 am

    #1606841

    Soooooooooooo... you're saying this code is production ready? :hehe:

  • April 16, 2013 at 8:37 am

    #1606844

    Erin Ramsay (4/16/2013)

    Soooooooooooo... you're saying this code is production ready? :hehe:

    You can do whatever you want on your last day. 😀

    _______________________________________________________________

    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/

  • April 17, 2013 at 2:48 pm

    #1607443

    The code's not really ready to be used in dev or QA either.

    NO reason to search the same table multiple times, esp. not once per column.

    Instead, should do a single access to each table for all relevant columns in one query.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • April 17, 2013 at 3:26 pm

    #1607453

    ScottPletcher (4/17/2013)

    The code's not really ready to be used in dev or QA either.

    NO reason to search the same table multiple times, esp. not once per column.

    Instead, should do a single access to each table for all relevant columns in one query.

    By all means feel free to correct it and post it back so we can all gain from your efforts. I posted some code that I cobbled together over 10 years ago when I needed to do this sort of thing. I look forward to a much improved version.

    _______________________________________________________________

    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/

Viewing 11 posts - 1 through 10 (of 10 total)

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy