XML FeedsIn 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.
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.