How to search all columns of all tables in a database for a keyword?
Credits to: Vyas
http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm
Here is the complete stored procedure code:
–To search all columns of all tables in Pubs database for the keyword “Computer”
| T-SQL | | copy code | | ? |
| 01 | CREATE PROC SearchAllTables |
| 02 | ( |
| 03 | @SearchStr NVARCHAR(100) |
| 04 | ) |
| 05 | AS |
| 06 | BEGIN |
| 07 | <span style="font-family: verdana; color: gray; font-size: xx-small;"> |
| 08 | -- Copyright � 2002 Narayana Vyas Kondreddi. All rights reserved. |
| 09 | -- Purpose: To search all columns of all tables for a given search string |
| 10 | -- Written by: Narayana Vyas Kondreddi |
| 11 | -- Site: http://vyaskn.tripod.com |
| 12 | -- Tested on: SQL Server 7.0 and SQL Server 2000 |
| 13 | -- Date modified: 28th July 2002 22:50 GMT |
| 14 | </span> |
| 15 | |
| 16 | CREATE TABLE #Results (ColumnName NVARCHAR(370), ColumnValue NVARCHAR(3630)) |
| 17 | |
| 18 | SET NOCOUNT ON |
| 19 | |
| 20 | DECLARE @TableName NVARCHAR(256), @ColumnName NVARCHAR(128), @SearchStr2 NVARCHAR(110) |
| 21 | SET @TableName = '' |
| 22 | SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''') |
| 23 | |
| 24 | WHILE @TableName IS NOT NULL |
| 25 | BEGIN |
| 26 | SET @ColumnName = '' |
| 27 | SET @TableName = |
| 28 | ( |
| 29 | SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) |
| 30 | FROM INFORMATION_SCHEMA.TABLES |
| 31 | WHERE TABLE_TYPE = 'BASE TABLE' |
| 32 | AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName |
| 33 | AND OBJECTPROPERTY( |
| 34 | OBJECT_ID( |
| 35 | QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) |
| 36 | ), 'IsMSShipped' |
| 37 | ) = 0 |
| 38 | ) |
| 39 | |
| 40 | WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL) |
| 41 | BEGIN |
| 42 | SET @ColumnName = |
| 43 | ( |
| 44 | SELECT MIN(QUOTENAME(COLUMN_NAME)) |
| 45 | FROM INFORMATION_SCHEMA.COLUMNS |
| 46 | WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2) |
| 47 | AND TABLE_NAME = PARSENAME(@TableName, 1) |
| 48 | AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar') |
| 49 | AND QUOTENAME(COLUMN_NAME) > @ColumnName |
| 50 | ) |
| 51 | |
| 52 | IF @ColumnName IS NOT NULL |
| 53 | BEGIN |
| 54 | INSERT INTO #Results |
| 55 | EXEC |
| 56 | ( |
| 57 | 'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) |
| 58 | FROM ' + @TableName + ' (NOLOCK) ' + |
| 59 | ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2 |
| 60 | ) |
| 61 | END |
| 62 | END |
| 63 | END |
| 64 | |
| 65 | SELECT ColumnName, ColumnValue FROM #Results |
| 66 | END |
| 67 |
| T-SQL | | copy code | | ? |
| 1 | EXEC SearchAllTables 'Computer' |
| 2 | GO |
| 3 |