Archive for June, 2012
SQL – Database Search
June 26, 2012
Leave a comment
We can search the database for a particular value just like searching in table. Here presenting a self-explanatory TSQL query
This query is useful in two ways
1. Wildcard Search i.e. LIKE
2. Exact Search i.e. EQUAL
-- Select Search criteria, If not WILDCARDMATCH, will search for Exact Match DECLARE @SEARCHCRETERIA NVARCHAR(1000) = 'WILDCARDMATCH' -- Enter Searching value DECLARE @SEARCH NVARCHAR(1000) = 'Australia' DECLARE @ROWCOUNT INT; DECLARE @COLUMNNAME NVARCHAR(1000); DECLARE @TABLENAME NVARCHAR(1000); DECLARE @STAT NVARCHAR(2000); DECLARE @TABLE TABLE ( COLUMN_NAME VARCHAR(1000) ,TABLE_NAME VARCHAR(1000) ) DECLARE @INTABLE TABLE ( COLUMN_NAME VARCHAR(1000) ,TABLE_NAME VARCHAR(1000) ) DECLARE @RESULTTABLE TABLE ( COLUMN_NAME VARCHAR(1000) ,COLUMN_VALUE VARCHAR(1000) ) INSERT INTO @TABLE SELECT '[' + TABLE_CATALOG + '].[' + TABLE_SCHEMA + '].[' + TABLE_NAME + '].[' + COLUMN_NAME + ']' AS 'TargetColumn' ,'[' + TABLE_CATALOG + '].[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']' AS 'TargetTable' FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE <> 'XML' SELECT @ROWCOUNT = COUNT(*) FROM @TABLE WHILE (@ROWCOUNT != 0) BEGIN INSERT INTO @INTABLE SELECT TOP 1 * FROM @TABLE IF (@SEARCHCRETERIA = 'WILDCARDMATCH') SELECT @STAT = 'SELECT ' + '''' + COLUMN_NAME + '''' + ',' + COLUMN_NAME + ' FROM ' + TABLE_NAME + ' WHERE CONVERT( VARCHAR,' + COLUMN_NAME + ') LIKE ' + '''%' + @SEARCH + '%''' FROM @INTABLE ELSE SELECT @STAT = 'SELECT ' + '''' + COLUMN_NAME + '''' + ',' + COLUMN_NAME + ' FROM ' + TABLE_NAME + ' WHERE CONVERT( VARCHAR,' + COLUMN_NAME + ') = ' + '''' + @SEARCH + '''' FROM @INTABLE INSERT INTO @RESULTTABLE EXEC (@STAT) DELETE FROM @INTABLE DELETE TOP (1) FROM @TABLE SELECT @ROWCOUNT = COUNT(*) FROM @TABLE END SELECT * FROM @RESULTTABLE
Points to consider:
1. This code has tested only in SQL 2008/2008 R2 and SQL 2012
2. We couldn’t able to search XML data type columns so excluded XML columns search
3. We could use DISTINCT at final results to avoid any duplicate search results of column values
Categories: TSQL
Database Search