Query
sp_msforeachdb '
DECLARE
@SearchText VARCHAR(MAX) = ''a''
DECLARE @SQL
VARCHAR(MAX) = ''SELECT [Database Name] = ''''?'''', [Schema Name] =
TABLESCHEMA, [Table Name] = TABLENAME, [Column Name] = COLUMNNAME, [Occur
Count] = SUM(CNT) FROM ('' + STUFF(
(SELECT '' UNION ALL SELECT TABLESCHEMA = '''''' + TABLES.TABLE_SCHEMA +
'''''', TABLENAME = '''''' + TABLES.TABLE_NAME + '''''', COLUMNNAME =
CAST('''''' + COLUMN_NAME + '''''' AS VARCHAR(MAX)), CNT = 1 FROM ?.['' +
TABLES.TABLE_SCHEMA + ''].['' + TABLES.TABLE_NAME + ''] WHERE '' + CASE WHEN
DATA_TYPE = ''image'' THEN ''CAST('' ELSE '''' END + ''CAST(['' + COLUMN_NAME +
'']'' + CASE WHEN DATA_TYPE = ''image'' THEN '' AS VARBINARY(MAX))'' ELSE ''''
END + '' AS VARCHAR(MAX)) LIKE ''''%'' + @SearchText + ''%''''''
FROM ?.INFORMATION_SCHEMA.TABLES
JOIN ?.INFORMATION_SCHEMA.COLUMNS
ON TABLES.TABLE_CATALOG =
COLUMNS.TABLE_CATALOG
AND TABLES.TABLE_SCHEMA =
COLUMNS.TABLE_SCHEMA
AND TABLES.TABLE_NAME =
COLUMNS.TABLE_NAME
WHERE TABLES.TABLE_TYPE = ''BASE TABLE'' AND TABLES.TABLE_CATALOG NOT IN
(''master'',''model'',''msdb'',''tempdb'')
FOR XML PATH(''''))
, 1, 11, '''') + '')Src GROUP BY
TABLESCHEMA, TABLENAME, COLUMNNAME''
EXEC (@SQL)'
|
Output
Reference
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=190619
No comments:
Post a Comment