SQL Server Search

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