quote:

---

Originally posted by waterduck

sp_msforeachdb '
DECLARE @SearchText VARCHAR(MAX) = ''Richardson''

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)'

---

quote:

---

Originally posted by rama108

Hello,
I need a script that can use sp_msforeachdb to loop thru each databases and find a particular data like '%Richardson%'.

Thank you.

---