Hi guys,
In SQL Server, how can I execute a piece of tsql against all databases in an instance?
Great thanks.
views:
21answers:
2
+3
A:
There is an undocumented stored procedure sp_MSForEachDB which if you call passing in a string as a parameter, it will execute that string, substituting a ? for the database name.
E.g.:
exec sp_MSForEachDB 'use ?; select * from INFORMATION_SCHEMA.TABLES'
ck
2010-05-25 07:15:05
+1 - this is why I spend time on SO, to find out little snippets like this!
David M
2010-05-25 07:16:41
+1, this works in 2000, 2005 and 2008. Also note the `sp_MSForEachtable`
Dustin Laine
2010-05-25 07:19:18
+2
A:
For SQl Server 2005, the excellent SSMS Tools pack contains this functionality.
[This functionality is available natively in SQL Server 2008].
Mitch Wheat
2010-05-25 07:16:29