Have you ever had a need to search for a specific string across all the tables in your database? Surprisingly this does seem like a more common requirement than I thought. I have seen a lot of questions asking for such a script on various newsgroups and forums.
Imagine this scenario as a DBA: You have a DBA Tools database that has lots of metadata about backups, restores, log files etc and it stores the path information in the configuration tables. Now you restore this database to a new server, but the drive letters between the old and new server don’t match. You want to find all the columns that store the paths pointing to D:\ drive, and replace them with E:\ drive. This sort of script will quickly show you all the occurrences of D:\ drive references across all your tables.
I have a script lying around in my personal script library that does exactly this. Its from a long time ago though. I’ve now modernised it, enhanced it a bit, added a feature or two while I am at it.
I now present you dbo.SearchAllTables. This stored procedure has the below input parameters:
| Parameter | Datatype | Mandatory? | Description |
|---|---|---|---|
| @DBName | sysname | Yes | Name of the database to be searched. |
| @SearchString | nvarchar(128) | Yes | The string to be searched for. |
| @Top | int | No | Restricts output for each column to specified number of rows. |
| @ExcludeSchemas | nvarchar(4000) | No | Comma separated list of schemas to be excluded from search. |
| @ExcludeTables | nvarchar(4000) | No | Comma separated list of tables to be excluded from search. Table name format: SchemaName.TableName |
| @ExcludeColumns | nvarchar(4000) | No | Comma separated list of columns to be excluded from search. Column name format: SchemaName.TableName.ColumnName |
| @Debug | bit | No | If set to 1, debug information will be printed. Defaults to 0 |
A quick summary of how this stored procedure works
First it validates the input parameter @DBName to make sure the specified database exists. Then it makes sure a valid search string is specified for @SearchString parameter.
Gathers a list of all character datatypes columns (text, ntext, char, nchar, varchar, nvarchar, sysname) into a temporary table named #ColumnList. Then it deletes the information from this temporary table by processing the specified schema, table and column level exclusions. Now the work table is ready, reflecting the exclusions.
The code then loops through the table, searching each in-scope column using the LIKE operator. The matching rows containing the search string would be stored in another temporary table named #Results. If @Top parameter is specified, the code makes sure the number of rows returned for each search are restricted to the specified number.
Once all the in-scope columns are searched, the results from the #Results temporary table would be output in the ascending alphanetical order of schema name, table name and column name, along ith the data.
Important notes:
- Create this stored procedure in a database of your choice – typically a DBA Tools database. You can make it search any database by specifying the target database name as the first input parameter @DBName
- This procedure can be quite quick on smaller databases, but could be very slow on larger databases with lots of character columns or with lots of rows.
- As the code is just scanning all the character columns, this is quite a resource intensive operation on larger tables. Test the impact on non-production servers first, before you run this in production.
- All the searches would be conducted using the NOLOCK hint to reduce any locking related contention.
- In the stored procedure code, notice the use of the follwing: string_split table valued function, PARSENAME and QUOTENAME functions. They make like so much easier with string parsing. Please look up the documentation to learn more about these functions.
Here’s the stored procedure code:
IF OBJECT_ID('dbo.SearchAllTables', 'P') IS NOT NULL
BEGIN
PRINT 'Stored procedure dbo.SearchAllTables alredy exists. Dropping and recreating'
DROP PROC dbo.SearchAllTables
END
GO
CREATE PROC dbo.SearchAllTables
(
@DBName sysname,
@SearchString nvarchar(128),
@Top int = 0,
@ExcludeSchemas nvarchar(4000) = '',
@ExcludeTables nvarchar(4000) = '',
@ExcludeColumns nvarchar(4000) = '',
@Debug bit = 0
)
AS
BEGIN
--Created by Vyas Kondreddi
--http://dutydba.com
SET NOCOUNT ON
CREATE TABLE #ColumnList
(
SchemaName sysname,
TableName sysname,
ColumnName sysname
)
CREATE TABLE #Results
(
DBName sysname,
SchemaName sysname,
TableName sysname,
ColumnName sysname,
Result nvarchar(max)
)
DECLARE @CMD nvarchar(max) = '', @Rows int = 1
DECLARE @SchemaName sysname, @TableName sysname, @ColumnName sysname
SET @DBName = LTRIM(RTRIM(@DBName))
IF DB_ID(@DBName) IS NULL
BEGIN
RAISERROR('Database name %s is invalid', 11, 1, @DBName)
RETURN (-1)
END
IF LTRIM(COALESCE(@SearchString, '')) = ''
BEGIN
RAISERROR('Provide a valid search string', 11, 1, @DBName)
RETURN (-2)
END
--Command to identify all character type columns
SET @CMD = 'USE ' + QUOTENAME(@DBName) + ';
SELECT
QUOTENAME(OBJECT_SCHEMA_NAME(object_id)),
QUOTENAME(OBJECT_NAME(object_id)),
QUOTENAME(name)
FROM
sys.columns AS c
WHERE
system_type_id IN
(
35 --text
,99 --ntext
,167 --varchar
,175 --char
,231 --nvarchar
,239 --nchar
,231 --sysname
)
AND OBJECTPROPERTY(object_id, ''isMSShipped'') = 0
AND OBJECTPROPERTYEX(object_id, ''IsTable'') = 1
'
IF @Debug = 1 PRINT @CMD
--Store all character type columns into this table
INSERT INTO #ColumnList (SchemaName, TableName, ColumnName)
EXEC sp_executesql @CMD
--Process the exclusions by deleting qualifying columns,
--that have been excluded via the 3 input parameters
--Schema level exclusions
DELETE #ColumnList WHERE SchemaName IN
(
SELECT
CASE WHEN LEFT(LTRIM(RTRIM(value)), 1) <> '['
THEN '[' ELSE ''
END +
LTRIM(RTRIM(value)) +
CASE WHEN RIGHT(LTRIM(RTRIM(value)), 1) <> ']'
THEN ']' ELSE ''
END
FROM string_split(@ExcludeSchemas, ',')
)
--Table level exclusions
DELETE #ColumnList
FROM
#ColumnList AS cl
JOIN
string_split(@ExcludeTables, ',') AS spl
ON
cl.SchemaName =
CASE
WHEN LEFT(PARSENAME(LTRIM(RTRIM(value)), 2), 1) <> '['
THEN '['
ELSE ''
END +
PARSENAME(LTRIM(RTRIM(value)), 2) +
CASE WHEN RIGHT(PARSENAME(LTRIM(RTRIM(value)), 2), 1) <> ']'
THEN ']'
ELSE ''
END
AND cl.TableName =
CASE
WHEN LEFT(PARSENAME(LTRIM(RTRIM(value)), 1), 1) <> '['
THEN '['
ELSE ''
END +
PARSENAME(LTRIM(RTRIM(value)), 1) +
CASE
WHEN RIGHT(PARSENAME(LTRIM(RTRIM(value)), 1), 1) <> ']'
THEN ']'
ELSE ''
END
--Column level exclusions
DELETE #ColumnList
FROM
#ColumnList AS cl
JOIN
string_split(@ExcludeColumns, ',') AS spl
ON
cl.SchemaName =
CASE
WHEN LEFT(PARSENAME(LTRIM(RTRIM(value)), 3), 1) <> '['
THEN '['
ELSE ''
END +
PARSENAME(LTRIM(RTRIM(value)), 3) +
CASE
WHEN RIGHT(PARSENAME(LTRIM(RTRIM(value)), 3), 1) <> ']'
THEN ']'
ELSE ''
END
AND cl.TableName =
CASE
WHEN LEFT(PARSENAME(LTRIM(RTRIM(value)), 2), 1) <> '['
THEN '['
ELSE ''
END +
PARSENAME(LTRIM(RTRIM(value)), 2) +
CASE
WHEN RIGHT(PARSENAME(LTRIM(RTRIM(value)), 2), 1) <> ']'
THEN ']'
ELSE ''
END
AND cl.ColumnName =
CASE
WHEN LEFT(PARSENAME(LTRIM(RTRIM(value)), 1), 1) <> '['
THEN '['
ELSE ''
END +
PARSENAME(LTRIM(RTRIM(value)), 1) +
CASE
WHEN RIGHT(PARSENAME(LTRIM(RTRIM(value)), 1), 1) <> ']'
THEN ']'
ELSE ''
END
--Adding an IDENTITY column to have ID starting with 1
--Useful to identify each row by this number in the below loop
ALTER TABLE #ColumnList ADD CID int IDENTITY(1, 1) PRIMARY KEY
--Loop through all the qualifying columns
--and run the search by executing SELECT commands with NOLOCK hint
WHILE 1 = 1
BEGIN
SELECT
@SchemaName = SchemaName,
@TableName = TableName,
@ColumnName = ColumnName
FROM #ColumnList
WHERE CID = @Rows
IF @@ROWCOUNT = 0 BREAK
--Forming the search command
SET @CMD = '
USE ' + QUOTENAME(@DBName) + ';
SELECT ' + CASE WHEN COALESCE(@Top, 0) < 1 THEN '' ELSE ' TOP ' + CAST(@Top AS varchar(10)) + ' ' END
+ QUOTENAME(@DBName, '''') + ', ' + QUOTENAME(@SchemaName, '''') + ', ' +
QUOTENAME(@TableName, '''') + ', ' + QUOTENAME(@ColumnName, '''') + ', ' + @ColumnName + '
FROM ' + QUOTENAME(@DBName) + '.' + @SchemaName + '.' + @TableName + ' WITH (NOLOCK)
WHERE ' + @ColumnName + ' LIKE ' + QUOTENAME('%' + @SearchString + '%', '''')
IF @Debug = 1 PRINT @CMD
--Storing the returned results
INSERT INTO #Results (DBName, SchemaName, TableName, ColumnName, Result)
EXEC(@CMD)
SET @Rows += 1
END
--Final output
SELECT *
FROM #Results
ORDER BY
SchemaName,
TableName,
ColumnName,
Result
RETURN (0)
END
GO
IF OBJECT_ID('dbo.SearchAllTables', 'P') IS NOT NULL
BEGIN
PRINT 'Stored procedure dbo.SearchAllTables created successfully'
END
ELSE
BEGIN
PRINT 'Failed to create stored procedure dbo.SearchAllTables. See error(s) above'
END
GOExample stored procedure calls:
--A basic call to search for the word Singapore in the MyFlights database:
EXEC dbo.SearchAllTables
@DBName = 'MyFlights',
@SearchString = 'Singapore'
GO
--Same search as above, but restricts output to just 1 row per column seach
EXEC dbo.SearchAllTables
@DBName = 'MyFlights',
@SearchString = 'Singapore',
@Top = 1
GO
-- Search for the word Singapore in the MyFlights database
-- but exclude tables from the Staging and QA schemas
EXEC dbo.SearchAllTables
@DBName = 'MyFlights',
@SearchString = 'Singapore',
@ExcludeSchemas = 'Staging'
GO
-- Search for the word Singapore in the MyFlights database
-- but exclude tables from the Staging and QA schemas
-- as well as from the tables dbo.Trips and dbo.TripDetails
EXEC dbo.SearchAllTables
@DBName = 'MyFlights',
@SearchString = 'Singapore',
@ExcludeSchemas = 'Staging, QA',
@ExcludeTables = 'dbo.Trips,dbo.TripDetails'
GO
-- Same search as above, but additionally exclude
-- the columns dbo.Airports.Description and dbo.Airports.Country
EXEC dbo.SearchAllTables
@DBName = 'MyFlights',
@SearchString = 'Singapore',
@ExcludeSchemas = 'Staging, QA',
@ExcludeTables = 'dbo.Trips,dbo.TripDetails',
@ExcludeColumns = 'dbo.Airports.Description,dbo.Airports.Country'
GOHope you find this stored procedure helpful. As always, please share your thoughts by leaving a comment below. Thanks.
