INFORMATION_SCHEMA.SCHEMATA does not return all rows on SQL 2005

7 Jan 2006 2:55 AM

Pradeep

Hi,

In SQL 2000, the following query used to return all the database names:

SELECT CATALOG_NAME FROM INFORMATION_SCHEMA.SCHEMATA

However, in SQL 2005, it just returns "master" as the database (that
too a number of times).

Can someone please confirm if this is a bug in SQL 2005?

Although I could use sp_catalogs_rowset;2, I prefer using ANSI SQL
standard statements.

Thank you in advance for your help.

Pradeep

7 Jan 2006 3:52 AM

Aaron Bertrand [SQL Server MVP]

> In SQL 2000, the following query used to return all the database names:

Which is incorrect behavior. This is fixed in SQL Server 2005 (this should
never have been a list of databases).

To get a list of database names,

SELECT name FROM sys.databases

7 Jan 2006 5:14 AM

Dan Guzman

To add on to Aaron's response, the reason for the SQL 2005 change was to
make the INFORMATION_SCHEMA.SCHEMATA view consistent with the ANSI standard.
The SQL 2000 behavior (database list) was proprietary. This is listed in
the SQL 2005 Books Online under the breaking changes topic
<ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/47edefbd-a09b-4087-937a-453cd5c6e061.htm>.

--
Hope this helps.

Dan Guzman
SQL Server MVP

Show quote

"Pradeep" <prad***@tapadiya.net> wrote in message
news:1136602519.887257.199350@g43g2000cwa.googlegroups.com...
> Hi,
>
> In SQL 2000, the following query used to return all the database names:
>
> SELECT CATALOG_NAME FROM INFORMATION_SCHEMA.SCHEMATA
>
> However, in SQL 2005, it just returns "master" as the database (that
> too a number of times).
>
> Can someone please confirm if this is a bug in SQL 2005?
>
> Although I could use sp_catalogs_rowset;2, I prefer using ANSI SQL
> standard statements.
>
> Thank you in advance for your help.
>
> Pradeep
>