CODEWITHSUNDEEP
sql-server-2005t-sql
Just a learner
Just a learner

Reputation: 28572

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

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

Great thanks.

Upvotes: 2

Views: 243

Answers (2)

Mitch Wheat
Mitch Wheat

Reputation: 300539

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

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

Upvotes: 2

cjk
cjk

Reputation: 46415

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'

Upvotes: 4

Related Questions