ddHz3acj

1-- This isn't my code, found online somewhere in a Google search, forgot to save the link...
2
3DECLARE @SearchStrTableName nvarchar(255), @SearchStrColumnName nvarchar(255), @SearchStrColumnValue nvarchar(255), @SearchStrInXML bit, @FullRowResult bit, @FullRowResultRows int
4SET @SearchStrColumnValue = '%searchthis%' /* use LIKE syntax */
5SET @FullRowResult = 1
6SET @FullRowResultRows = 3
7SET @SearchStrTableName = NULL /* NULL for all tables, uses LIKE syntax */
8SET @SearchStrColumnName = NULL /* NULL for all columns, uses LIKE syntax */
9SET @SearchStrInXML = 0 /* Searching XML data may be slow */
10
11IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results
12CREATE TABLE #Results (TableName nvarchar(128), ColumnName nvarchar(128), ColumnValue nvarchar(max),ColumnType nvarchar(20))
13
14SET NOCOUNT ON
15
16DECLARE @TableName nvarchar(256) = '',@ColumnName nvarchar(128),@ColumnType nvarchar(20), @QuotedSearchStrColumnValue nvarchar(110), @QuotedSearchStrColumnName nvarchar(110)
17SET @QuotedSearchStrColumnValue = QUOTENAME(@SearchStrColumnValue,'''')
18DECLARE @ColumnNameTable TABLE (COLUMN_NAME nvarchar(128),DATA_TYPE nvarchar(20))
19
20WHILE @TableName IS NOT NULL
21BEGIN
22    SET @TableName = 
23    (
24        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
25        FROM    INFORMATION_SCHEMA.TABLES
26        WHERE       TABLE_TYPE = 'BASE TABLE'
27            AND TABLE_NAME LIKE COALESCE(@SearchStrTableName,TABLE_NAME)
28            AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
29            AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
30    )
31    IF @TableName IS NOT NULL
32    BEGIN
33        DECLARE @sql VARCHAR(MAX)
34        SET @sql = 'SELECT QUOTENAME(COLUMN_NAME),DATA_TYPE
35                FROM    INFORMATION_SCHEMA.COLUMNS
36                WHERE       TABLE_SCHEMA    = PARSENAME(''' + @TableName + ''', 2)
37                AND TABLE_NAME  = PARSENAME(''' + @TableName + ''', 1)
38                AND DATA_TYPE IN (' + CASE WHEN ISNUMERIC(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@SearchStrColumnValue,'%',''),'_',''),'[',''),']',''),'-','')) = 1 THEN '''tinyint'',''int'',''smallint'',''bigint'',''numeric'',''decimal'',''smallmoney'',''money'',' ELSE '' END + '''char'',''varchar'',''nchar'',''nvarchar'',''timestamp'',''uniqueidentifier''' + CASE @SearchStrInXML WHEN 1 THEN ',''xml''' ELSE '' END + ')
39                AND COLUMN_NAME LIKE COALESCE(' + CASE WHEN @SearchStrColumnName IS NULL THEN 'NULL' ELSE '''' + @SearchStrColumnName + '''' END  + ',COLUMN_NAME)'
40        INSERT INTO @ColumnNameTable
41        EXEC (@sql)
42        WHILE EXISTS (SELECT TOP 1 COLUMN_NAME FROM @ColumnNameTable)
43        BEGIN
44            PRINT @ColumnName
45            SELECT TOP 1 @ColumnName = COLUMN_NAME,@ColumnType = DATA_TYPE FROM @ColumnNameTable
46            SET @sql = 'SELECT ''' + @TableName + ''',''' + @ColumnName + ''',' + CASE @ColumnType WHEN 'xml' THEN 'LEFT(CAST(' + @ColumnName + ' AS nvarchar(MAX)), 4096),''' 
47            WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + '),'''
48            ELSE 'LEFT(' + @ColumnName + ', 4096),''' END + @ColumnType + ''' 
49                    FROM ' + @TableName + ' (NOLOCK) ' +
50                    ' WHERE ' + CASE @ColumnType WHEN 'xml' THEN 'CAST(' + @ColumnName + ' AS nvarchar(MAX))' 
51                    WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + ')'
52                    ELSE @ColumnName END + ' LIKE ' + @QuotedSearchStrColumnValue
53            INSERT INTO #Results
54            EXEC(@sql)
55            IF @@ROWCOUNT > 0 IF @FullRowResult = 1 
56            BEGIN
57                SET @sql = 'SELECT TOP ' + CAST(@FullRowResultRows AS VARCHAR(3)) + ' ''' + @TableName + ''' AS [TableFound],''' + @ColumnName + ''' AS [ColumnFound],''FullRow>'' AS [FullRow>],*' +
58                    ' FROM ' + @TableName + ' (NOLOCK) ' +
59                    ' WHERE ' + CASE @ColumnType WHEN 'xml' THEN 'CAST(' + @ColumnName + ' AS nvarchar(MAX))' 
60                    WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + ')'
61                    ELSE @ColumnName END + ' LIKE ' + @QuotedSearchStrColumnValue
62                EXEC(@sql)
63            END
64            DELETE FROM @ColumnNameTable WHERE COLUMN_NAME = @ColumnName
65        END 
66    END
67END
68SET NOCOUNT OFF
69
70SELECT TableName, ColumnName, ColumnValue, ColumnType, COUNT(*) AS Count FROM #Results
71GROUP BY TableName, ColumnName, ColumnValue, ColumnType