How to search all text fields in a DB for some substring with T-SQL
Solution 1
You'r close. Compare yours with this example: Searching and finding a string value in all columns in a SQL Server table
The above link is for searching a single table, however here is another link which includes all tables: How to search all columns of all tables in a database for a keyword?
EDIT : Just in case the link ever goes bad, here's the solution from that link...
CREATE PROC SearchAllTables
(
@SearchStr nvarchar(100)
)
AS
BEGIN
-- 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 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')
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
END
EXEC SearchAllTables '<yourSubstringHere>'
Note: As the comment suggests in the code snippet, it was tested using older versions of SQL Server. This may not work on SQL Server 2012.
Solution 2
The chosen answer is brilliant, but I found when using it repeatedly the results were erroneous, so I added some clean up to make it re-runnable with accurate results:
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'SearchAllTables')
DROP PROC SearchAllTables
GO
CREATE PROC SearchAllTables
(
@SearchStr nvarchar(100)
)
AS
BEGIN
-- 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
-- Slightly modified by: Natalie Ford, 6/10/15
-- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 28th July 2002 22:50 GMT
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
SET NOCOUNT ON
TRUNCATE Table #Results
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')
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
END
Jim
Updated on October 07, 2020Comments
-
Jim almost 4 years
I have a huge schema, with several hundreds of tables and several thousands of columns. I'd know that a specific IP address is stored in this database in several places, but I'm not sure what table(s) or column(s) it is stored in. Basically, I'm trying to find everywhere that this IP address is stored in the DB so I can update it to a new value in all those places.
Here's my first crack at a T-SQL statement to print out the table and column name, and the value, for every text column in the database that has the substring 10.15.13 in it.
Now, this works, sort of. The problem is, when I execute it in Management Studio, the call to sp_executesql will actually return all the empty results from every query that returns nothing (i.e. the column doesn't have any records with that substring), and it fills the result window to its max, and then I don't actually see if anything was printed.
Is there a better way to write this query? Or can I run it in some different way so that it only shows me the Tables and Columns where this substring exists?
DECLARE @SchemaName VARCHAR(50), @TableName VARCHAR(50), @ColumnName VARCHAR(50); BEGIN DECLARE textColumns CURSOR FOR SELECT s.Name, tab.Name, c.Name FROM Sys.Columns c, Sys.Types t, Sys.Tables tab, Sys.Schemas s WHERE s.schema_id = tab.schema_id AND tab.object_id = c.object_id AND c.user_type_id = t.user_type_id AND t.Name in ('TEXT','NTEXT','VARCHAR','CHAR','NVARCHAR','NCHAR'); OPEN textColumns FETCH NEXT FROM textColumns INTO @SchemaName, @TableName, @ColumnName WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @sql NVARCHAR(MAX), @ParamDef NVARCHAR(MAX), @result NVARCHAR(MAX); SET @sql = N'SELECT ' + @ColumnName + ' FROM ' + @SchemaName + '.' + @TableName + ' WHERE ' + @ColumnName + ' LIKE ''%10.15.13%'''; SET @ParamDef = N'@resultOut NVARCHAR(MAX) OUTPUT'; EXEC sp_executesql @sql, @ParamDef, @resultOut = @result OUTPUT; PRINT 'Column = ' + @TableName + '.' + @ColumnName + ', Value = ' + @result; FETCH NEXT FROM textColumns INTO @SchemaName, @TableName, @ColumnName END CLOSE textColumns; DEALLOCATE textColumns; END
I'd like to see results something like this where it shows the table/column that the substring was found in, and the full value in that column...
Column = SomeTable.SomeTextColumn, Value = 'https://10.15.13.210/foo' Column = SomeTable.SomeOtherColumn, Value = '10.15.13.210'
etc.
-
codemonkeh about 10 years-1 I get the following error in SQL Server 2012 "Msg 217, Level 16, State 1, Procedure SearchAllTables, Line 54 Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)."
-
Munawar almost 10 yearscodemonkeh, make sure to add drop storedprocedure and temp table statements after each execution OR before the execution.
-
Jahangeer almost 10 yearsget the following error in SQL Server 2012 "Msg 217, Level 16, State 1, Procedure SearchAllTables, Line 54 Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32). Same error
-
aggie over 8 years@PiotrWolkowski Im getting the same errors 1) how can I reuse this without errors in db listed 2) how can I specify some columns across a couple of tables
-
PiotrWolkowski over 8 years@aggie Perhaps this is a question either to Jeremy (the owner of the answer) or to Jim (the owner of the question) since I only edited this thread.
-
Edward Dortland over 8 yearsThe error you guys are describing is because there is no batch seperator between the stored procedure definition and the execution statement. Therefore, the execution statement is now part of the stored procedure definition hence it's recursively being executed. Put "GO" between the last END and the Execute statement..
-
Mike over 4 yearsTested successfully on SQL 2014