Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

System Information in SQL Server 2000 vs SQL Server 2005


By:   |   Read Comments (3)   |   Related Tips: 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
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

Next Steps



Last Update:






About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an Edgewood Solutions SQL Server Consultant, MSSQLTips.com co-founder and Baltimore SSUG co-leader.

View all my tips
Related Resources





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Monday, May 05, 2014 - 9:15:28 PM - JJ Back To Top

 

Great info Jeremy! 

 

But I'm stuck on this ... what is the equivalent of this in SQL Server 2000?

if (SELECT user_access_desc FROM sysdatabases WHERE name = 'MyDatabaseName')
                                                            = 'SINGLE_USER'


Sunday, September 08, 2013 - 4:30:37 PM - Jeremy Kadlec Back To Top

Vijay,

Check out these tips:

http://www.mssqltips.com/sqlservertip/1735/auditing-failed-logins-in-sql-server/

http://www.mssqltips.com/sqlservertip/1627/sql-server-2005-sysadmin-login-auditing/

http://www.mssqltips.com/sqlservertip/2741/how-to-audit-login-changes-on-a-sql-server/

http://www.mssqltips.com/sql-server-tip-category/19/security/

HTH.

Thank you,
Jeremy Kadlec
Community Co-Leader


Thursday, August 22, 2013 - 6:39:32 AM - VIJAY D. Back To Top

I am using SQL SERVER 2005 Std version and have created few USERS with SQL Server authentication and have provided access to some of the db in the database.  I need a query to get LogIn details of a specific SQL Server user in the database, like when the user had last logged in to SQL server.


Learn more about SQL Server tools