In my Are you ready for SQL Server 2012 or are you still partying like it is 1999? post, I wrote about how you should start using SQL Server 2005 and SQL Server 2008 functionality now in order to prepare for SQL Server 2012. I still see tons of code that is written in the pre 2005 style and people still keep using those functions, procs and statements even though SQL Server 2005 and 2008 have much better functionality.

With the introduction of SQL Server 2005 system tables have been deprecated, there are three new set of objects to replace system tables: Compatibility Views, Dynamic Management Views and Catalog Views.

Compatibility views replace the system tables, so while you still can use syscolumns, you should get into the habit of using sys.columns instead

Every database

The following table maps the system tables or functions that live in every database in SQL Server 2000 to their equivalent system views or functions in SQL Server 2008.

System table or function System view or function Type of view or function
fn_virtualfilestats sys.dm_io_virtual_file_stats Dynamic management view
syscolumns sys.columns Catalog view
syscomments sys.sql_modules Catalog view
sysconstraints sys.check_constraints Catalog view
sys.default_constraints Catalog view
sys.key_constraints Catalog view
sys.foreign_keys Catalog view
sysdepends sys.sql_expression_dependencies Catalog view
sysfilegroups sys.filegroups Catalog view
sysfiles sys.database_files Catalog view
sysforeignkeys sys.foreign_key_columns Catalog view
sysindexes sys.indexes Catalog view
sys.partitions Catalog view
sys.allocation_units Catalog view
sys.dm_db_partition_stats Dynamic management view
sysindexkeys sys.index_columns Catalog view
sysmembers sys.database_role_members Catalog view
sysobjects sys.objects Catalog view
syspermissions sys.database_permissions Catalog view
sys.server_permissions Catalog view
sysprotects sys.database_permissions Catalog view
sys.server_permissions Catalog view
sysreferences sys.foreign_keys Catalog view
systypes sys.types Catalog view
sysusers sys.database_principals Catalog view
sysfulltextcatalogs sys.fulltext_catalogs Catalog view

Master database

The following table maps the system tables inside the master database in SQL Server 2000 to their equivalent system views or functions in SQL Server 2008.

System table System views or functions Type of view or function
sysaltfiles sys.master_files Catalog view
syscacheobjects sys.dm_exec_cached_plans Dynamic management view
sys.dm_exec_plan_attributes () Dynamic management view
sys.dm_exec_sql_text Dynamic management view
sys.dm_exec_cached_plan_dependent_objects Dynamic management view
syscharsets sys.syscharsets Compatibility view
sysconfigures sys.configurations Catalog view
syscurconfigs sys.configurations Catalog view
sysdatabases sys.databases Catalog view
sysdevices sys.backup_devices Catalog view
syslanguages sys.syslanguages Compatibility view
syslockinfo sys.dm_tran_locks Dynamic management view
syslocks sys.dm_tran_locks Dynamic management view
syslogins sys.server_principals Catalog view
sys.sql_logins
sysmessages sys.messages Catalog view
sysoledbusers sys.linked_logins Catalog view
sysopentapes sys.dm_io_backup_tapes Dynamic management view
sysperfinfo sys.dm_os_performance_counters Dynamic management view
sysprocesses sys.dm_exec_connections Dynamic management view
sys.dm_exec_sessions Dynamic management view
sys.dm_exec_requests Dynamic management view
sysremotelogins sys.remote_logins Catalog view
sysservers sys.servers Catalog view

As you can see in the tables above, the sysobjects system table maps to sys.objects. In SQL Server 2005 we actually have a couple of views that take parts of sysobjects, below are these views

sys.tables

sys.procedures

sys.views

sys.triggers

As you can see, you now have a view for just tables, triggers, procedures and views.

Some examples

Although Dynamic Management Views are listed in the tables above, I will not give examples in this post, I will have another post in this series focusing just on Dynamic Management Views.

If you wanted a list of all stored procedures in SQL Server 2000, you would typically do something like the following

SELECT name 
FROM sysobjects
WHERE type = 'P'

In SQL Server 2005 and up, you now have a view just for procedures

SELECT name 
FROM sys.procedures

If you run those two queries you might get a different number of rows back, this is because the sys.procedures view has these 4 kinds of procedures

  • P = SQL Stored procedure
  • X = Extended stored procedure
  • RF = Replication-filter-procedure
  • PC = Assembly (CLR) stored-procedure

In order to make them the same you would do

SELECT name FROM sysobjects
WHERE type IN ( 'P','PC','RF','X')

That is the same as

SELECT name 
FROM sys.procedures

A quick count of all the procs grouped by type

SELECT COUNT(*), type_desc
FROM sys.procedures
GROUP BY type_desc

A nice thing about these new views is that they have creation as well as modified dates, you can quickly see if a proc was ever modified or not

SELECT name,
	CASE WHEN create_date = modify_date 
			THEN 'Never Modified' 
		ELSE 'Modified' END AS modified
 FROM sys.procedures

Or if you just want a list of procedures that have never been modified (by way of an alter statement, not a drop and a create of course)

SELECT name
 FROM sys.procedures
WHERE create_date = modify_date
  • * *Let’s look at some other examples. The sys.messages view in the master database is a replacement for the sysmessages system table. If you run these 2 queries which bring back the same error message you will notice something interesting.
SELECT * 
FROM master..sysmessages
WHERE error= 1481
AND msglangid = 1033


SELECT * 
FROM master.sys.messages
WHERE  message_id =1481
AND language_id = 1033

output

———————

_Database mirroring could not repair physical page %S_PGID in database “%.*ls”. The mirroring partner could not be contacted or did not provide a copy of the page. Possible reasons include a lack of network connectivity or that the copy of the page kept by

Database mirroring could not repair physical page %S_PGID in database “%.*ls”. The mirroring partner could not be contacted or did not provide a copy of the page. Possible reasons include a lack of network connectivity or that the copy of the page kept by the partner is also corrupted. To learn whether the partners are currently connected, view the mirroring_state_desc column of the sys.database_mirroring catalog view. If they are connected, for information about why the partner could not provide a copy of the page, examine its error log entries from around the time when this message was reported. Try to resolve the error and resume mirroring.

As you can see the text is truncated to 255 characters in the old system views, in the new view you see the whole message.

I only showed you examples for two of these tables, if I did all of them this post would take forever to write. You can investigate the other tables by yourself, maybe pick one or two a day and mess around with some queries.

I would say, take a look at these views, start using them, learn them well because they will help you do your job better.