How to run a query in all the database in the server
Posted on 2/2/2008 8:35:39 PM
in #SQL SERVER
Hi,
What do you do when you have to run some query in all the database in the server. Like To know all the relationship in all the database or to know the name of the table, procedure in the database in the server. The requirement can also be to know all the running process in all the database in the server etc.
In know all of us can write a small little curser after querying the sysdatabase for the all the database. But SQL server comes with a same purpose that is made for this purpose.
We an use the Stored procedure sp_Msforeachdb which takes a string (the query that should be run against all the database in the server.) Here is a way to use the stored procedure
EXEC sp_MSforeachdb 'USE ? SELECT name FROM sysobjects'
The question Mark (?) placeholder is used in place of the name of the database.
Vikram
|