en-UShe-IL
You are here:   Blog
Register   |  Login

Blog Archive:

Maximize
* Can be used in order to search for older blogs Entries

Search in blogs


Blog Categories:

Maximize
* Can be used in order to search for blogs Entries by Categories

Blog Tags:

Maximize
* Can be used in order to search for blogs by keywords

TNWikiSummit


Awared MVP 


 


Microsoft® Community Contributor 


Microsoft® Community Contributor


 Read first, before you you use the blog! Maximize
אפר28

Written by: ronen ariely
28/04/2014 13:10 RssIcon

In this short blog I will post a simple dynamic query to search in one operation all string columns ('text','ntext','varchar','nvarchar','char','nchar') of the specific table or in all tables of the database. Next step, we can use CROSS APPLY and SPLIT Function (or user type) in order to expand this query to get result from several searching strings.

use AdventureWorks2012
GO
 
DECLARE @MyQuery NVARCHAR(MAX) = N''
  
-- What do we want to find?
-- If you need to search Multiple Strings then you shold use CLR Split Function as mentioned below*!
DECLARE @SearchString nvarchar(100) = N'Ariely Ronen'
 
-- Where do we want to look for?
-- If you want to search all tables then dont insert any value (NULL),
-- If you want to search a specific table then insert the table name
-- If you need to search several specific tables then you should use tables comma delimited as mentioned below!
DECLARE @TableToFilter NVARCHAR(100) --= 'test'
  
SET @MyQuery = @MyQuery + (
    SELECT
        'SELECT * FROM (' + CHAR(10) +
        'SELECT ' +
            '''[' + TABLE_CATALOG + ']'' as TABLE_CATALOG, ''[' + TABLE_SCHEMA + ']'' as TABLE_SCHEMA, ''[' + TABLE_NAME + ']'' as TABLE_NAME,' +
            '''[' + COLUMN_NAME + ']'' as COLUMN_NAME, ' +
             QUOTENAME(COLUMN_NAME) + ' COLLATE DATABASE_DEFAULT as Value' +
        ' FROM [' + TABLE_CATALOG + '].[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']' +
        -- Here we filter the search String we want to find.
        -- If you need to search Multiple Strings then you shold use CLR Split Function or User type,
        -- and replace the use of "Like" filter with the use of "IN" filter
        ' WHERE [' + TABLE_NAME + '].[' + COLUMN_NAME + '] LIKE ''%'' + @SearchString + ''%'' COLLATE DATABASE_DEFAULT' +
        ') as [' + TABLE_CATALOG + TABLE_SCHEMA + TABLE_NAME + ']' CHAR(10) +
        CHAR(10) + 'UNION ALL' + CHAR(10)
    FROM (
        SELECT
            C.TABLE_SCHEMA,C.TABLE_CATALOG,C.TABLE_NAME,C.COLUMN_NAME
            -- Here we filter the tables that we want to search in.
            ,TableToFilter =  CASE
                WHEN @TableToFilter IS NULL THEN 1
                -- If you need to search in several specific tables, then you shold use CLR Split Function or User type,
                -- and replace the use of "=" filter with the use of "IN" filter
                WHEN NOT @TableToFilter IS NULL and C.TABLE_NAME = @TableToFilter THEN 1
                ELSE 0
            END
        FROM INFORMATION_SCHEMA.COLUMNS C
        left JOIN INFORMATION_SCHEMA.TABLES T on C.TABLE_CATALOG = T.TABLE_CATALOG and T.TABLE_SCHEMA = T.TABLE_SCHEMA and C.TABLE_NAME = T.TABLE_NAME
        WHERE DATA_TYPE IN ('text','ntext','varchar','nvarchar','char','nchar') and TABLE_TYPE = 'BASE TABLE'
    ) T1
    where TableToFilter = 1
    FOR XML PATH('')
)
SET @MyQuery = REPLACE(@MyQuery + N'END','UNION ALL'+ CHAR(10) +'END','')
PRINT @MyQuery
EXECUTE sp_executesql @MyQuery, N'@SearchString NVARCHAR(100)', @SearchString = N'Glenside'


* Thanks to Naomi Nosonovsky for the comment.
It look like on one of her database the original query I wrote result with an error:
Msg 457, Level 16, State 1, Line 1
Implicit conversion of varchar value to text cannot be performed because the collation of the value is unresolved due to a collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in UNION ALL operator.
It is clear that the problem is related to the collation. I did not get this error, but to go on the safe side, I added to the query "COLLATE DATABASE_DEFAULT" as Naomi suggested.

.

Tags: SQL , T-SQL
Categories: SQL