Warning: is_writable() [function.is-writable]: open_basedir restriction in effect. File(/f5/sqllion/public/wp-content/uploads/shareaholic/) is not within the allowed path(s): (/fs3d/sqllion/:/nfsn/apps/php53/lib/php/:/nfsn/apps/php5/lib/php/:/nfsn/apps/php/lib/php/) in /fs3d/sqllion/public/wp-content/plugins/sexybookmarks/sexy-bookmarks.php on line 335
For each database – sp_MSForEachDB | SQL Lion
Home > T - SQL, Tips N Tricks > For each database – sp_MSForEachDB

For each database – sp_MSForEachDB

Sp_MSForEachDB is a great procedure coming with MS SQL Server even though it is un-documented. It eases some of the complex operations that we mostly do by using CURSORS.

Here is a quick tip to get all the schemas available in a particular server. Very useful in case of auditing or administrating security roles on SQL Server.




First Step: To get schema information for a particular database:

Select the database, and then run the below query:

select * from sys.schemas ;

MSForEachDB1

Second Step: To get schema information for all the available databases in one shot:

For this we will use ‘sp_MSForEachDB’, undocumented procedure but very useful. This will iterate your query for each database available in your server.

EXEC sp_MSForEachDB 'Use ?; select * from sys.schemas' ;

MSForEachDB2

Third Step: To get all distinct schemas available in a server for all databases. The above query will give you multiple result set; i.e. one for each database. But if you want all the schema information to be displayed in one result and that of too to have uniqueness, then you have to prefer CURSOR for this.

create table #tempschema (name varchar(100))
declare @dbname varchar(100)
declare @sqlQuery nvarchar(4000)
 
declare dbcursor CURSOR for 
select name from sys.databases
 
OPEN dbcursor
FETCH NEXT FROM dbcursor
into @dbname
 
WHILE @@FETCH_STATUS = 0
BEGIN
	set @sqlQuery = ' Insert into #tempschema(name) select name from ['+ @dbname + '].[sys].[schemas];'
	exec sp_executesql @sqlQuery  
 
FETCH NEXT FROM dbcursor
into @dbname
END
 
select distinct name from #tempschema
drop table #tempschema
CLOSE dbcursor
Deallocate dbcursor

MSForEachDB3

Fourth Step:  One another way to achieve the same result in more sophisticated way by using undocumented procedure “sp_MSForEachDB” . I hope you would like it.

create table #tempschema 
(name varchar(100),
 schema_id int,
 principal_id int)
 
INSERT INTO #tempschema
EXEC sp_MSForEachDB 'select * from [?].sys.schemas'
 
select distinct * from #tempschema
drop table #tempschema

MSForEachDB4

Let me know your valuable suggestions on it. Check out more in my upcoming posts. Register or sign-up for the latest updates.




Categories: T - SQL, Tips N Tricks
  1. August 23rd, 2010 at 11:04 | #1

    nice storeced prco……

  2. Manoj
    September 13th, 2010 at 15:18 | #2

    Can we use it for any other purpose other than looping for each database and running a query…

  3. Ram Kumar
    October 7th, 2010 at 20:07 | #3

    no

  4. Jacob
    May 24th, 2011 at 23:17 | #4

    To be more accurate, you can also use it to run DBCC commands and such as well:

    EXEC sp_MSforeachdb ‘DBCC CHECKDB (?)’
    EXEC sp_MSforeachdb ‘DBCC UPDATEUSAGE (?)’

  5. September 28th, 2011 at 13:36 | #5

    in this link you can find an example of using the sp_MSforeachdb command, to generate script for restoring all the DBs in the server

  6. September 28th, 2011 at 13:37 | #6

    zohar :in this link you can find an example of using the sp_MSforeachdb command, to generate script for restoring all the DBs in the server

    https://sites.google.com/site/sqlserversamplesandsolutions/home/system-functions-procedures/sp_msforeachdb/samples-and-solutions/restore-all-dbs

  7. Sreedhar
    November 9th, 2012 at 00:43 | #7

    Nice one!

  1. No trackbacks yet.
You must be logged in to post a comment.