Created
July 14, 2022 14:43
-
-
Save dariusz-wozniak/e5f834f8ad70a3caad6662ecc4e19dac to your computer and use it in GitHub Desktop.
SQL: Search all tables and all columns for a specific value
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
DECLARE @SearchStr nvarchar(100) | |
SET @SearchStr = '## YOUR STRING HERE ##' | |
-- 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 | |
-- Updated and tested by Tim Gaunt | |
-- http://www.thesitedoctor.co.uk | |
-- http://blogs.thesitedoctor.co.uk/tim/2010/02/19/Search+Every+Table+And+Field+In+A+SQL+Server+Database+Updated.aspx | |
-- Tested on: SQL Server 7.0, SQL Server 2000, SQL Server 2005 and SQL Server 2010 | |
-- Date modified: 03rd March 2011 19:00 GMT | |
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630)) | |
SET NOCOUNT ON | |
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110) | |
SET @TableName = '' | |
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''') | |
WHILE @TableName IS NOT NULL | |
BEGIN | |
SET @ColumnName = '' | |
SET @TableName = | |
( | |
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) | |
FROM INFORMATION_SCHEMA.TABLES | |
WHERE TABLE_TYPE = 'BASE TABLE' | |
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName | |
AND OBJECTPROPERTY( | |
OBJECT_ID( | |
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) | |
), 'IsMSShipped' | |
) = 0 | |
) | |
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL) | |
BEGIN | |
SET @ColumnName = | |
( | |
SELECT MIN(QUOTENAME(COLUMN_NAME)) | |
FROM INFORMATION_SCHEMA.COLUMNS | |
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2) | |
AND TABLE_NAME = PARSENAME(@TableName, 1) | |
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal') | |
AND QUOTENAME(COLUMN_NAME) > @ColumnName | |
) | |
IF @ColumnName IS NOT NULL | |
BEGIN | |
INSERT INTO #Results | |
EXEC | |
( | |
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' + | |
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2 | |
) | |
END | |
END | |
END | |
SELECT ColumnName, ColumnValue FROM #Results | |
DROP TABLE #Results |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment