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