Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Find data in databases

Author  Topic 

rama108
Posting Yak Master

115 Posts

Posted - 2014-01-07 : 09:34:53
Hello,
I need a script that can use sp_msforeachdb to loop thru each databases and find a particular data like '%Richardson%'.

Thank you.

waterduck
Aged Yak Warrior

982 Posts

Posted - 2014-01-07 : 22:12:38
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)'


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-08 : 06:56:48
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.


you mean serach within all columns inside db tables for this value?

http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -