By: Jeremy Kadlec | Updated: 2006-08-10 | Comments (3) | Related: More > Database Administration
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 |
| 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 |
Next Steps
- As you begin to learn SQL Server 2005, make the switch from using the old objects to the new objects to ensure you are not spending time with depreciated code sets.
- If some of these commands are new to you, start to test them out and keep them in the back of your mind as future needs arise.
- Check out these related MSSQLTips:
- Stay tuned for future tips mapping the system objects between SQL Server 2000 and 2005.
About the author
Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2006-08-10
