> ## Content Index
> Fetch the complete content index at: https://blog.tsd.digital/llms.txt
> Use this file to discover other available public pages before exploring further.

# How to search every table and field in a SQL Server Database
- URL: https://blog.tsd.digital/how-to-search-every-table-and-field-in-a-sql-server-database/
- Published: 2007-11-02T13:28:37.000Z
- Updated: 2007-11-02T13:28:37.000Z
- Author: Tim Gaunt
- Tags: SQL Server, #Import 2025-04-01 05:36

### Update: I have corrected the original issue with this script. Please see: [Search every table and field in a SQL Server Database Updated](https://blog.tsd.digital/blog/search-every-table-and-field-in-a-sql-server-database-updated/).

Today I had an issue with [Umbraco](http://www.umbraco.org/?ref=blog.tsd.digital) 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)](http://vyaskn.tripod.com/search%5Fall%5Fcolumns%5Fin%5Fall%5Ftables.htm?ref=blog.tsd.digital) 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