Yesterday we talked about the sysusers system table, and today we are going to talk about the sysdatabases system table. This table is scoped at the instance level and in case you need a reminder this system table will be removed from a future version of SQL Server.
What is this table for?
This table will contain one row for every database that has been created on your instance. Its that simple.
Why should you care?
Unlike the first two posts of this series which focused more on a recovery standpoint, you don’t care about sysdatabases as much for recovery. That’s not saying that you would not *ever* have such a need, just that most of the time I used this table in a more proactive manner.
Go ahead and run the following query:
SELECT * FROM master.dbo.sysdatabases
And pay attention to the columns named ‘status’, ‘status2’, ‘category’, and ‘cmptlevel’. I would use this columns when building reports looking for outliers from our standard configurations. For example, every now and then I would want to verify that I had all of my SQL 2005 databases set to a compatibility level of ’90’, or SQL 2008 to ‘100’, and I knew I could get this information from this table.
This table was also very useful when I was asked to produce a report that did a simple count of the total number of databases in my shop. I could include the server name and then also further break down the number of production, test, and development databases, or by version, or…well, you get the idea. And it was quite handy when we were underway with a virtualization effort and I wanted to track down and eliminate databases that were no longer being used.
I would also use this table whenever I needed to perform actions on all databases, such as looping through each database in order to get details on all users, or joining to this table in order to make certain I am checking for details on databases that are only currently online.
Where else is this information?
Remember how I said this system table will be removed from a future version of SQL Server? Good, then start using the sys.databases catalog view from this point forward. In fact, go and run this right now:
SELECT * FROM sys.databases
And pay attention to the different result set returned from master.dbo.sysdatabases.
Pedantic, I know, but none of these objects are tables. They haven’t been since SQL 2000.
Well, for about 40% of the people using SQL Server, these are system tables right now. I don’t see an issue using that term, especially if I point them to the master.dbo schema. And I am doing my best to make certain they understand that there are new catalog views in place.
That being said, you are spot on…and pedantic…all at the same time.
Thanks for reading, and for the comment Gail!
I started reading this and had the same pedantic thought that Gail posted. Nice Explanation Tom