System Information in SQL Server 2000 vs SQL Server 2005

Problem

Accessing SQL Server system information is necessary for administrative scripts and very important when troubleshooting particular issues.  Unfortunately, in the transition from SQL Server 2000 to 2005, some of the objects that we have grown to rely on are no longer the recommended information source.  In this tip we will outline core sets of data that need to be retrieved for databases and map the objects from SQL Server 2000 to 2005.

Solution

The database information mapping between SQL Server 2000 and 2005 is critical to ensure scripts are working properly when upgrading to SQL Server 2005.  Below outlines the common database related objects.

IDInformationSQL Server 2000SQL Server 2005
1Database system table\view – Source for all databases on the SQL Server to include the name, owner, creation date, etc.
 		SELECT *
FROM master.dbo.sysdatabases
GO		SELECT *
FROM sys.databases;
GO
 
2Database files system table\view – Source for the currently connected database’s file names, size, location, type (database or log), etc.
 		SELECT *
FROM dbo.sysfiles
GO		SELECT *
FROM sys.database_files;
GO
3Database files system table\view – Source for all database’s file related information
 		SELECT *
FROM master.dbo.sysaltfiles
GO
 		SELECT *
FROM sys.master_files;
GO
4IO statistics on database files – Returns the usage statistics on a per file basis— Single database file
SELECT *
FROM :: fn_virtualfilestats(1, 1)
GO		— All database files
SELECT *
FROM sys.dm_io_virtual_file_stats(NULL, NULL);
GO
 
5Database meta data – Returns the pertinent database name, size and remarks
 		EXEC master.dbo.sp_databases
GO		EXEC master.dbo.sp_databases;
GO
6Database meta data – Fairly complete set of pertinent database information that can return data for all databases or 1 database
 		— All databases
EXEC master.dbo.sp_helpdb
GO

— Single database
EXEC master.dbo.sp_helpdb ‘Northwind’
GO
 

 

— All databases
EXEC master.dbo.sp_helpdb;
GO

— Single database
EXEC master.dbo.sp_helpdb ‘AdventureWorks’;
GO

 
7Change database ownership – System stored procedure to change the database owner
 		EXEC sp_changedbowner sa
GO
 		EXEC sp_changedbowner sa;
GO
8Database ID to name translation – System function that will provide the database name when passed the database ID from the database system table
 		— Returns the master database
SELECT DB_NAME(1)
GO		— Returns the master database
SELECT DB_NAME(1);
GO
9Database name to ID translation – System function that will provide the database ID when passed the database name from the database system table
 		— Returns 1
SELECT DB_ID(‘master’)
GO		— Returns 1
SELECT DB_ID(‘master’);
GO
10Database status – System function that will return the value for 1 of ~25 database specific valuesSELECT DATABASEPROPERTYEX(‘master’, ‘Status’)
GO
 		SELECT DATABASEPROPERTYEX(‘master’, ‘Status’);
GO

Next Steps

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *