Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Search all tables in a database
DECLARE @SearchStr nvarchar(100) = 'brent'
DECLARE @SearchSchema nvarchar(100) = 'dbo'
CREATE TABLE #Results (
TableName nvarchar(255),
ColumnName nvarchar(255),
ColumnValue nvarchar(MAX))
SET NOCOUNT ON
DECLARE @TableName nvarchar(255) = '',
@ColumnName nvarchar(255),
@SearchStr2 nvarchar(255) = 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
TABLE_SCHEMA = @SearchSchema
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')
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
TableName,
ColumnName,
ColumnValue,
COUNT(*)
FROM #Results
GROUP BY TableName, ColumnName, ColumnValue
DROP TABLE #Results
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment