Hello Everyone !!!
There are times when you need to run a SQL command against each database on one of my SQL Server instances. There is a handy stored procedure that allows you to do this without needing to set up a cursor against your sysdatabases table in the master database: sp_MSforeachdb.
Syntax:
EXEC sp_MSforeachdb 'Your Command Goes Here'
EXEC sp_msforeachdb 'USE [?]; INSERT INTO #ListOfSPs Select ''?'', Object_Id, Name FROM sys.procedures'
There are times when you need to run a SQL command against each database on one of my SQL Server instances. There is a handy stored procedure that allows you to do this without needing to set up a cursor against your sysdatabases table in the master database: sp_MSforeachdb.
Syntax:
EXEC sp_MSforeachdb 'Your Command Goes Here'
EXEC sp_msforeachdb 'USE [?]; INSERT INTO #ListOfSPs Select ''?'', Object_Id, Name FROM sys.procedures'
"?" Placeholder:
You'll see the use of the question mark as a placeholder for the database/database name. To reference the database name as a string to be returned in a query, embed it between a double set of single quotation marks. To treat it as a reference to the database object simply use it by itself. It is necessary to set the database for the query to run against, by using the USE ? statement, otherwise the code will execute in the context of the current database, for each database in your SQL instance. If you have 5 databases hosted in the current instance and you were to run the stored procedure code above while in the context of DBx it would execute the T-SQL text of the command 5 times in DB.
sp_MSforeachdb is extremely useful for pulling together metadata about your various SQL databases. We should use it quite frequently for reporting on such important metrics as database file sizes, amount of free space, and backup status.
Happy Coding !!!
You'll see the use of the question mark as a placeholder for the database/database name. To reference the database name as a string to be returned in a query, embed it between a double set of single quotation marks. To treat it as a reference to the database object simply use it by itself. It is necessary to set the database for the query to run against, by using the USE ? statement, otherwise the code will execute in the context of the current database, for each database in your SQL instance. If you have 5 databases hosted in the current instance and you were to run the stored procedure code above while in the context of DBx it would execute the T-SQL text of the command 5 times in DB.
sp_MSforeachdb is extremely useful for pulling together metadata about your various SQL databases. We should use it quite frequently for reporting on such important metrics as database file sizes, amount of free space, and backup status.
Happy Coding !!!
No comments:
Post a Comment