Search any table for value (wildcard)

This code is a SQL script that searches for a specific string (@SearchStr) across all columns of all tables in a specified database. Here's a breakdown of how it works (replace with your own values for the database name and SearchStr):

USE [You_Database_Name] -- replace with your actual DB name
GO

DECLARE @SearchStr varchar(255)
SET @SearchStr = 'Your_Search_Value' -- replace with your search value

DECLARE @Results TABLE(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', 'numeric')
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

 

  1. DECLARE statements are used to declare variables.
  2. SET statements are used to assign values to variables.
  3. DECLARE @Results TABLE(ColumnName nvarchar(370), ColumnValue nvarchar(3630)): This declares a table variable @Results with two columns: ColumnName to store the name of the column where the search string is found, and ColumnValue to store the value of the column where the search string is found.
  4. SET NOCOUNT ON: This prevents the count of the number of rows affected by a Transact-SQL statement from being returned as part of the result set.
  5. Two nested WHILE loops are used to iterate over all tables and columns in the database.
  6. The outer loop iterates over tables (@TableName) and the inner loop iterates over columns (@ColumnName) of each table.
  7. Inside the loops, SQL queries are dynamically generated and executed to search for the given string (@SearchStr) in each column of each table.
  8. The results of the search are inserted into the @Results table variable.
  9. Finally, the contents of the @Results table variable are selected and returned as the output of the script.

This script essentially performs a wildcard search for a specific string across all columns of all tables in the specified database and returns the results. It's commonly used for tasks like data auditing or troubleshooting.

No data are modified and the performance of the query depends on the size of the database.

 

No files yet, migration hasn't completed yet!