Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
T-SQL 6
SQL Server 5
#SQL Server 5
#TSQL 4
BRH 3

Archive · View All
October 2010 2
April 2012 1
October 2011 1
September 2011 1
May 2011 1
April 2010 1
March 2010 1

How to search a string value in all columns in the table and in all tables in the database

Oct 29 2010 12:00AM by Naomi   

One of the common questions I encounter in the different SQL related forums is "How to search all columns in all tables in the database". My usual answer is to refer people to the Question #2 in this blog The ten most asked SQL Server questions. However, the recent thread in MSDN forum forced me to re-evaluate the solution I knew about and come up with my own solution.

At first, I would like to demonstrate the solution I came up with and then I'll show how we can expand it.

Search for a string value in all columns of a table

-- First declare variables for test
declare @Table_Name sysname, @SearchString nvarchar(max)
-- Set values for variables for test
select @Table_Name = 'Items', @SearchString = N'COST'

declare @Columns nvarchar(max), @Cols nvarchar(max)

-- Get all character columns
set @Columns = stuff((select ', ' + quotename(Column_Name) 
 from INFORMATION_SCHEMA.COLUMNS 
 where DATA_TYPE in ('text','ntext','varchar','nvarchar','char','nchar')
 and TABLE_NAME = @Table_Name 
 order by COLUMN_NAME 
 for XML PATH('')),1,2,'')

-- Get columns for select statement - we need to convert all columns to nvarchar(max)
set @Cols = stuff((select ', cast(' + quotename(Column_Name) + ' as nvarchar(max)) as ' + QUOTENAME(Column_Name)
 from INFORMATION_SCHEMA.COLUMNS 
 where DATA_TYPE in ('text','ntext','varchar','nvarchar','char','nchar')
 and TABLE_NAME = @Table_Name 
 order by COLUMN_NAME 
 for XML PATH('')),1,2,'')
 
 -- set select statement using dynamic UNPIVOT
 declare @SQL nvarchar(max)
 set @SQL = 'select * from (select '+ @Cols + ' FROM ' + QUOTENAME(@Table_Name) + 
 ' )src UNPIVOT (Column_Value for ColumnName IN (' + @Columns + ')) unpvt 
 WHERE Column_Value LIKE ''%'' + @SearchString + ''%'''
 
--print @SQL

execute sp_ExecuteSQL @SQL, N'@SearchString nvarchar(max)', @SearchString 

The above select statement will select Column Name and Column Data for the specified search string. It will not add a primary key column to the returned result. Also, if we find the string we're searching for in several columns of the same record, this record will be returned multiple times.

I want to expand on this solution.

First, we add a primary key column to this select statement. In the case when the table has only one primary key column, this column is returned. If the table has more than one primary key columns, this select will return the primary key columns concatenated with '|' character as the result.  The order of PK columns will be in the order of the name of the columns. Finally, we'll make this as a stored procedure and call it spSearchStringInTable.



Stored procedure to search a value in all columns in the table

CREATE PROCEDURE spSearchStringInTable
(@SearchString NVARCHAR(MAX),
 @Table_Schema sysname,
 @Table_Name sysname)
 AS
 BEGIN
DECLARE @Columns NVARCHAR(MAX), @Cols NVARCHAR(MAX), @PkColumn NVARCHAR(MAX)

-- Get all character columns
SET @Columns = STUFF((SELECT ', ' + QUOTENAME(Column_Name) 
 FROM INFORMATION_SCHEMA.COLUMNS 
 WHERE DATA_TYPE IN ('text','ntext','varchar','nvarchar','char','nchar')
 AND TABLE_NAME = @Table_Name 
 ORDER BY COLUMN_NAME 
 FOR XML PATH('')),1,2,'')

IF @Columns IS NULL -- no character columns
   RETURN -1

-- Get columns for select statement - we need to convert all columns to nvarchar(max)
SET @Cols = STUFF((SELECT ', cast(' + QUOTENAME(Column_Name) + ' as nvarchar(max)) as ' + QUOTENAME(Column_Name)
 FROM INFORMATION_SCHEMA.COLUMNS 
 WHERE DATA_TYPE IN ('text','ntext','varchar','nvarchar','char','nchar')
 AND TABLE_NAME = @Table_Name 
 ORDER BY COLUMN_NAME 
 FOR XML PATH('')),1,2,'')
 
 SET @PkColumn = STUFF((SELECT N' + ''|'' + ' + ' cast(' + QUOTENAME(CU.COLUMN_NAME) + ' as nvarchar(max))' 
 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
 INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CU ON TC.TABLE_NAME = CU.TABLE_NAME
 AND TC.TABLE_SCHEMA = CU.TABLE_SCHEMA 
 AND Tc.CONSTRAINT_NAME = CU.CONSTRAINT_NAME 
 WHERE TC.CONSTRAINT_TYPE ='PRIMARY KEY' AND TC.TABLE_SCHEMA = @Table_Schema AND TC.TABLE_NAME = @Table_Name 
 ORDER BY CU.COLUMN_NAME
 FOR XML PATH('')),1,9,'') 

 IF @PkColumn IS NULL
    SELECT @PkColumn = 'cast(NULL as nvarchar(max))' 
    
 -- set select statement using dynamic UNPIVOT
 DECLARE @SQL NVARCHAR(MAX)
 SET @SQL = 'select *, ' + QUOTENAME(@Table_Schema,'''') + 'as [Table Schema], ' + QUOTENAME(@Table_Name,'''') + ' as [Table Name]' +
  ' from 
  (select '+ @PkColumn + ' as [PK Column], ' + @Cols + ' FROM ' + QUOTENAME(@Table_Schema) + '.' + QUOTENAME(@Table_Name) +  ' )src UNPIVOT ([Column Value] for [Column Name] IN (' + @Columns + ')) unpvt 
 WHERE [Column Value] LIKE ''%'' + @SearchString + ''%'''
 
 --print @SQL

EXECUTE sp_ExecuteSQL @SQL, N'@SearchString nvarchar(max)', @SearchString 
END
GO
And if we want to call this SP for all tables in the database, we can use this code:
CREATE TABLE #RESULT ([PK COLUMN] NVARCHAR(MAX), [COLUMN VALUE] NVARCHAR(MAX), [COLUMN Name] sysname, [TABLE SCHEMA] sysname, [TABLE Name] sysname)
DECLARE @Table_Name sysname, @SearchString NVARCHAR(MAX), @Table_Schema sysname
SET @SearchString = N'Cost'

DECLARE curAllTables CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
    FOR
    SELECT   Table_Schema, Table_Name
    FROM     INFORMATION_SCHEMA.Tables    
    WHERE TABLE_TYPE = 'BASE TABLE'
    ORDER BY Table_Schema, Table_Name
    
    OPEN curAllTables
    FETCH  curAllTables
    INTO @Table_Schema, @Table_Name    
	WHILE (@@FETCH_STATUS = 0) -- Loop through all tables in the database
      BEGIN
		INSERT #RESULT 
		EXECUTE spSearchStringInTable @SearchString, @Table_Schema, @Table_Name
    
        FETCH  curAllTables
        INTO @Table_Schema, @Table_Name
      END -- while
    CLOSE curAllTables
    DEALLOCATE curAllTables
  -- Return results 
  SELECT * FROM #RESULT ORDER BY [Table Name] 

Obviously, this code may execute very long on a database with many big tables. You may change it to only check for the existence of the value in the table instead of returning the actual records. The code modifications for this task is trivial and I leave it to the readers of this blog.

There is an alternative version of this script published here Search all columns in all tables You may like that script as well.

Hope you like this topic and see you next time.

Tags: T-SQL, BRH, #SQL Server, #TSQL, SQL Server,


Naomi
31 · 6% · 1776
2
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

10  Comments  

  • This is another method that works for character data http://beyondrelational.com/blogs/madhivanan/archive/2009/12/14/search-a-value-in-character-column-of-all-tables.aspx

    commented on Nov 18 2010 4:48AM
    Madhivanan
    3 · 40% · 12924
  • Hi Naomi, How do i make it work if I want to search all the columns for the values which have single quotes in them? I tried this

    select @TableName = 'trecur', @SearchString = N'''

    And the error I am getting is :-

    Msg 102, Level 15, State 1, Line 21 Incorrect syntax near ','. Msg 102, Level 15, State 1, Line 51 Incorrect syntax near ' + QUOTENAME(@Table_Name) +

    '. Msg 105, Level 15, State 1, Line 62 Unclosed quotation mark after the character string ', @SearchString '.

    I am doing something wrong can you please advice.

    Many Thanks in advance, DCS

    commented on Sep 15 2011 4:12AM
    DCS
    173 · 1% · 285
  • Using the top code from this blog works fine for me with the search for single quote.

    commented on Sep 15 2011 9:36AM
    Naomi
    31 · 6% · 1776
  • Hi Naomi,

    Thanks for replying, U r using @SearchString = N''''

    while I used :- @SearchString = N'''

    maybe that is the reason.

    I used one single quote between two single quotes and you used four single quotes.

    commented on Sep 16 2011 2:40AM
    DCS
    173 · 1% · 285
  • Correct usage is 4 single quotes.

    commented on Sep 16 2011 6:08PM
    Naomi
    31 · 6% · 1776
  • Hi Naomi,

    The stored procedure does not correctly handle tables with schema names. Line 44 of the stored proc reads

    (select '+ @PkColumn + ' as [PK Column], ' + @Cols + ' from ' + QUOTENAME(@Table_Name) +

    It should be

    (select '+ @PkColumn + ' as [PK Column], ' + @Cols + ' from ' + QUOTENAME(@TableSchema) + '.' + QUOTENAME(@TableName) +

    Tom Cooper

    commented on Jul 9 2013 9:25PM
    thomkc
    894 · 0% · 32
  • Hi Naomi, Thanks for a wonderful post. I am working on this problem where I need to search for multiple keywords across all tables. Any suggestions how can I tweak the procedure to achieve this?

    commented on Sep 16 2013 11:37AM
    sdave05
    3057 · 0% · 2
  • Hi Naomi,Can you tell me how to search word by word that are splitted from a string? How to modify the procedure that will search word by word and if the word is found I also need to return the entire row. Please give a suggestion. Thanks in advance ...

    commented on Nov 4 2013 3:58AM
    parijat
    3057 · 0% · 2
  • Hi Tom,

    Thanks, I didn't see your comment until now. I fixed the procedure - good catch.


    Hi Parijat,

    Interesting question. I think for SQL Server 2008 and up we can change parameter to SP to be a table valued list of words. We then would need to output this list into a temp table (so our dynamic SQL will see it) and change the procedure to JOIN with that temp table instead of a single expression. I'll think about writing a TechNet article about it (with that new idea). I'll post the link once it will be ready,

    commented on Apr 24 2014 10:29AM
    Naomi
    31 · 6% · 1776
  • Hi Parijat again,

    Sorry for being late with my reply. I created a new TechNet article where I demonstrated how to expand search for multiple words. Please take a look at link text

    commented on Apr 28 2014 10:57AM
    Naomi
    31 · 6% · 1776

Your Comment


Sign Up or Login to post a comment.

"How to search a string value in all columns in the table and in all tables in the database" rated 5 out of 5 by 2 readers
How to search a string value in all columns in the table and in all tables in the database , 5.0 out of 5 based on 2 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]