>>>why would this not give me a list of all db's on a MS SQL Server 2008 server?
>>>
>>>select CATALOG_NAME from INFORMATION_SCHEMA.SCHEMATA where SCHEMA_NAME='dbo'
>>
>>In SQL 2000 the INFORMATION_SCHEMA.SCHEMATA wasn't complaint with SQL Standard. It has been fixed starting with SQL 2005.
>>In SQL 2005 and later you can use
>>SELECT name FROM sys.databases
>>
>
>I'm using MS Sql Server 2008 which should work right? But it doesn't. It only returns a single record for master even though there are many databases on the server.
I tested your original query in SQL Server 2008 and it only returned one record.
>
>I switched to use the select name from sys.databases and it works... I think I was trying to use a cross-DB statement here as this doesn't work in MySQL. Guess I'll just have 2 different statements depending on backend.
>
>
>Thanks.
I was thinking you can also use undocumented SP sp_MSForEachDb to print all the names of databases, but the above query is much simpler.
sp_msForEachDb 'print ''?'''
If it's not broken, fix it until it is.
My Blog