How to search every table and field in a SQL Server Database
Update: I have corrected the original issue with this script. Please see: Search every table and field in a SQL Server Database Updated.
Today I had an issue with Umbraco and a copy of a deleted page appearing in the menu, I'll post how I fix it if I ever do find the answer but while trying to track the issue down I came across a really useful piece of T-SQL from Narayana Vyas Kondreddi (Vyas) that searches each table in a database and then each field in the table. I had to expand it to include integers etc but all credit to him! For reference here's a copy of the code:
Search all tables and fields in a SQL Server DatabaseCREATEPROC SearchAllTables(@SearchStrnvarchar(100))ASbegin...endBEGIN-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.-- Purpose: To search all columns of all tables for a given search string-- Written by: Narayana Vyas Kondreddi-- Site: http://vyaskn.tripod.com-- Tested on: SQL Server 7.0 and SQL Server 2000-- Date modified: 28th July 2002 22:50 GMTCREATETABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))SETNOCOUNTONDECLARE@TableNamenvarchar(256),@ColumnNamenvarchar(128),@SearchStr2nvarchar(110)SET@TableName = ''SET@SearchStr2 = QUOTENAME('%'+@SearchStr+'%','''')WHILE@TableNameISNOTNULLbegin...endBEGINSET@ColumnName = ''SET@TableName = (SELECTMIN(QUOTENAME(TABLE_SCHEMA) +'.'+QUOTENAME(TABLE_NAME))FROM INFORMATION_SCHEMA.TABLESWHERE TABLE_TYPE = 'BASE TABLE'ANDQUOTENAME(TABLE_SCHEMA) +'.'+QUOTENAME(TABLE_NAME) >@TableNameANDOBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +'.'+QUOTENAME(TABLE_NAME)),'IsMSShipped') = 0)WHILE(@TableNameISNOTNULL)AND(@ColumnNameISNOTNULL)begin...endBEGINSET@ColumnName =(SELECTMIN(QUOTENAME(COLUMN_NAME))FROM INFORMATION_SCHEMA.COLUMNSWHERE TABLE_SCHEMA = PARSENAME(@TableName,2)AND TABLE_NAME = PARSENAME(@TableName,1)AND DATA_TYPE IN('char','varchar','nchar','nvarchar','int','decimal')ANDQUOTENAME(COLUMN_NAME) >@ColumnName)IF@ColumnNameISNOTNULLbegin...endBEGININSERTINTO #ResultsEXEC('SELECT '''+@TableName+'.'+@ColumnName+''', LEFT('+@ColumnName+, 3630)+@TableName+'(NOLOCK) '+'SELECT '''+@TableName+'.'+@ColumnName+''', LEFT('+@ColumnName+', 3630)FROM '+@TableName+'(NOLOCK) '+' WHERE '+@ColumnName+'LIKE '+@SearchStr2)ENDENDENDSELECT ColumnName, ColumnValue FROM #ResultsEND