SQL Help - How do I get a list of all the DB names in the server and a list of SCHEMAS that belong to each DB

Question

SQL Help - How do I get a list of all the DB names in the server and a list of SCHEMAS that belong to each DB

mw_sql_developer

SSCoach

Points: 19445
More actions
April 5, 2017 at 2:01 pm

#323599

Team:
The following SQL gives a list of DB names across the SERVER that I have access to . Works well.. This is good.!
Any idea how I can get to know each schema available in each DB ?
So I am expecting something like "Select distinct db_name, schema_name from ...."
SELECT name
FROM Sys.databases
WHERE name not in ( 'ReortServer', 'msdb', 'tempdb' )
AND HAS_DBACCESS(name) = 1;

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 1

easiest way is to use the SP_MSFOREACHDB extended stored proc:
CREATE TABLE #SchemaList (db_id int, db_name sysname, schema_name sysname); EXECUTE sp_msforeachdb 'USE ? INSERT INTO #SchemaList SELECT DB_ID(), DB_NAME(), name FROM sys.schemas'; SELECT db_name, schema_name FROM #SchemaList WHERE db_id > 4 ORDER BY db_name, schema_name; DROP TABLE #SchemaList;

mw_sql_developer SSCoach Points: 19445 More actions · Answer 2

mw_sql_developer

SSCoach

Points: 19445

April 6, 2017 at 7:16 am

#1937027

Slight error popped up... Can you fix...Thanks

Sue_H SSC Guru Points: 90860 More actions · Answer 3

You probably have a database with a name like xxx 2016 - with the space in there.
If that's the case, just change the use syntax to 'USE [?] INSERT ....so that the database name is in brackets.
And don't forget to thank Chris for writing that up for you.

Sue

mw_sql_developer SSCoach Points: 19445 More actions · Answer 4

Sue_H - Thursday, April 6, 2017 7:24 AM
You probably have a database with a name like xxx 2016 - with the space in there.
If that's the case, just change the use syntax to 'USE [?] INSERT ....so that the database name is in brackets.
And don't forget to thank Chris for writing that up for you.
Sue

That worked, Thanks Chris and Sue. Good Job....

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 5

Chris Harshman

SSC-Forever

Points: 42192

April 7, 2017 at 8:54 am

#1937221

d'oh sorry I forgot the square brackets!

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

I really hate cursors and sp_msforeachdb is a big nasty undocumented cursor that sometimes skips databases. http://sqlblog.com/blogs/aaron_bertrand/archive/2010/12/29/a-more-reliable-and-more-flexible-sp-msforeachdb.aspx

Thankfully we can easily use the system views to generate some dynamic sql for us. It eliminates that cursor and we don't have to use somewhat buggy undocumented procedures.

declare @sql nvarchar(max) = ''

select @sql = @sql + 'SELECT ' + convert(varchar(10), database_id) + ' as DatabaseID, ''' + name + ''' as DatabaseName, name collate SQL_Latin1_General_CP1_CI_AS as SchemaName FROM ' + quotename(name) + '.sys.schemas union all ' from Sys.databases WHERE name not in ( 'ReportServer', 'msdb', 'tempdb' )

select @sql = left(@SQL, len(@SQL) - 10)

--select @sql exec sp_executesql @sql

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 7

Sean Lange - Friday, April 7, 2017 9:39 AM
I really hate cursors and sp_msforeachdb is a big nasty undocumented cursor that sometimes skips databases. http://sqlblog.com/blogs/aaron_bertrand/archive/2010/12/29/a-more-reliable-and-more-flexible-sp-msforeachdb.aspx
Thankfully we can easily use the system views to generate some dynamic sql for us. It eliminates that cursor and we don't have to use somewhat buggy undocumented procedures.

good to know, thanks

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

Sean Lange - Friday, April 7, 2017 9:39 AM
I really hate cursors and sp_msforeachdb is a big nasty undocumented cursor that sometimes skips databases. http://sqlblog.com/blogs/aaron_bertrand/archive/2010/12/29/a-more-reliable-and-more-flexible-sp-msforeachdb.aspx
Thankfully we can easily use the system views to generate some dynamic sql for us. It eliminates that cursor and we don't have to use somewhat buggy undocumented procedures.
declare @sql nvarchar(max) = ''
select @sql = @sql + 'SELECT ' + convert(varchar(10), database_id) + ' as DatabaseID, ''' + name + ''' as DatabaseName, name collate SQL_Latin1_General_CP1_CI_AS as SchemaName FROM ' + quotename(name) + '.sys.schemas union all ' from Sys.databases WHERE name not in ( 'ReportServer', 'msdb', 'tempdb' )
select @sql = left(@SQL, len(@SQL) - 10)
--select @sql exec sp_executesql @sql

I tend to agree on the cursor front and absolutely agree on the msforeachdb front. It is terribly buggy. That said, foreachdb is really just a poorly written cursor.

I don't have an issue with cursors when used properly and infrequently. For a sysadmin function such as this, I have no qualms with the cursor and actually prefer it over the dynamic sql. Though, the dynamic sql is safe in this context and generally harmless (again in this context).

I would actually recommend this as an alternative to foreachDB. It is solid and well done.

https://spaghettidba.com/2011/09/09/a-better-sp_msforeachdb/

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Sean Lange SSC Guru Points: 286590 More actions · Answer 9

SQLRNNR - Friday, April 7, 2017 10:32 AM
I would actually recommend this as an alternative to foreachDB. It is solid and well done.
https://spaghettidba.com/2011/09/09/a-better-sp_msforeachdb/

Agreed. This is an excellent alternative and gets rid of the dreaded cursor. I should have linked this one instead (or in addition) to Aaron's article.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 10

I'm a little bit old fashioned... and a bit of a control freak when it comes to such things, not to mention being a bit anal about formatting and readability of even dynamic SQL. 😉 With that, here's the hat that I'll throw into the ring (and I do wish they'd fix the bloody code windows on this forum. It used to be pretty good but not any more).
DECLARE @sql VARCHAR(MAX) ; SELECT @sql = ISNULL(@SQL + ' UNION ALL','') + REPLACE(REPLACE(REPLACE(' SELECT DBName = "[<<DBName>>]" ,DBID = <<DBID>> ,SchemaName = QUOTENAME(s.name COLLATE database_default) ,SchemaID = s.schema_id ,SchemaOwnerName= p.name COLLATE database_default ,SchemaOwnerID = s.principal_id FROM [<<DBName>>].sys.schemas s JOIN [<<DBName>>].sys.database_principals p ON s.principal_id = p.principal_id' ,'"','''') --The other end of the REPLACEs starts here ,'<<DBName>>',db.name) ,'<<DBID>>',CONVERT(VARCHAR(10),database_id)) FROM sys.databases db WHERE db.name NOT IN ('ReportServer','ReportServerTempDB') AND db.database_id > 4 AND HAS_DBACCESS(name) = 1 ; SELECT @sql = ' USE master;'+@SQL; ; PRINT @SQL --Will be truncated at 8K or less ; EXEC (@SQL) ;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ed Wagner SSC Guru Points: 287026 More actions · Answer 11

SQLRNNR - Friday, April 7, 2017 10:32 AM
Sean Lange - Friday, April 7, 2017 9:39 AM
I really hate cursors and sp_msforeachdb is a big nasty undocumented cursor that sometimes skips databases. http://sqlblog.com/blogs/aaron_bertrand/archive/2010/12/29/a-more-reliable-and-more-flexible-sp-msforeachdb.aspx
Thankfully we can easily use the system views to generate some dynamic sql for us. It eliminates that cursor and we don't have to use somewhat buggy undocumented procedures.
declare @sql nvarchar(max) = ''
select @sql = @sql + 'SELECT ' + convert(varchar(10), database_id) + ' as DatabaseID, ''' + name + ''' as DatabaseName, name collate SQL_Latin1_General_CP1_CI_AS as SchemaName FROM ' + quotename(name) + '.sys.schemas union all ' from Sys.databases WHERE name not in ( 'ReportServer', 'msdb', 'tempdb' )
select @sql = left(@SQL, len(@SQL) - 10)
--select @sql exec sp_executesql @sql
I tend to agree on the cursor front and absolutely agree on the msforeachdb front. It is terribly buggy. That said, foreachdb is really just a poorly written cursor.
I don't have an issue with cursors when used properly and infrequently. For a sysadmin function such as this, I have no qualms with the cursor and actually prefer it over the dynamic sql. Though, the dynamic sql is safe in this context and generally harmless (again in this context).
I would actually recommend this as an alternative to foreachDB. It is solid and well done.
https://spaghettidba.com/2011/09/09/a-better-sp_msforeachdb/

Thanks for posting the link, Jason. I have something to look at tomorrow.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

SQLRNNR SSC Guru Points: 281334 More actions · Answer 12

Ed Wagner - Sunday, April 9, 2017 7:18 AM
Thanks for posting the link, Jason. I have something to look at tomorrow.

You're welcome

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events