| Author |
Topic |
|
bntringa
Starting Member
5 Posts |
 Posted - 2006-05-08 : 12:20:21
|
| Hey all,First time post.I have the following query that I used in SQL 2000 to retrieve a list of databases from the sysdatabases table in the Master db.SELECT name FROM sysdatabases ORDER BY nameIt appears that the sysdatabases table no longer exists in 2005 or at least exists but only in a view?Can anyone help with updated this query to retrieve a list of db names from 2005?thank you!- Brian |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-05-08 : 12:28:40
|
| should still work but is in master.SELECT name FROM master..sysdatabases ORDER BY namefor 2005 it should beSELECT name FROM master.sys.databases ORDER BY namebut the old views still work.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2006-05-09 : 08:24:38
|
| You could also use the ANSI standard:select * from information_schema.schemata |
|
|
|
bntringa
Starting Member
5 Posts |
Posted - 2006-05-09 : 09:17:07
|
| Thanks for the replies!Rob - What is the difference in using the ANSI method you mention? Is that better/worse - what are the pros/cons vs querying the master db?thanks again!- Brian |
|
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2006-05-09 : 13:04:10
|
| Microsoft is trying to move away from users having any access to the underlying system objects, and instead are granting authority to the INFORMATION_SCHEMA vies. This enables them to have the system objects as "black boxes" that they can maintain/alter from release to release. The upgrade from 7.0 to 2000 involved a system object change that caused a lot of grief for people because of functionality that was built around specific column names that they had the nerve to change. In fact I'm currently reading "Microsoft SQL Server 2005 New Features" which was written using the Beta 2 version and the book contains the following "SQL Server 2005 no longer allows any direct access to system tables." I immediately went and tested theory, and obviously there was backlash in the community and they "relaxed" that policy. But I'm taking that a serious warning to stop using the system tables and get on board with the information schema views, that I've avoided doing with SQL Server 2000 just to be obstonate.Hope it helps,DaltonBlessings aren't so much a matter of "if they come" but "are you noticing them." |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2006-05-09 : 13:27:01
|
quote:
Originally posted by robvolk
You could also use the ANSI standard:select * from information_schema.schemata
This has changed in sql2005 since the introduction of schema concept in sql2005.rockmoose |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2006-05-09 : 20:08:06
|
Thanks rockmoose, I didn't know that. Oh well, maybe you SHOULDN'T use the ANSI INFORMATION_SCHEMA views.  |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2006-05-10 : 03:25:04
|
| I ran the upgrade advisor on one of our servers.I got a warning for the schemata view.The warning was kind of MS to provide, otherwise the code would certainly be broken if we used multiple schemas.Q is, was the view ANSI before or now, or never or both?The only constant is change.rockmoose |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-05-10 : 06:08:04
|
| So what's the consensus on the best way to do this in 2005 pls?Kristen |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2006-05-10 : 06:46:34
|
| I think the only recurse is to use:sys.databasesI whish they would have include a INFORMATION_SCHEMA.CATALOGS view.rockmoose |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-05-10 : 06:57:55
|
So ... we're supposed not to use the System Tables, but some data is only available in them.Not much opportunity to re-factor code before the next release when they will really be withdrawn?  Perhaps I should write my own VIEWs which can be modified in the future to be immune to changes.Kristen |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2006-05-10 : 07:15:01
|
| SELECT name FROM sys.databases ORDER BY namewould surely provide the same as the OP was asking for? Generally I think a lot of information is exposed through the various views in 2005, it's just a matter of adjusting to the correct ones.-------Moo. :) |
|
|
|
|
Query sysdatabases table in 2005?