DBA and SysAdmin World

December 4, 2008

Mapping System Objects From SQL 2000 to Next SQL Server Version

Filed under: SQL Server — Tags: — solihinho @ 11:05 PM
next generation

As a SQL Server DBA, knowledge of system objects is necessity. On SQL Server 2000 we may know system objects such as dbo.sysdatabases on master database, dbo.sysobjects on each databases etc. I use these objects repeatedly when creating a script for monitoring and also for maintenance databases. Unfortunately when microsoft launch SQL Server 2005 or 2008, some system objects are changed.
Do I need to change all my scripts that using those system objects? For a while the answer is no, For backward compatibility, SQL 2005/2008 provide system view which is have a name same with system object name on SQL Server 2000. Microsoft does not recommend to use this system view because this feature will be removed in a future version of SQL Server. But for a while some of my scripts can run both SQL 2000 or SQL next version.

WhistlingTo test this just type sp_helptext sysdatabases at master database on SQL 2005. You will find this  view query data from sys.databases table. But when I am trying query from master.sys.objects there is no object sysdatabases  I dont know

Mapping System Objects

Category SQL 2000 SQL 2005/08 description
Databases and files sysdatabases sys.databases Returns one row for each database in the server
sysfiles sys.database_files Return one row for each file of the database
sysaltfiles sys.master_files Contains a row per file of a database as stored in the master database
sysfilegroups sys.filegroups Contains a row for each data space that is a filegroup
sysdevices sys.backup_devices Contains a row for each backup-device registered by using sp_addumpdevice or created in SQL Server Management Studio
Object sysobjects sys.objects Contains a row for each user-defined, schema-scoped object that is created within a database
syscolumns sys.columns Returns a row for each column of an object that has columns, such as views or tables. The following is a list of object types that have columns:
* Table-valued assembly functions (FT)
* Inline table-valued SQL functions (IF)
* Internal tables (IT)
* System tables (S)
* Table-valued SQL functions (TF)
* User tables (U)
* Views (V)
syscomments sys.sql_modules Returns a row for each object that is an SQL language-defined module. Objects of type P, RF, V, TR, FN, IF, TF, and R have an associated SQL module. Stand-alone defaults, objects of type D, also have an SQL module definition in this view. For a description of these types, see the type column in the sys.objects catalog view
syslanguages sys.syslanguages Contains one row for each language present in the instance of SQL Server
sysmessages sys.messages Contains a row for each message_id or language_id of the error messages in the system, for both system-defined and user-defined messages
sysconstraints sys.check_constraints
sys.default_constraints
sys.key_constraints
sys.foreign_keys
 
sysindexes sys.indexes
sys.partitions
sys.allocation_units
sys.dm_db_partition_stats
 
Process syslockinfo sys.dm_tran_locks Returns information about currently active lock manager resources. Each row represents a currently active request to the lock manager for a lock that has been granted or is waiting to be granted
sysprocesses sys.dm_exec_connections
sys.dm_exec_sessions
sys.dm_exec_requests
 
sysperfinfo sys.dm_os_performance_counters Returns a row per performance counter maintained by the server
syslocks sys.dm_tran_locks Returns information about currently active lock manager resources. Each row represents a currently active request to the lock manager for a lock that has been granted or is waiting to be granted.
Security syslogins sys.server_principals
sys.sql_logins
 
sysmembers sys.database_role_members Returns one row for each member of each database role
syspermissions sys.database_permissions
sys.server_permissions
 
sysusers sys.database_principals Returns a row for each principal in a database
Link Servers sysservers sys.servers Contains a row per linked or remote server registered, and a row for the local server that has server_id = 0

For complete system objects just visit
http://msdn.microsoft.com/en-us/library/ms187997.aspx

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: