SQL Lion

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 ;

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' ;

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

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

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