fqqycTFk

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