Retrieving Database Names
-
Hi
I want to retrieve all the user created database names from the server means otherthan system databases. is it possbile.
pls. give me the query.
Thanks
thiru.
-
Search on sp_MSforeachdb for examples of iterating DB names.
-
SELECT name FROM master.dbo.sysdatabases WHERE dbid>6 AND Name<>'distribution'
1 - 4 are the true system database
5 - 6 are pubs and northwind
distribution depends on when replication was set up on your box.
-
I prefer the sp_msforeachdb. This way you don't have to hard-code DBIDs (IF you didn't install pubs/northwind way lose some created DBs)
Good Hunting!AJ Ahrens
webmaster@kritter.net -
sp_msforeachdb is an undocumented stored procedure.
By all means use it but there are no guarantees it will persist into future versions of SQL.
DBID 1 - 4 are explicitly system tables so hard coding these is not an issue.
You could always include Name NOT IN ('pubs','northwind','distribution') in the WHERE condition to cater for other eventualities.
-
Do:
SELECT
*
FROM
INFORMATION_SCHEMA.SCHEMATA
WHERE
CATALOG_NAME
NOT IN
('master', 'model', 'tempdb', 'msdb', 'pubs', 'northwind')
sp_MSforeachDB is undocumented, and directly querying the system tables isn't the best solution either.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Frank,
I vaguely remembered that information_schema had the database catalog names - I just could not remember which one.
Thanks
Quand on parle du loup, on en voit la queue
-
Glad I could remind you, Patrick.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply