Given an instance of SQL server, what's the best way to enumerate the databases?
Add a comment
|
3 Answers
In SQL Server 2000+:
select * from sysdatabases
In SQL Server 2005+:
select * from sys.databases
The difference is subtle and barely worth mentioning for a one-liner like this. But depending on how much you're going to be accessing the system catalog, you may get some use out of this article:
Querying the SQL Server System Catalog
You can also execute sp_helpdb without an argument to get basic information about all databases. (Pass in a database name as an argument to get more detailed information about that database).
2 Comments
Remus Rusanu
Note that there is no
sys schema in SQL 2000, is just select * from sysdatabses
ajk
@Remus Thanks, guess it's been too long since I needed 2000-compatible syntax!
Try this:
SELECT [name] FROM sys.sysdatabases
You could also get this from exec sp_databases;
These commands will work from SQL 2000+.
Comments
EXECUTE master.sys.sp_MSforeachdb 'USE [?]; EXEC sp_spaceused'
Comments
