SQL Server Search
From WickyWiki
This is an example to search a SQL Server database for specific values. This can be used to analyse the data and learn about its relationships and structure.
BEGIN
-- PARAMETERS, possible wildcards:
-- _ single character
-- [abc] single character a,b or c
-- [^abc] single character NOT a,b or c
-- % zero or more characters
DECLARE @SchemaPtr nvarchar(100)='IPLN_E'
DECLARE @SearchPtr nvarchar(100)='%'+'account'+'%'
DROP TABLE IF EXISTS #Results
CREATE TABLE #Results (TableName nvarchar(260), ColumnName nvarchar(130), ColumnValue nvarchar(3630))
DECLARE @TableName nvarchar(256)
, @ColumnName nvarchar(128)
, @ColumnType nvarchar(128)
SET @TableName = ''
INSERT INTO #Results(TableName, ColumnName, ColumnValue)
-- tablename
select 'INFORMATION_SCHEMA.TABLES', 'TABLE_NAME', TABLE_NAME
from INFORMATION_SCHEMA.TABLES
where TABLE_SCHEMA LIKE @SchemaPtr and TABLE_NAME like @SearchPtr
-- columnname
UNION select 'INFORMATION_SCHEMA.COLUMNS', 'COLUMN_NAME', COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA LIKE @SchemaPtr and COLUMN_NAME like @SearchPtr
SET @SearchPtr = QUOTENAME(@SearchPtr,'''')
DECLARE CUR CURSOR FOR
-- all tables
SELECT CONCAT(QUOTENAME(t.TABLE_SCHEMA),'.',QUOTENAME(t.TABLE_NAME)) as TableName
, QUOTENAME(c.COLUMN_NAME) as ColumnName
, c.DATA_TYPE as ColumnType
FROM INFORMATION_SCHEMA.TABLES as t
JOIN INFORMATION_SCHEMA.COLUMNS as c
ON c.TABLE_SCHEMA = t.TABLE_SCHEMA
AND c.TABLE_NAME = t.TABLE_NAME
WHERE t.TABLE_SCHEMA LIKE @SchemaPtr
AND t.TABLE_TYPE = 'BASE TABLE'
-- ignore system tables:
AND OBJECTPROPERTY(OBJECT_ID(CONCAT(QUOTENAME(t.TABLE_SCHEMA),'.',QUOTENAME(t.TABLE_NAME))), 'IsMSShipped') = 0
OPEN CUR
FETCH NEXT FROM CUR INTO @TableName, @ColumnName, @ColumnType
WHILE @@FETCH_STATUS = 0
BEGIN
-- ignore types that have a problem with LIKE:
IF @ColumnType<>'hierarchyid'
BEGIN
INSERT INTO #Results(TableName, ColumnName, ColumnValue)
EXEC ( 'SELECT ''' + @TableName + ''','''
+ @ColumnName + ''', LEFT(CONVERT(varchar(max), '
+ @ColumnName + '), 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE CONVERT(varchar(max), ' + @ColumnName + ') LIKE ' + @SearchPtr )
END
FETCH NEXT FROM CUR INTO @TableName, @ColumnName, @ColumnType
END
CLOSE CUR
DEALLOCATE CUR
-- results
SELECT distinct
'select * from ' + TableName + ' where ' + ColumnName
+ '='''+ REPLACE(ColumnValue,'''','''''') + '''' as qry
FROM #Results
END
GO
See also
