SQL Databases in Server

[inutelinside]

Hello Y'all,

How do you query all the database names in a particular server? Thanks.

 

[Jamfool]

--sql 2000
SELECT name FROM master..sysdatabases ORDER BY name

--sql 2005
SELECT name FROM master.sys.databases ORDER BY name

 

[SQLDenis]

Or for both 2000 and 2005

SELECT CATALOG_NAME FROM INFORMATION_SCHEMA.SCHEMATA

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[Jamfool]

SQLDenis try running that one in sql2005

In earlier versions of SQL Server, the INFORMATION_SCHEMA.SCHEMATA view returned all databases in an instance of SQL Server. In SQL Server 2005, the view returns all schemas in a database. This behavior complies with the SQL Standard. For more information, see SCHEMATA (Transact-SQL).

http://msdn2.microsoft.com/en-us/library/ms143179.aspx

 

[SQLDenis]

Yeo you are right, I remeber MS saying don't use system tables use ANSI INFORMATION_SCHEMA views because they will never changes oh well......

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/