Continue reading to view and download the SQL script.
*I decided to apply a filter by column type on which columns to search, in case the types of some columns throw casting errors on your search value or has other general problems with the WHERE clause.
If you want to see the full list of types in your database, you can run the following query:
-- Variables to modify
-- @ColumnTypes: Comma-separated value of database types. The script only performs the search clause on
-- @SearchString: The value to search for.
-- @SearchClause: The template of the
DECLARE @SearchString NVARCHAR(MAX),
@SearchClause NVARCHAR(MAX),
@ColumnTypes NVARCHAR(MAX)
--SET @ColumnTypes = 'INT'
--SET @ColumnTypes = 'xml,char,nchar,ntext,nvarchar,text,uniqueidentifier,varchar'
--------------------------------------------------------------------------------------
-- Modify @ColumnTypes to set the type of columns to search on
--------------------------------------------------------------------------------------
SET @ColumnTypes = 'xml,char,nchar,ntext,nvarchar,text,uniqueidentifier,varchar'
--------------------------------------------------------------------------------------
-- Modify @SearchString to set the value you're searching on
--------------------------------------------------------------------------------------
SET @SearchString = 'Waldorf'
IF(LEN(ISNULL(@SearchClause, '')) = 0)
BEGIN
--------------------------------------------------------------------------------------
-- Modify @SearchClause change your search criteria (i.e. = v.s. LIKE, if you want % in your LIKE search clauses. CAST/etc
-- may also go here. [<>] is replaced by the name of the column
--------------------------------------------------------------------------------------
SET @SearchClause = '[<>] LIKE ''%' + @SearchString + '%'''
END
-- Table used for storing the (table, column)-pairs that satisfy our @SearchClause
DECLARE @SearchResultRow TABLE
(
TableName NVARCHAR(512),
ColumnName NVARCHAR(512),
OrdinalPosition INT
)
DECLARE @Column TABLE
(
TableName NVARCHAR(512),
ColumnName NVARCHAR(512),
DataType NVARCHAR(512),
CharacterMaximumLength INT,
OrdinalPosition INT
)
-- Get list of all columns in the database that are of the column type we're searching on.
INSERT INTO @Column
(
TableName,
ColumnName,
DataType,
CharacterMaximumLength,
OrdinalPosition
)
SELECT TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
ORDINAL_POSITION
FROM Information_Schema.Columns
WHERE ',' + @ColumnTypes + ',' LIKE '%,' + DATA_TYPE + ',%'
-- Used to keep track of every (table, column)-pair.
DECLARE @TableColumn TABLE
(
TableName NVARCHAR(512),
ColumnName NVARCHAR(512),
DataType NVARCHAR(512),
CharacterMaximumLength INT,
OrdinalPosition INT
)
DECLARE @CurrentTableName NVARCHAR(512)
SET @CurrentTableName =
(
SELECT TOP 1 TableName
FROM @Column
ORDER BY TableName
)
-- The template of the query that is executed on each row from @Column
-- to see if it satisfies our @SearchClause.
DECLARE @SqlTestTemplate NVARCHAR(MAX)
SET @SqlTestTemplate =
'SELECT [TableName],
[ColumnName],
[OrdinalPosition]
FROM
(
SELECT ''[<>]'' AS TableName,
''[<>]'' AS ColumnName,
[<>] AS OrdinalPosition
) AS TableColumn
WHERE EXISTS
(
SELECT [<>]
FROM [<>]
WHERE ' + @SearchClause + '
)'
-- Cycle through and find each (table, column)-pair satisfying our @SearchClause
WHILE(@CurrentTableName IS NOT NULL) -- Cycle through each table.
BEGIN
DELETE
FROM @TableColumn
-- Insert every (table, column)-pair into TableColumn for the current Table
INSERT INTO
@TableColumn
(
TableName,
ColumnName,
DataType,
CharacterMaximumLength,
OrdinalPosition
)
SELECT TableName,
ColumnName,
DataType,
CharacterMaximumLength,
OrdinalPosition
FROM @Column AS [Column]
WHERE [Column].TableName = @CurrentTableName
-- SELECT *
-- FROM @TableColumn
-- ORDER BY OrdinalPosition
DECLARE @CurrentColumnName NVARCHAR(512),
@OrdinalPosition INT
SET @CurrentColumnName =
(
SELECT TOP 1 ColumnName
FROM @TableColumn AS [TableColumn]
ORDER BY OrdinalPosition
)
SET @OrdinalPosition =
(
SELECT TOP 1 OrdinalPosition
FROM @TableColumn AS [TableColumn]
WHERE [TableColumn].[ColumnName] = @CurrentColumnName
)
WHILE(@CurrentColumnName IS NOT NULL) -- Cycle through each column for the current table.
BEGIN
--SELECT @CurrentTableName, @CurrentColumnName
-- Build our sql test for a column from the @SqlTestTemplate variable.
DECLARE @ColumnTestSql NVARCHAR(MAX)
SET @ColumnTestSql = REPLACE(@SqlTestTemplate, '[<>]', '[' + @CurrentTableName + ']')
SET @ColumnTestSql = REPLACE(@ColumnTestSql, '[<>]', '[' + @CurrentColumnName + ']')
SET @ColumnTestSql = REPLACE(@ColumnTestSql, '[<>]', CAST(@OrdinalPosition AS NVARCHAR(512)))
-- PRINT @ColumnTestSql
-- Insert record of (table, column)-pair satisfying our criteria.
INSERT INTO @SearchResultRow
(
TableName,
ColumnName,
OrdinalPosition
)
EXEC sp_executesql @ColumnTestSql
-- Get the next column we're going to search on and get its ordinal position (for looping
-- through the columns in a table).
SET @CurrentColumnName =
(
SELECT TOP 1 ColumnName
FROM @TableColumn AS [TableColumn]
WHERE OrdinalPosition > @OrdinalPosition
ORDER BY OrdinalPosition
)
SET @OrdinalPosition =
(
SELECT TOP 1 OrdinalPosition
FROM @TableColumn AS [TableColumn]
WHERE [TableColumn].[ColumnName] = @CurrentColumnName
)
END -- WHILE(@CurrentColumnName IS NOT NULL)
-- Get next table we going to test (for looping through our list of tables)
SET @CurrentTableName =
(
SELECT TOP 1 TableName
FROM @Column AS [Column]
WHERE [Column].TableName > @CurrentTableName
ORDER BY TableName
)
END -- WHILE(@CurrentTableName IS NOT NULL)
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------- The list of all tables that have a value that --------------
------------------- satisfies our SearchClause in at least one row -------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SELECT DISTINCT TableName
FROM @SearchResultRow AS SearchResultRow
ORDER BY TableName
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-------- The list of all (table, colum)-pairs that have a value that -----------
------------------- satisfies our SearchClause in at least one row -------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SELECT TableName, ColumnName
FROM @SearchResultRow AS SearchResultRow
ORDER BY TableName, OrdinalPosition, ColumnName
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------- Building Queries -----------------------------------
-- This code is for building the select/sql queries for getting the matching ---
-- rows from each of the tables that we know has a matching value somewhere ----
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
DECLARE @SearchResultQuery TABLE
(
TableName NVARCHAR(512),
ColumnList NVARCHAR(MAX),
WhereClause NVARCHAR(MAX)
)
DECLARE @QueryTableName NVARCHAR(512)
DECLARE @QueryColumn NVARCHAR(512)
SET @QueryTableName =
(
SELECT TOP 1 TableName
FROM (
SELECT DISTINCT TableName
FROM @SearchResultRow AS SearchResultRow
) AS MatchingTable
ORDER BY TableName
)
WHILE(@QueryTableName IS NOT NULL)
BEGIN
DECLARE @QueryColumnName NVARCHAR(512),
@QueryColumnOrdinalPosition INT
SET @QueryColumnName = (
SELECT TOP 1 ColumnName
FROM @SearchResultRow AS SearchResultRow
WHERE TableName = @QueryTableName
ORDER BY TableName, OrdinalPosition, ColumnName
)
SET @QueryColumnOrdinalPosition = (
SELECT TOP 1 OrdinalPosition
FROM @SearchResultRow AS SearchResultRow
WHERE TableName = @QueryTableName
AND ColumnName = @QueryColumnName
)
INSERT INTO @SearchResultQuery
(
TableName,
ColumnList,
WhereClause
)
VALUES
(
@QueryTableName,
'',
''
)
WHILE(@QueryColumnName IS NOT NULL)
BEGIN
DECLARE @ColumnList NVARCHAR(MAX),
@WhereClause NVARCHAR(MAX),
@AdditionalWhereClause NVARCHAR(MAX)
SELECT @ColumnList = ColumnList,
@WhereClause = WhereClause
FROM @SearchResultQuery AS SearchResultQuery
WHERE SearchResultQuery.TableName = @QueryTableName
SET @AdditionalWhereClause = REPLACE(@SearchClause, '[<>]', @QueryColumnName)
IF(LEN(@WhereClause) <> 0)
BEGIN
SET @WhereClause = @WhereClause + ' OR '
END
SET @WhereClause = @WhereClause + @AdditionalWhereClause
IF(LEN(@ColumnList) <> 0)
BEGIN
SET @ColumnList = @ColumnList + ',
'
END
SET @ColumnList = @ColumnList + @QueryColumnName
UPDATE @SearchResultQuery
SET ColumnList = @ColumnList,
WhereClause = @WhereClause
WHERE TableName = @QueryTableName
-- PRINT @QueryTableName + ': ' + @QueryColumnName
SET @QueryColumnName = (
SELECT TOP 1 ColumnName
FROM @SearchResultRow AS SearchResultRow
WHERE SearchResultRow.TableName = @QueryTableName
AND SearchResultRow.OrdinalPosition > @QueryColumnOrdinalPosition
ORDER BY TableName, OrdinalPosition, ColumnName
)
SET @QueryColumnOrdinalPosition = (
SELECT TOP 1 OrdinalPosition
FROM @SearchResultRow AS SearchResultRow
WHERE TableName = @QueryTableName
AND ColumnName = @QueryColumnName
)
END
------------------------ WHILE(@QueryColumnName IS NOT NULL)
SET @QueryTableName =
(
SELECT TOP 1 TableName
FROM (
SELECT DISTINCT TableName
FROM @SearchResultRow AS SearchResultRow
WHERE SearchResultRow.TableName > @QueryTableName
) AS MatchingTable
ORDER BY TableName
)
END
------------------------- WHILE(@QueryTableName IS NOT NULL)
------------------------------------------------------------------
-------- Spit out the sql queries we were just building ----------
------------------------------------------------------------------
SELECT '
SELECT ' + ColumnList + ', *
FROM ' + TableName + '
WHERE ' + WhereClause
FROM @SearchResultQuery AS SearchResultQuery
PRINT 'Done'