SQL SERVER 2000 Vs 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.

ID	Information	SQL Server 2000	SQL Server 2005
1	Database 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
2	Database 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
3	Database files system table\view - Source for all database's file related information	SELECT *  FROM master.dbo.sysaltfiles GO	SELECT *  FROM sys.master_files; GO
4	IO 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
5	Database meta data - Returns the pertinent database name, size and remarks	EXEC master.dbo.sp_databases GO	EXEC master.dbo.sp_databases; GO
6	Database 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
7	Change database ownership - System stored procedure to change the database owner	EXEC sp_changedbowner sa GO	EXEC sp_changedbowner sa;  GO
8	Database 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
9	Database 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
10	Database status - System function that will return the value for 1 of ~25 database specific values	SELECT DATABASEPROPERTYEX('master', 'Status') GO	SELECT DATABASEPROPERTYEX('master', 'Status'); GO