VKYX192D

1DECLARE @SearchStrTableName nvarchar(255), @SearchStrColumnName nvarchar(255), @SearchStrColumnValue nvarchar(255), @SearchStrInXML bit, @FullRowResult bit, @FullRowResultRows int
2SET @SearchStrColumnValue = '%groep 28 EUV%' /* 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
20SET @TableName =
21(
22SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
23FROM INFORMATION_SCHEMA.TABLES
24WHERE TABLE_TYPE = 'BASE TABLE'
25AND TABLE_NAME LIKE COALESCE(@SearchStrTableName,TABLE_NAME)
26AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
27AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
28)
29IF @TableName IS NOT NULL
30BEGIN
31DECLARE @sql VARCHAR(MAX)
32SET @sql = 'SELECT QUOTENAME(COLUMN_NAME),DATA_TYPE
33FROM INFORMATION_SCHEMA.COLUMNS
34WHERE TABLE_SCHEMA = PARSENAME(''' + @TableName + ''', 2)
35AND TABLE_NAME = PARSENAME(''' + @TableName + ''', 1)
36AND 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 + ')
37AND COLUMN_NAME LIKE COALESCE(' + CASE WHEN @SearchStrColumnName IS NULL THEN 'NULL' ELSE '''' + @SearchStrColumnName + '''' END + ',COLUMN_NAME)'
38INSERT INTO @ColumnNameTable
39EXEC (@sql)
40WHILE EXISTS (SELECT TOP 1 COLUMN_NAME FROM @ColumnNameTable)
41BEGIN
42PRINT @ColumnName
43SELECT TOP 1 @ColumnName = COLUMN_NAME,@ColumnType = DATA_TYPE FROM @ColumnNameTable
44SET @sql = 'SELECT ''' + @TableName + ''',''' + @ColumnName + ''',' + CASE @ColumnType WHEN 'xml' THEN 'LEFT(CAST(' + @ColumnName + ' AS nvarchar(MAX)), 4096),'''
45WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + '),'''
46ELSE 'LEFT(' + @ColumnName + ', 4096),''' END + @ColumnType + '''
47FROM ' + @TableName + ' (NOLOCK) ' +
48' WHERE ' + CASE @ColumnType WHEN 'xml' THEN 'CAST(' + @ColumnName + ' AS nvarchar(MAX))'
49WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + ')'
50ELSE @ColumnName END + ' LIKE ' + @QuotedSearchStrColumnValue
51INSERT INTO #Results
52EXEC(@sql)
53IF @@ROWCOUNT > 0 IF @FullRowResult = 1
54BEGIN
55SET @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))'
58WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + ')'
59ELSE @ColumnName END + ' LIKE ' + @QuotedSearchStrColumnValue
60EXEC(@sql)
61END
62DELETE FROM @ColumnNameTable WHERE COLUMN_NAME = @ColumnName
63END 
64END
65END
66SET NOCOUNT OFF
67
68SELECT TableName, ColumnName, ColumnValue, ColumnType, COUNT(*) AS Count FROM #Results
69GROUP BY TableName, ColumnName, ColumnValue, ColumnType