Understanding basic tools of SQL Server 2008/2005

One of my friends has just updated his live database from SQL Server 2000 to SQL Server 2008. You may know that we had direct access of system table in SQL Server 2000 which is not directly possible in SQL Server 2008/2005. He has developed many scripts in last 8 years which was directly accessing many of the system tables to generate certain kind of report, out of such scripts, few was not working. 

In recent SQL Server (2005/2008) version, we do have those “System Base tables” but it is not directly visible as well as accessible unless you are making dedicated administrator connection (DAC).  You have to access those information stored in system base table via “Compatibility View” or “Catalog View”.

“Compatibility View” is there to support your old scripts (though few very specific won’t work) which was using direct system tables in SQL Server 2000. For example we had sysobjects, sysindexes, sysusers etc. tables in older version but now we have views with the same name so your scripts of older version would works fine. Though it is “Compatibility View” intend to provide backward compatibility only, it doesn’t consist many columns which provides information about specific features of newer SQL Server version so it is recommended to use “Catalog View”.

Right from SQL Server 2005+ we are having “Catalog View” which was reading may useful information from system base tables and provide us. “Catalog View” along with “Dynamic Management View (DMV)” is under sys schema. I would prefer to go with “Catalog View” rather than “Compatibility View”. Because “Catalog View” is much richer than “Compatibility View”, you can run following query and see result by your own.

--Compatability View

select * from sysdatabases

--Catalog View

select * from sys.databases

Apart from these Views, there is one more powerful tool to read metadata of SQL Server 2008/2005 and internal information about SQL Server is Dynamic Management View (DMV) and Dynamic Management Functions (DMF). DMV and DMF generally starts with “dm_” and it resides in “sys” database so it would always starts with prefixes “sys.dm_” for example:

--return information about authenticated session in SQL Server

select * from sys.dm_exec_sessions

--returns info about currently locked object.

select * from sys.dm_tran_locks

--this is DMF and retun I/O stat for MDF and LDF file.

select * from sys.dm_io_virtual_file_stats(DB_ID(N'master'), 2);

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com