In SQL Server, how can I execute a piece of tsql against all databases in an instance?

开发者_JAVA技巧

In SQL Server, how can I execute a piece of tsql against all databases in an instance?

Great thanks.

---

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'

---

For SQl Server 2005, the excellent SSMS Tools pack contains this functionality.

[This functionality is available natively in SQL Server 2008].