String Search
It's been rare in my career that I've needed to find a string of text within any and all of the many possible tables of a database. For the few occasions when I did, I would just go to Google, find something that worked, run it, and be done.
More recently my need for this type of script has increased, and I began to notice that most of the code out there was either slow (even when doing an extremely small search), didn't return all of the fields I wanted to see in the output, didn't search all of the character data types I was interested in, gave limited control / filter options (if any), and / or needed to be run from within the context of the database that was being searched (AKA: not feasible to make it a stored procedure unless you are only ever going to search within a specific database or deploy the procedure to all existing and future databases).
Hence my inspiration for creating my own stored procedure which allows one to search a multitude of data types in any specified database and includes some optional input parameter options / filters.
NOTE: Depending on your environment and needs (number of tables / columns / search criteria, etc.) these types of queries have the possibility to run long and have a performance impact on the system. Always use common sense when running queries such as these and limit your search via the input parameter options rather than casting as wide a net as possible to see what you will get back as results (especially in a production or shared environment).
The procedure accepts the following input parameters:
-
@Search_String: Text string you wish to search for
-
@Database_Name: Database whose table(s) and / or view(s) you wish to search against
-
@Object_Types: Object types to search against (U for tables, V for views; default is both)
-
@Data_Types: Data types to search against (default includes CHAR, NCHAR, NTEXT, NVARCHAR, TEXT, VARCHAR, and XML)
-
@Table_Max_Rows: Limits the search to objects of a maximum row count, avoiding scans against large datasets (default is no limit, though using this option will remove NTEXT, TEXT, XML, and views from the search)
-
@Column_Max_Length: Limits the search to columns of a maximum defined data length; handy for when you only want to search against short description fields (default is no limit)
-
@Creation_Source: Search objects created by users (U - default value), SQL Server (S), or both (B)
The procedure will output the following values:
-
object_type: Indicates if the object containing the search result is a table or view
-
is_ms_shipped: Indicates if the object was created by SQL Server
-
data_type: Indicates the data type of the column containing the search result
-
data_length: Indicates the column length, as defined in the database, containing the search result
-
database_name: Indicates the database name the search was performed against
-
schema_name: Indicates the schema name the search result was found in
-
object_name: Indicates the object name the search result was found in
-
column_name: Indicates the column name the search result was found in
-
column_data: Full contents of the column containing the search criteria match
-
column_data_xml: When the contents of the column_data field are XML then this column will return the same data but converted to the XML data type
-
occurrences: Count of the total number of occurrences of the full content of the column_data field
To execute the stored procedure you must at least populate the @Search_String and @Database_Name input parameters:
EXECUTE dbo.usp_String_Search
@Search_String = N'random text'
,@Database_Name = N'my_database'
You can also use any or all of the optional input parameters to limit the scope of your search and produce results quicker while lessening the load on your server (recommended):
EXECUTE dbo.usp_String_Search
@Search_String = N'random text'
,@Database_Name = N'my_database'
,@Object_Types = N'U'
,@Data_Types = N'CHAR, NCHAR, NVARCHAR, VARCHAR'
,@Table_Max_Rows = 10000
,@Column_Max_Length = 50
,@Creation_Source = N'U'
Any friendly feedback is always welcome. Enjoy!
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
-----------------------------------------------------------------------------------------------------------------------------
-- Error Trapping: Check If Procedure Already Exists And Create Shell If Applicable
-----------------------------------------------------------------------------------------------------------------------------
IF OBJECT_ID (N'dbo.usp_String_Search', N'P') IS NULL
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.usp_String_Search AS SELECT 1 AS shell')
END
GO
-----------------------------------------------------------------------------------------------------------------------------
-- Stored Procedure Details: Listing Of Standard Details Related To The Stored Procedure
-----------------------------------------------------------------------------------------------------------------------------
-- Purpose: Search For A String Value Within Columns Of Data Types CHAR, NCHAR, NTEXT, NVARCHAR, TEXT, VARCHAR, XML
-- Create Date (MM/DD/YYYY): 03/20/2012
-- Developer: Sean Smith (s.smith.sql AT gmail DOT com)
-- Additional Notes: N/A
-----------------------------------------------------------------------------------------------------------------------------
-- Modification History: Listing Of All Modifications Since Original Implementation
-----------------------------------------------------------------------------------------------------------------------------
-- Description: Added "is_ms_shipped" Field
-- : Added Option To Include System Objects In Search
-- Date (MM/DD/YYYY): 12/06/2015
-- Developer: Sean Smith (s.smith.sql AT gmail DOT com)
-- Additional Notes: N/A
-----------------------------------------------------------------------------------------------------------------------------
-- Main Query: Create Procedure
-----------------------------------------------------------------------------------------------------------------------------
ALTER PROCEDURE dbo.usp_String_Search
@Search_String AS NVARCHAR (500)
,@Database_Name AS NVARCHAR (300)
,@Object_Types AS NVARCHAR (10) = NULL
,@Data_Types AS NVARCHAR (100) = NULL
,@Table_Max_Rows AS BIGINT = NULL
,@Column_Max_Length AS SMALLINT = NULL
,@Creation_Source AS NVARCHAR (1) = N'U'
WITH RECOMPILE
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
SET ARITHABORT OFF
SET ARITHIGNORE ON
SET TEXTSIZE 2147483647
DECLARE
@Creation_Filter AS NVARCHAR (30)
,@Loop_Column_Number AS BIGINT
,@Loop_Column_Number_First AS BIGINT
,@Loop_Object_Number AS BIGINT
,@Object_Name AS SYSNAME
,@Object_Prefix AS NVARCHAR (4)
,@Schema_Name AS SYSNAME
,@SQL_String_Full AS NVARCHAR (MAX)
,@SQL_String_IN_Column_Name AS NVARCHAR (MAX)
,@SQL_String_SELECT AS NVARCHAR (MAX)
,@SQL_String_WHERE AS NVARCHAR (MAX)
SET @Creation_Filter = (CASE @Creation_Source
WHEN N'S' THEN N'AND O.is_ms_shipped = 1'
WHEN N'U' THEN N'AND O.is_ms_shipped = 0'
ELSE N''
END)
SET @Object_Prefix = (CASE
WHEN @Creation_Source IN (N'B', N'S') THEN N'all_'
ELSE N''
END)
SET @Object_Types = N'''' + REPLACE (REPLACE (NULLIF (@Object_Types, N''), N' ', N''), N',', N''',''') + N''''
SET @Data_Types = N'''' + REPLACE (REPLACE (NULLIF (@Data_Types, N''), N' ', N''), N',', N''',''') + N''''
-----------------------------------------------------------------------------------------------------------------------------
-- Error Trapping I: Validate "@Creation_Source", "@Database_Name", And "@Search_String" Input Parameter Values
-----------------------------------------------------------------------------------------------------------------------------
IF @Creation_Source NOT IN (N'B', N'S', N'U')
BEGIN
RAISERROR
(
'ERROR: ''%s'' is not a valid creation source.
Valid Creation Sources:
B : Both (User and System created objects)
S : System created objects only
U : User created objects only'
,16
,1
,@Creation_Source
)
RETURN
END
IF EXISTS (SELECT * FROM master.sys.databases DB WHERE DB.name = @Database_Name)
BEGIN
SET @Database_Name = QUOTENAME (@Database_Name)
END
ELSE IF EXISTS (SELECT * FROM master.sys.databases DB WHERE QUOTENAME (DB.name) = @Database_Name + N']')
BEGIN
SET @Database_Name = @Database_Name + N']'
END
ELSE IF NOT EXISTS (SELECT * FROM master.sys.databases DB WHERE QUOTENAME (DB.name) = @Database_Name)
BEGIN
RAISERROR
(
N'ERROR: Database (''%s'') does not exist. Make sure that the name is entered correctly.'
,16
,1
,@Database_Name
)
RETURN
END
IF NULLIF (@Search_String, N'') IS NOT NULL
BEGIN
SET @Search_String = REPLACE (REPLACE (REPLACE (REPLACE (@Search_String, N'[', N'[[]'), N'%', N'[%]'), N'_', N'[_]'), N'''', N'''''')
END
ELSE BEGIN
RAISERROR
(
N'ERROR: @Search_String input parameter cannot be blank or NULL.'
,16
,1
)
RETURN
END
-----------------------------------------------------------------------------------------------------------------------------
-- Error Trapping II: Check If Temp Table(s) Already Exist(s) And Drop If Applicable
-----------------------------------------------------------------------------------------------------------------------------
IF OBJECT_ID (N'tempdb.dbo.#temp_string_search_objects_columns', N'U') IS NOT NULL
BEGIN
DROP TABLE dbo.#temp_string_search_objects_columns
END
IF OBJECT_ID (N'tempdb.dbo.#temp_string_search_results', N'U') IS NOT NULL
BEGIN
DROP TABLE dbo.#temp_string_search_results
END
-----------------------------------------------------------------------------------------------------------------------------
-- Table Creation: Create Temp Tables To Temporarily Store Searchable Objects / Columns And Output Results
-----------------------------------------------------------------------------------------------------------------------------
CREATE TABLE dbo.#temp_string_search_objects_columns
(
object_type VARCHAR (2) NOT NULL
,is_ms_shipped BIT NOT NULL
,data_type SYSNAME NOT NULL
,data_length SMALLINT NOT NULL
,[schema_name] SYSNAME NOT NULL
,[object_name] SYSNAME NOT NULL
,column_name SYSNAME NOT NULL
,schema_object_dense_rank BIGINT NOT NULL
,column_row_number BIGINT UNIQUE NOT NULL
,PRIMARY KEY CLUSTERED
(
schema_object_dense_rank
,column_row_number
)
)
CREATE TABLE dbo.#temp_string_search_results
(
[schema_name] SYSNAME NOT NULL
,[object_name] SYSNAME NOT NULL
,column_name SYSNAME NOT NULL
,column_data NVARCHAR (MAX) NOT NULL
,occurrences INT NOT NULL
)
-----------------------------------------------------------------------------------------------------------------------------
-- Table Update I: Insert Searchable Objects / Columns Into Temp Table
-----------------------------------------------------------------------------------------------------------------------------
SET @SQL_String_Full =
N'
SELECT
O.[type] AS object_type
,O.is_ms_shipped
,LOWER (TYPE_NAME (C.user_type_id) + ISNULL ((N'': [ '' + (CASE
WHEN C.system_type_id <> C.user_type_id THEN TYPE_NAME (C.system_type_id)
END) + N'' ]''), N'''')) AS data_type
,(CASE
WHEN LOWER (TYPE_NAME (C.system_type_id)) IN (N''nchar'', N''ntext'', N''nvarchar'') THEN CONVERT (VARCHAR (6), C.max_length / 2)
WHEN LOWER (TYPE_NAME (C.system_type_id)) NOT IN (N''bigint'', N''bit'', N''date'', N''datetime'', N''datetime2'', N''datetimeoffset'', N''decimal'', N''float'', N''int'', N''money'', N''numeric'', N''real'', N''smalldatetime'', N''smallint'', N''smallmoney'', N''time'', N''tinyint'') THEN CONVERT (VARCHAR (6), C.max_length)
ELSE CONVERT (VARCHAR (6), C.max_length) + '' ('' + CONVERT (VARCHAR (11), COLUMNPROPERTY (C.[object_id], C.name, ''Precision'')) + '','' + ISNULL (CONVERT (VARCHAR (11), COLUMNPROPERTY (C.[object_id], C.name, ''Scale'')), 0) + '')''
END) AS data_length
,S.name AS [schema_name]
,O.name AS [object_name]
,C.name AS column_name
,DENSE_RANK () OVER
(
ORDER BY
S.name
,O.name
) AS schema_object_dense_rank
,ROW_NUMBER () OVER
(
ORDER BY
C.name
) AS column_row_number
FROM
' + @Database_Name + N'.sys.schemas S
INNER JOIN ' + @Database_Name + N'.sys.' + @Object_Prefix + N'objects O ON O.[schema_id] = S.[schema_id]
AND O.[type] IN (' + (CASE
WHEN @Object_Types IS NOT NULL THEN @Object_Types
ELSE N'''U'', ''V'''
END) + N')
'
+ @Creation_Filter +
N'
INNER JOIN ' + @Database_Name + N'.sys.' + @Object_Prefix + N'columns C ON C.[object_id] = O.[object_id]
'
IF @Column_Max_Length > 0
BEGIN
SET @SQL_String_Full = @SQL_String_Full +
N'
' + NCHAR (9) + N'AND C.system_type_id IN (167, 175, 231, 239)
' + NCHAR (9) + N'AND (CASE
WHEN TYPE_NAME (C.system_type_id) IN (N''nchar'', N''ntext'', N''nvarchar'') THEN C.max_length / 2
ELSE C.max_length
END) BETWEEN 1 AND ' + CONVERT (NVARCHAR (6), @Column_Max_Length) + N'
'
END
ELSE BEGIN
SET @SQL_String_Full = @SQL_String_Full +
N'
' + NCHAR (9) + N'AND C.system_type_id IN (35, 99, 167, 175, 231, 239, 241)
'
END
SET @SQL_String_Full = @SQL_String_Full +
N'
INNER JOIN ' + @Database_Name + N'.sys.types T ON T.system_type_id = C.system_type_id
AND T.user_type_id = C.user_type_id
' + (CASE
WHEN @Data_Types IS NOT NULL THEN REPLICATE (NCHAR (9), 3) + N'AND T.name IN (' + @Data_Types + N')'
ELSE N''
END)
IF @Table_Max_Rows > 0
BEGIN
SET @SQL_String_Full = @SQL_String_Full +
N'
INNER JOIN
(
SELECT
DDPS.[object_id]
FROM
' + @Database_Name + N'.sys.dm_db_partition_stats DDPS
WHERE
DDPS.index_id < 2
GROUP BY
DDPS.[object_id]
HAVING
SUM (DDPS.row_count) <= ' + CONVERT (NVARCHAR (20), @Table_Max_Rows) + N'
) sqTMR ON sqTMR.[object_id] = O.[object_id]
'
END
INSERT INTO dbo.#temp_string_search_objects_columns
(
object_type
,is_ms_shipped
,data_type
,data_length
,[schema_name]
,[object_name]
,column_name
,schema_object_dense_rank
,column_row_number
)
EXECUTE (@SQL_String_Full)
-----------------------------------------------------------------------------------------------------------------------------
-- Table Update II: Insert Matched Results Into Temp Table
-----------------------------------------------------------------------------------------------------------------------------
SELECT TOP (1)
@Loop_Object_Number = X.schema_object_dense_rank
,@Schema_Name = X.[schema_name]
,@Object_Name = X.[object_name]
FROM
dbo.#temp_string_search_objects_columns X
ORDER BY
X.schema_object_dense_rank
WHILE @Loop_Object_Number IS NOT NULL
BEGIN
SET @SQL_String_SELECT = N''
SET @SQL_String_IN_Column_Name = N''
SET @SQL_String_WHERE = N''
SET @Loop_Column_Number = (SELECT MIN (X.column_row_number) FROM dbo.#temp_string_search_objects_columns X WHERE X.schema_object_dense_rank = @Loop_Object_Number)
SET @Loop_Column_Number_First = @Loop_Column_Number
WHILE @Loop_Column_Number IS NOT NULL
BEGIN
SELECT
@SQL_String_SELECT = @SQL_String_SELECT + (CASE
WHEN @Loop_Column_Number = @Loop_Column_Number_First THEN N'SELECT' + NCHAR (13) + REPLICATE (NCHAR (9), 7) + N' '
ELSE NCHAR (13) + REPLICATE (NCHAR (9), 7) + N','
END) + N'CONVERT (NVARCHAR (MAX), (CASE
WHEN ' + (CASE
WHEN X.data_type = N'XML' THEN N'CONVERT (NVARCHAR (MAX), '
ELSE N''
END) + QUOTENAME (X.column_name) + (CASE
WHEN X.data_type = N'XML' THEN N')'
ELSE N''
END) + N' LIKE ''%' + @Search_String + N'%'' THEN ' + QUOTENAME (X.column_name) + N'
ELSE NULL
END)) AS ' + QUOTENAME (X.column_name)
,@SQL_String_IN_Column_Name = @SQL_String_IN_Column_Name + (CASE
WHEN @Loop_Column_Number = @Loop_Column_Number_First THEN N''
ELSE N', '
END) + QUOTENAME (X.column_name)
,@SQL_String_WHERE = @SQL_String_WHERE + (CASE
WHEN @Loop_Column_Number = @Loop_Column_Number_First THEN NCHAR (13) + REPLICATE (NCHAR (9), 6) + N'WHERE' + NCHAR (13) + REPLICATE (NCHAR (9), 7)
ELSE NCHAR (13) + REPLICATE (NCHAR (9), 7) + N'OR '
END) + (CASE
WHEN X.data_type = N'XML' THEN N'CONVERT (NVARCHAR (MAX), '
ELSE N''
END) + QUOTENAME (X.column_name) + (CASE
WHEN X.data_type = N'XML' THEN N')'
ELSE N''
END) + N' LIKE ''%' + @Search_String + N'%'''
FROM
dbo.#temp_string_search_objects_columns X
WHERE
X.schema_object_dense_rank = @Loop_Object_Number
AND X.column_row_number = @Loop_Column_Number
SET @Loop_Column_Number = (SELECT MIN (X.column_row_number) FROM dbo.#temp_string_search_objects_columns X WHERE X.schema_object_dense_rank = @Loop_Object_Number AND X.column_row_number > @Loop_Column_Number)
END
SET @SQL_String_Full =
(
N'
SELECT
''' + @Schema_Name + N''' AS [schema_name]
,''' + @Object_Name + N''' AS [object_name]
,UNPV.column_name
,UNPV.column_data
,COUNT (*) AS occurrences
FROM
(
'
+ @SQL_String_SELECT
+ NCHAR (13) + REPLICATE (NCHAR (9), 6) + N'FROM' + NCHAR (13)
+ REPLICATE (NCHAR (9), 7) + @Database_Name
+ N'.'
+ QUOTENAME (@Schema_Name)
+ N'.'
+ QUOTENAME (@Object_Name)
+ @SQL_String_WHERE
+ N'
) sqMAT
UNPIVOT
(
column_data FOR column_name IN
(
' + @SQL_String_IN_Column_Name + N'
)
) UNPV
GROUP BY
UNPV.column_name
,UNPV.column_data
'
)
BEGIN TRY
INSERT INTO dbo.#temp_string_search_results
(
[schema_name]
,[object_name]
,column_name
,column_data
,occurrences
)
EXECUTE (@SQL_String_Full)
END TRY
BEGIN CATCH
END CATCH
SELECT TOP (1)
@Loop_Object_Number = X.schema_object_dense_rank
,@Schema_Name = X.[schema_name]
,@Object_Name = X.[object_name]
FROM
dbo.#temp_string_search_objects_columns X
WHERE
X.schema_object_dense_rank = @Loop_Object_Number + 1
ORDER BY
X.schema_object_dense_rank
IF @@ROWCOUNT = 0
BEGIN
SET @Loop_Object_Number = NULL
END
END
-----------------------------------------------------------------------------------------------------------------------------
-- Main Query: Final Display / Output
-----------------------------------------------------------------------------------------------------------------------------
SELECT
(CASE Y.object_type
WHEN 'U' THEN 'Table'
WHEN 'V' THEN 'View'
ELSE 'ERROR'
END) AS object_type
,(CASE
WHEN Y.is_ms_shipped = 1 THEN 'Yes'
ELSE 'No'
END) AS is_ms_shipped
,Y.data_type
,Y.data_length
,DB_NAME (DB_ID (SUBSTRING (@Database_Name, 2, LEN (@Database_Name) - 2))) AS database_name
,Y.[schema_name]
,Y.[object_name]
,Y.column_name
,Z.column_data
,CONVERT (XML, (CASE
WHEN Y.data_type = N'XML' THEN Z.column_data
ELSE N''
END)) AS column_data_xml
,Z.occurrences
FROM
dbo.#temp_string_search_objects_columns Y
INNER JOIN dbo.#temp_string_search_results Z ON Z.[schema_name] = Y.[schema_name]
AND Z.[object_name] = Y.[object_name]
AND Z.column_name = Y.column_name
ORDER BY
Y.object_type
,Y.is_ms_shipped
,Y.[schema_name]
,Y.[object_name]
,Y.column_name
,Z.column_data
-----------------------------------------------------------------------------------------------------------------------------
-- Cleanup: Drop Any Remaining Temp Tables
-----------------------------------------------------------------------------------------------------------------------------
IF OBJECT_ID (N'tempdb.dbo.#temp_string_search_objects_columns', N'U') IS NOT NULL
BEGIN
DROP TABLE dbo.#temp_string_search_objects_columns
END
IF OBJECT_ID (N'tempdb.dbo.#temp_string_search_results', N'U') IS NOT NULL
BEGIN
DROP TABLE dbo.#temp_string_search_results
END
GO
