1. DECLARE @SearchStrColumnName nvarchar(100), @SearchStrColumnValue nvarchar(100), @SearchStrInXML bit
  2. SET @SearchStrColumnValue = '%OWNED%' /* use LIKE syntax */
  3. SET @SearchStrColumnName = NULL /* NULL for all, use LIKE syntax */
  4. SET @SearchStrInXML = 0 /* Searching XML data may be slow */
  6. IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results
  8. CREATE TABLE #Results (TableName nvarchar(128), ColumnName nvarchar(128), ColumnValue nvarchar(max),ColumnType nvarchar(20))
  10. SET NOCOUNT ON
  12. DECLARE @TableName nvarchar(256) = '',@ColumnName nvarchar(128),@ColumnType nvarchar(20), @QuotedSearchStrColumnValue nvarchar(110), @QuotedSearchStrColumnName nvarchar(110)

  14. SET @QuotedSearchStrColumnValue = QUOTENAME(@SearchStrColumnValue,'''')

  16. DECLARE @ColumnNameTable TABLE (COLUMN_NAME nvarchar(128),DATA_TYPE nvarchar(20))
  18. WHILE @TableName IS NOT NULL
  19. BEGIN
  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 QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
  26. AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
  27. )
  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'',''bigint'',' ELSE '' END + '''char'',''varchar'',''nchar'',''nvarchar''' + 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)'

  39. INSERT INTO @ColumnNameTable
  40. EXEC (@sql)
  41. WHILE EXISTS (SELECT TOP 1 COLUMN_NAME FROM @ColumnNameTable)
  42. BEGIN
  43. SELECT TOP 1 @ColumnName = COLUMN_NAME,@ColumnType = DATA_TYPE FROM @ColumnNameTable
  44. SET @sql = 'SELECT ''' + @TableName + ''',''' + @ColumnName + ''',' + CASE @SearchStrInXML WHEN 1 THEN 'LEFT(CAST(' + @ColumnName + ' AS nvarchar(MAX)), 4096),''' ELSE 'LEFT(' + @ColumnName + ', 4096),''' END + @ColumnType + '''
  45. FROM ' + @TableName + ' (NOLOCK) ' +
  46. ' WHERE ' + CASE @SearchStrInXML WHEN 1 THEN 'CAST(' + @ColumnName + ' AS nvarchar(MAX))' ELSE @ColumnName END + ' LIKE ' + @QuotedSearchStrColumnValue
  47. INSERT INTO #Results
  48. EXEC(@sql)
  49. DELETE FROM @ColumnNameTable WHERE COLUMN_NAME = @ColumnName
  50. END
  51. END
  52. END

  54. SET NOCOUNT OFF
  55. SELECT TableName, ColumnName, ColumnValue, ColumnType, COUNT(*) AS Count FROM #Results
  56. GROUP BY TableName, ColumnName, ColumnValue, ColumnType
Query Output: