December 6, 2012

Compatibility Views

Se more view version maps here: Microsoft Sql System View Version Maps

sys.sysalt_files

Under special circumstances, contains rows corresponding to the files in a database. Important This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
fileid smallint File identification number. This is unique for each database.
groupid smallint File group identification number.
size int File size, in 8-kilobyte (KB) pages.
maxsize int Maximum file size, in 8-KB pages.
0 = No growth.
-1 = File will grow until the disk is full.
268435456 = Log file will grow to a maximum size of 2 TB.
Note: Databases that are upgraded with an unlimited log file size will report -1 for the maximum size of the log file.
growth int Growth size of the database.
0 = No growth. Can be either the number of pages or the percentage of file size, depending on the value of status. If status is 0x100000, growth is the percentage of file size; otherwise, it is the number of pages.
status int Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
perf int Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
dbid smallint Database identification number of the database to which this file belongs.
name sysname Logical name of the file.
filename nvarchar(260) Name of the physical device. This includes the full path of the file.

TSQL

Sql 2005
SELECT [fileid], [groupid], [size], [maxsize], [growth], [status], [perf], [dbid], [name], [filename] FROM sys.sysalt_files
Sql 2008
SELECT [fileid], [groupid], [size], [maxsize], [growth], [status], [perf], [dbid], [name], [filename] FROM sys.sysalt_files
Sql 2008 R2
SELECT [fileid], [groupid], [size], [maxsize], [growth], [status], [perf], [dbid], [name], [filename] FROM sys.sysalt_files
Sql 2012
SELECT [fileid], [groupid], [size], [maxsize], [growth], [status], [perf], [dbid], [name], [filename] FROM sys.sysalt_files

Back to Top


sys.syscache_objects

Contains information about how the cache is used. Important This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
bucketid int Bucket ID. Value indicates a range from 0 through (directory size - 1). Directory size is the size of the hash table.
cacheobjtype nvarchar(17) Type of object in the cache:
Compiled plan

Executable plan

Parse tree

Cursor

Extended stored procedure
objtype nvarchar(8) Type of object:
Stored procedure

Prepared statement

Ad hoc query (Transact-SQL submitted as language events from the sqlcmd or osql utilities, instead of remote procedure calls)

ReplProc (replication procedure)

Trigger

View

Default

User table

System table

Check

Rule
objid int One of the main keys used for looking up an object in the cache. This is the object ID stored in sysobjects for database objects (procedures, views, triggers, and so on). For cache objects such as ad hoc or prepared SQL, objid is an internally generated value.
dbid smallint Database ID in which the cache object was compiled.
dbidexec smallint Database ID from which the query is executed.
For most objects, dbidexec has the same value as dbid.
For system views, dbidexec is the database ID from which the query is executed.
For ad hoc queries, dbidexec is 0. This means dbidexec has the same value as dbid.
uid smallint Indicates the creator of the plan for ad hoc query plans and prepared plans.
-2 = The batch submitted does not depend on implicit name resolution and can be shared among different users. This is the preferred method. Any other value represents the user ID of the user submitting the query in the database.
Overflows or returns NULL if the number of users and roles exceeds 32,767. For more information, see Querying the SQL Server System Catalog2.
refcounts int Number of other cache objects referencing this cache object. A count of 1 is the base.
usecounts int Number of times this cache object has been used since inception.
pagesused int Number of pages consumed by the cache object.
lasttime bigint For backward compatibility only. Always returns 0.
maxexectime bigint For backward compatibility only. Always returns 0.
avgexectime bigint For backward compatibility only. Always returns 0.
lastreads bigint For backward compatibility only. Always returns 0.
lastwrites bigint For backward compatibility only. Always returns 0.
setopts int SET option settings that affect a compiled plan. These settings are part of the cache key. Changes to values in this column indicate users have modified SET options.
These options include the following:
ANSI_PADDING

FORCEPLAN

CONCAT_NULL_YIELDS_NULL

ANSI_WARNINGS

ANSI_NULLS

QUOTED_IDENTIFIER

ANSI_NULL_DFLT_ON

ANSI_NULL_DFLT_OFF
langid smallint Language ID. ID of the language of the connection that created the cache object.
dateformat smallint Date format of the connection that created the cache object.
status int Indicates whether the cache object is a cursor plan. Currently, only the least significant bit is used.
sqlbytes int Length in bytes of the procedure definition or batch submitted.
sql nvarchar(3900) Module definition or the first 3900 characters of the batch submitted.

TSQL

Sql 2005
SELECT [bucketid], [cacheobjtype], [objtype], [objid], [dbid], [dbidexec], [uid], [refcounts], [usecounts], [pagesused], [lasttime], [maxexectime], [avgexectime], [lastreads], [lastwrites], [setopts], [langid], [dateformat], [status], [sqlbytes], [sql] FROM sys.syscache_objects
Sql 2008
SELECT [bucketid], [cacheobjtype], [objtype], [objid], [dbid], [dbidexec], [uid], [refcounts], [usecounts], [pagesused], [lasttime], [maxexectime], [avgexectime], [lastreads], [lastwrites], [setopts], [langid], [dateformat], [status], [sqlbytes], [sql] FROM sys.syscache_objects
Sql 2008 R2
SELECT [bucketid], [cacheobjtype], [objtype], [objid], [dbid], [dbidexec], [uid], [refcounts], [usecounts], [pagesused], [lasttime], [maxexectime], [avgexectime], [lastreads], [lastwrites], [setopts], [langid], [dateformat], [status], [sqlbytes], [sql] FROM sys.syscache_objects
Sql 2012
SELECT [bucketid], [cacheobjtype], [objtype], [objid], [dbid], [dbidexec], [uid], [refcounts], [usecounts], [pagesused], [setopts], [langid], [dateformat], [status], [lasttime], [maxexectime], [avgexectime], [lastreads], [lastwrites], [sqlbytes], [sql] FROM sys.syscache_objects

Back to Top


sys.syscharsets

Contains one row for each character set and sort order defined for use by the SQL Server Database Engine. One of the sort orders is marked in sysconfigures as the default sort order. This is the only one actually being used.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
type smallint Type of entity this row represents:
1001 = Character set.
2001 = Sort order.
id tinyint Unique ID for the character set or sort order. Note sort orders and character sets cannot share the same ID number. The ID range of 1 through 240 is reserved for use by the Database Engine.
csid tinyint If the row represents a character set, this field is unused. If the row represents a sort order, this field is the ID of the character set that the sort order is built on. It is assumed a character set row with this ID exists in this table.
status smallint Internal system status information bits.
name sysname Unique name for the character set or sort order. This field must contain only the letters A-Z or a-z, numbers 0 - 9, and underscores(_); and it must start with a letter.
description nvarchar(255) Optional description of the features of the character set or sort order.
binarydefinition varbinary(255) Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
definition image Internal definition of the character set or sort order. The structure of the data in this field depends on the type.

TSQL

Sql 2005
SELECT [type], [id], [csid], [status], [name], [description], [binarydefinition], [definition] FROM sys.syscharsets
Sql 2008
SELECT [type], [id], [csid], [status], [name], [description], [binarydefinition], [definition] FROM sys.syscharsets
Sql 2008 R2
SELECT [type], [id], [csid], [status], [name], [description], [binarydefinition], [definition] FROM sys.syscharsets
Sql 2012
SELECT [type], [id], [csid], [status], [name], [description], [binarydefinition], [definition] FROM sys.syscharsets

Back to Top


sys.syscolumns

Returns one row for every column in every table and view, and a row for each parameter in a stored procedure in the database. Important This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
name sysname Name of the column or procedure parameter.
id int Object ID of the table to which this column belongs, or the ID of the stored procedure with which this parameter is associated.
xtype tinyint Physical storage type from sys.types.
typestat tinyint Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
xusertype smallint ID of extended user-defined data type. Overflows or returns NULL if the number of data types exceeds 32,767. For more information, see Querying the SQL Server System Catalog2.
length smallint Maximum physical storage length from sys.types.
xprec tinyint Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
xscale tinyint Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
colid smallint Column or parameter ID.
xoffset smallint Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
bitpos tinyint Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
reserved tinyint Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
colstat smallint Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
cdefault int ID of the default for this column.
domain int ID of the rule or CHECK constraint for this column.
number smallint Subprocedure number when the procedure is grouped.
0 = Nonprocedure entries
colorder smallint Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
autoval varbinary(8000) Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
offset smallint Offset into the row in which this column appears.
collationid int ID of the collation of the column. NULL for noncharacter-based columns.
status tinyint Bitmap used to describe a property of the column or the parameter:
0x08 = Column allows null values.
0x10 = ANSI padding was in effect when varchar or varbinary columns were added. Trailing blanks are preserved for varchar and trailing zeros are preserved for varbinary columns.
0x40 = Parameter is an OUTPUT parameter.
0x80 = Column is an identity column.
type tinyint Physical storage type from sys.types.
usertype smallint ID of user-defined data type from sys.types. Overflows or returns NULL if the number of data types exceeds 32,767. For more information, see Querying the SQL Server System Catalog2.
printfmt varchar(255) Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
prec smallint Level of precision for this column.
-1 = xml or large value type.
scale int Scale for this column.
NULL = Data type is nonnumeric.
iscomputed int Flag indicating whether the column is computed:
0 = Noncomputed
1 = Computed
isoutparam int Indicates whether the procedure parameter is an output parameter:
1 = True
0 = False
isnullable int Indicates whether the column allows null values:
1 = True
0 = False
collation sysname Name of the collation of the column. NULL if not a character-based column.

TSQL

Sql 2005
SELECT [name], [id], [xtype], [typestat], [xusertype], [length], [xprec], [xscale], [colid], [xoffset], [bitpos], [reserved], [colstat], [cdefault], [domain], [number], [colorder], [autoval], [offset], [collationid], [status], [type], [usertype], [printfmt], [prec], [scale], [iscomputed], [isoutparam], [isnullable], [collation] FROM sys.syscolumns
Sql 2008
SELECT [name], [id], [xtype], [typestat], [xusertype], [length], [xprec], [xscale], [colid], [xoffset], [bitpos], [reserved], [colstat], [cdefault], [domain], [number], [colorder], [autoval], [offset], [collationid], [status], [type], [usertype], [printfmt], [prec], [scale], [iscomputed], [isoutparam], [isnullable], [collation] FROM sys.syscolumns
Sql 2008 R2
SELECT [name], [id], [xtype], [typestat], [xusertype], [length], [xprec], [xscale], [colid], [xoffset], [bitpos], [reserved], [colstat], [cdefault], [domain], [number], [colorder], [autoval], [offset], [collationid], [status], [type], [usertype], [printfmt], [prec], [scale], [iscomputed], [isoutparam], [isnullable], [collation] FROM sys.syscolumns
Sql 2012
SELECT [name], [id], [xtype], [typestat], [xusertype], [length], [xprec], [xscale], [colid], [xoffset], [bitpos], [reserved], [colstat], [cdefault], [domain], [number], [colorder], [autoval], [offset], [collationid], [status], [type], [usertype], [printfmt], [prec], [scale], [iscomputed], [isoutparam], [isnullable], [collation] FROM sys.syscolumns

Back to Top


sys.syscomments

Contains entries for each view, rule, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure within the database. The text column contains the original SQL definition statements. Important This feature will be removed in a future version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. We recommend that you use sys.sql_modules instead. For more information, see sys.sql_modules (Transact-SQL)1.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
id int Object ID to which this text applies.
number smallint Number within procedure grouping, if grouped.
0 = Entries are not procedures.
colid smallint Row sequence number for object definitions that are longer than 4,000 characters.
status smallint Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
ctext varbinary(8000) The raw bytes of the SQL definition statement.
texttype smallint 0 = User-supplied comment
1 = System-supplied comment
4 = Encrypted comment
language smallint Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
encrypted bit Indicates whether the procedure definition is obfuscated.
0 = Not obfuscated
1 = Obfuscated
Important: To obfuscate stored procedure definitions, use CREATE PROCEDURE with the ENCRYPTION keyword.
compressed bit Always returns 0. This indicates that the procedure is compressed.
text nvarchar(4000) Actual text of the SQL definition statement.
SQL Server 2005 differs from SQL Server 2000 in the way it decodes and stores SQL expressions in the catalog metadata. The semantics of the decoded expression are equivalent to the original text; however, there are no syntactic guarantees. For example, white spaces are removed from the decoded expression. For more information, see, Behavior Changes to Database Engine Features in SQL Server 20052.

TSQL

Sql 2005
SELECT [id], [number], [colid], [status], [ctext], [texttype], [language], [encrypted], [compressed], [text] FROM sys.syscomments
Sql 2008
SELECT [id], [number], [colid], [status], [ctext], [texttype], [language], [encrypted], [compressed], [text] FROM sys.syscomments
Sql 2008 R2
SELECT [id], [number], [colid], [status], [ctext], [texttype], [language], [encrypted], [compressed], [text] FROM sys.syscomments
Sql 2012
SELECT [id], [number], [colid], [status], [ctext], [texttype], [language], [encrypted], [compressed], [text] FROM sys.syscomments

Back to Top


sys.sysconfigures

Contains one row for each configuration option set by a user. sysconfigures contains the configuration options that are defined before the most recent startup of SQL Server, plus any dynamic configuration options set since then. Important This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
value int User-modifiable value for the variable. This is used by the Database Engine only if RECONFIGURE has been executed.
config int Configuration variable number.
comment nvarchar(255) Explanation of the configuration option.
status smallint Bitmap that indicates the status for the option. Possible values include the following:
0 = Static. Setting takes effect when the server is restarted.
1 = Dynamic. Variable takes effect when the RECONFIGURE statement is executed.
2 = Advanced. Variable is displayed only when the show advanced options is set. Setting takes effect when the server is restarted.
3 = Dynamic and advanced.

TSQL

Sql 2005
SELECT [value], [config], [comment], [status] FROM sys.sysconfigures
Sql 2008
SELECT [value], [config], [comment], [status] FROM sys.sysconfigures
Sql 2008 R2
SELECT [value], [config], [comment], [status] FROM sys.sysconfigures
Sql 2012
SELECT [value], [config], [comment], [status] FROM sys.sysconfigures

Back to Top


sys.sysconstraints

Contains mappings of constraints to the objects that own the constraints within the database. Important This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
constid int Constraint number.
id int ID of the table that owns the constraint.
colid smallint ID of the column on which the constraint is defined.
0 = Table constraint
spare1 tinyint Reserved
status int Pseudo-bit-mask indicating the status. Possible values include the following:
1 = PRIMARY KEY constraint
2 = UNIQUE KEY constraint
3 = FOREIGN KEY constraint
4 = CHECK constraint
5 = DEFAULT constraint
16 = Column-level constraint
32 = Table-level constraint
actions int Reserved
error int Reserved

TSQL

Sql 2005
SELECT [constid], [id], [colid], [spare1], [status], [actions], [error] FROM sys.sysconstraints
Sql 2008
SELECT [constid], [id], [colid], [spare1], [status], [actions], [error] FROM sys.sysconstraints
Sql 2008 R2
SELECT [constid], [id], [colid], [spare1], [status], [actions], [error] FROM sys.sysconstraints
Sql 2012
SELECT [constid], [id], [colid], [spare1], [status], [actions], [error] FROM sys.sysconstraints

Back to Top


sys.syscur_configs

Contains an entry for each current configuration option. Also, this view contains four entries that describe the configuration structure. syscurconfigs is built dynamically when queried by a user. For more information, see sys.sysconfigures (Transact-SQL)1. Important This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
value int User-modifiable value for the variable. This is used by the Microsoft SQL Server 2005 Database Engine only if RECONFIGURE has been executed.
config smallint Configuration variable number.
comment nvarchar(255) Explanation of the configuration option.
status smallint Bitmap indicating the status for the option. Possible values include the following:
0 = Static. Setting takes effect when the server is restarted.
1 = Dynamic. Variable takes effect when the RECONFIGURE statement is executed.
2 = Advanced. Variable is displayed only when the show advanced options is set.
3 = Dynamic and advanced.

TSQL

Sql 2005
SELECT [value], [config], [comment], [status] FROM sys.syscur_configs
Sql 2008
SELECT [value], [config], [comment], [status] FROM sys.syscur_configs
Sql 2008 R2
SELECT [value], [config], [comment], [status] FROM sys.syscur_configs
Sql 2012
SELECT [value], [config], [comment], [status] FROM sys.syscur_configs

Back to Top


sys.sysdatabases

Contains one row for each database in an instance of Microsoft SQL Server. When SQL Server is first installed, sysdatabases contains entries for the master, model, msdb, and tempdb databases. Important This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
name sysname Database name
dbid smallint Database ID
sid varbinary(85) System ID of the database creator
mode smallint Used internally for locking a database while it is being created.
status int Status bits, some of which can be set by using ALTER DATABASE2 as noted:
1 = autoclose (ALTER DATABASE)
4 = select into/bulkcopy (ALTER DATABASE using SET RECOVERY)
8 = trunc. log on chkpt (ALTER DATABASE using SET RECOVERY)
16 = torn page detection (ALTER DATABASE)
32 = loading
64 = pre recovery
128 = recovering
256 = not recovered
512 = offline (ALTER DATABASE)
1024 = read only (ALTER DATABASE)
2048 = dbo use only (ALTER DATABASE using SET RESTRICTED_USER)
4096 = single user (ALTER DATABASE)
32768 = emergency mode
4194304 = autoshrink (ALTER DATABASE)
1073741824 = cleanly shutdown
Multiple bits can be ON at the same time.
status2 int 16384 = ANSI null default (ALTER DATABASE)
65536 = concat null yields null (ALTER DATABASE)
131072 = recursive triggers (ALTER DATABASE)
1048576 = default to local cursor (ALTER DATABASE)
8388608 = quoted identifier (ALTER DATABASE)
33554432 = cursor close on commit (ALTER DATABASE)
67108864 = ANSI nulls (ALTER DATABASE)
268435456 = ANSI warnings (ALTER DATABASE)
536870912 = full text enabled (set by using sp_fulltext_database)
crdate datetime Creation date
reserved datetime Reserved for future use.
category int Contains a bitmap of information used for replication:
1 = Published for snapshot or transactional replication.
2 = Subscribed to a snapshot or transactional publication.
4 = Published for merge replication.
8 = Subscribed to a merge publication.
16 = Distribution database.
cmptlevel tinyint Compatibility level for the database. For more information, see sp_dbcmptlevel (Transact-SQL)3.
filename nvarchar(260) Operating-system path and name for the primary file for the database.
filename is visible to dbcreator, sysadmin, the database owner with CREATE ANY DATABASE permissions, or grantees that have any one of the following permissions: ALTER ANY DATABASE, CREATE ANY DATABASE, VIEW ANY DEFINITION. To return the path and file name, query the sys.sysfiles4 compatibility view, or the sys.database_files5 view.
version smallint Internal version number of the SQL Server code with which the database was created. For internal use only by SQL Server tools and in upgrade processing.

TSQL

Sql 2005
SELECT [name], [dbid], [sid], [mode], [status], [status2], [crdate], [reserved], [category], [cmptlevel], [filename], [version] FROM sys.sysdatabases
Sql 2008
SELECT [name], [dbid], [sid], [mode], [status], [status2], [crdate], [reserved], [category], [cmptlevel], [filename], [version] FROM sys.sysdatabases
Sql 2008 R2
SELECT [name], [dbid], [sid], [mode], [status], [status2], [crdate], [reserved], [category], [cmptlevel], [filename], [version] FROM sys.sysdatabases
Sql 2012
SELECT [name], [dbid], [sid], [mode], [status], [status2], [crdate], [reserved], [category], [cmptlevel], [filename], [version] FROM sys.sysdatabases

Back to Top


sys.sysdepends

Contains dependency information between objects (views, procedures, and triggers) in the database, and the objects (tables, views, and procedures) that are contained in their definition. Important This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
id int Object ID
depid int Dependent object ID
number smallint Procedure number
depnumber smallint Dependent procedure number
status smallint Internal status information
depdbid smallint Reserved
depsiteid smallint Reserved
selall bit 1 = Object is used in a SELECT * statement.
0 = No
resultobj bit 1 = Object is being updated.
0 = No
readobj bit 1 = The object is being read.
0 = No
deptype       tinyint Identifies the dependent object type:
0 = Object or column (non-schema-bound references only
1 = Object or column (schema-bound references)

TSQL

Sql 2005
SELECT [id], [depid], [number], [depnumber], [status], [depdbid], [depsiteid], [selall], [resultobj], [readobj] FROM sys.sysdepends
Sql 2008
SELECT [id], [depid], [number], [depnumber], [status], [depdbid], [depsiteid], [selall], [resultobj], [readobj] FROM sys.sysdepends
Sql 2008 R2
SELECT [id], [depid], [number], [depnumber], [status], [depdbid], [depsiteid], [selall], [resultobj], [readobj] FROM sys.sysdepends
Sql 2012
SELECT [id], [depid], [number], [depnumber], [status], [deptype], [depdbid], [depsiteid], [selall], [resultobj], [readobj] FROM sys.sysdepends

Back to Top


sys.sysdevices

Contains one row for each disk backup file, tape backup file, and database file. Important This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
name sysname Logical name of the backup file or database file.
size int Size of the file in 2-kilobyte (KB) pages.
low int Maintained for backward compatibility only.
high int Maintained for backward compatibility only.
status smallint Bitmap indicating the type of device:
1 = Default disk
2 = Physical disk
4 = Logical disk
8 = Skip header
16 = Backup file
32 = Serial writes
4096 = Read-only
cntrltype smallint Controller type:
0 = Non-CD-ROM database file
2 = Disk backup file
3 - 4 = Diskette backup file
5 = Tape backup file
6 = Named-pipe file
phyname nvarchar(260) Name of the physical file.

TSQL

Sql 2005
SELECT [name], [size], [low], [high], [status], [cntrltype], [phyname] FROM sys.sysdevices
Sql 2008
SELECT [name], [size], [low], [high], [status], [cntrltype], [phyname] FROM sys.sysdevices
Sql 2008 R2
SELECT [name], [size], [low], [high], [status], [cntrltype], [phyname] FROM sys.sysdevices
Sql 2012
SELECT [name], [size], [low], [high], [status], [cntrltype], [phyname] FROM sys.sysdevices

Back to Top


sys.sysfile_groups

Contains one row for each file group in a database. There is at least one entry in this table that is for the primary file group. Important This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
groupid smallint Group identification number unique for each database.
allocpolicy smallint Reserved
status int 0x8 = Read-only
0x10 = Default
groupname sysname Name of the file group.

TSQL

Sql 2005
SELECT [groupid], [allocpolicy], [status], [groupname] FROM sys.sysfile_groups
Sql 2008
SELECT [groupid], [allocpolicy], [status], [groupname] FROM sys.sysfile_groups
Sql 2008 R2
SELECT [groupid], [allocpolicy], [status], [groupname] FROM sys.sysfile_groups
Sql 2012
SELECT [groupid], [allocpolicy], [status], [groupname] FROM sys.sysfile_groups

Back to Top


sys.sysfiles

Contains one row for each file in a database. Important This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
fileid smallint File identification number unique for each database.
groupid smallint File group identification number.
size int Size of the file, in 8-KB pages.
maxsize int Maximum file size, in 8-KB pages.
0 = No growth.
-1 = File will grow until the disk is full.
268435456 = Log file will grow to a maximum size of 2 TB.
Note: Databases that are upgraded with an unlimited log file size will report -1 for the maximum size of the log file.
growth int Growth size of the database. Can be either the number of pages or the percentage of file size, depending on value of status.
0 = No growth.
status int Status bits for the growth value in either megabytes (MB) or kilobytes (KB).
0x2 = Disk file.
0x40 = Log file.
0x100000 = Growth. This value is a percentage and not the number of pages.
perf int Reserved.
name sysname Logical name of the file.
filename nvarchar(260) Name of the physical device. This includes the full path of the file.

TSQL

Sql 2005
SELECT [fileid], [groupid], [size], [maxsize], [growth], [status], [perf], [name], [filename] FROM sys.sysfiles
Sql 2008
SELECT [fileid], [groupid], [size], [maxsize], [growth], [status], [perf], [name], [filename] FROM sys.sysfiles
Sql 2008 R2
SELECT [fileid], [groupid], [size], [maxsize], [growth], [status], [perf], [name], [filename] FROM sys.sysfiles
Sql 2012
SELECT [fileid], [groupid], [size], [maxsize], [growth], [status], [perf], [name], [filename] FROM sys.sysfiles

Back to Top


sys.sysforeign_keys

Contains information about the FOREIGN KEY constraints that are in the definitions of tables in the database. Important This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
constid int ID of the FOREIGN KEY constraint.
fkeyid int Object ID of the table with the FOREIGN KEY constraint.
rkeyid int Object ID of the table referenced in the FOREIGN KEY constraint.
fkey smallint ID of the referencing column.
rkey smallint ID of the referenced column.
keyno smallint Position of the column in the reference column list.

TSQL

Sql 2005
SELECT [constid], [fkeyid], [rkeyid], [fkey], [rkey], [keyno] FROM sys.sysforeign_keys
Sql 2008
SELECT [constid], [fkeyid], [rkeyid], [fkey], [rkey], [keyno] FROM sys.sysforeign_keys
Sql 2008 R2
SELECT [constid], [fkeyid], [rkeyid], [fkey], [rkey], [keyno] FROM sys.sysforeign_keys
Sql 2012
SELECT [constid], [fkeyid], [rkeyid], [fkey], [rkey], [keyno] FROM sys.sysforeign_keys

Back to Top


sys.sysfulltext_catalogs

Contains information about the full-text catalogs. Important This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
ftcatid smallint Identifier of the full-text catalog.
name sysname Full-text catalog name specified by the user.
status smallint Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
path nvarchar(260) Root path specified by the user.
NULL = Path was not specified. The default (installation) path was used.

TSQL

Sql 2005
SELECT [ftcatid], [name], [status], [path] FROM sys.sysfulltext_catalogs
Sql 2008
SELECT [ftcatid], [name], [status], [path] FROM sys.sysfulltext_catalogs
Sql 2008 R2
SELECT [ftcatid], [name], [status], [path] FROM sys.sysfulltext_catalogs
Sql 2012
SELECT [ftcatid], [name], [status], [path] FROM sys.sysfulltext_catalogs

Back to Top


sys.sysindexes

Contains one row for each index and table in the current database. XML indexes are not supported in this view. Partitioned tables and indexes are not fully supported in this view; use the sys.indexes1 catalog view instead. Important This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
id int ID of the table to which the index belongs.
status int Internal system-status information.
first binary(6) Pointer to the first or root page.
In SQL Server version 6.5 and earlier, sysindexes.first always points to the start of a heap, the start of the leaf level of an index, or the start of a chain of text and image pages.
In SQL Server version 7.0 and later, sysindexes.first is unused when indid = 0.
NULL = Index is partitioned when indid > 1.
NULL = Table is partitioned when indid is 0 or 1.
indid smallint ID of the index:
0 = Heap
1 = Clustered index
>1 = Nonclustered index
root binary(6) For indid >= 1, root is the pointer to the root page.
In SQL Server version 6.5 and earlier, sysindexes.root points to the last page in a heap when indid = 0.
In SQL Server version 7.0 and later, sysindexes.root is unused when indid = 0.
NULL = Index is partitioned when indid > 1.
NULL = Table is partitioned when indid is 0 or 1.
minlen smallint Minimum size of a row
keycnt smallint Number of keys
groupid smallint Filegroup ID on which the object was created.
NULL = Index is partitioned when indid > 1.
NULL = Table is partitioned when indid is 0 or 1.
dpages int For indid = 0 or indid = 1, dpages is the count of data pages used.
For indid > 1, dpages is the count of index pages used.
0 = Index is partitioned when indid > 1.
0 = Table is partitioned when indid is 0 or 1.
Does not yield accurate results if row-overflow occurs.
reserved int For indid = 0 or indid = 1, reserved is the count of pages allocated for all indexes and table data.
For indid > 1, reserved is the count of pages allocated for the index.
0 = Index is partitioned when indid > 1.
0 = Table is partitioned when indid is 0 or 1.
Does not yield accurate results if row-overflow occurs.
used int For indid = 0 or indid = 1, used is the count of the total pages used for all index and table data.
For indid > 1, used is the count of pages used for the index.
0 = Index is partitioned when indid > 1.
0 = Table is partitioned when indid is 0 or 1.
Does not yield accurate results if row-overflow occurs.
rowcnt bigint Data-level row count based on indid = 0 and indid = 1.
0 = Index is partitioned when indid > 1.
0 = Table is partitioned when indid is 0 or 1.
rowmodctr int Counts the total number of inserted, deleted, or updated rows since the last time statistics were updated for the table.
0 = Index is partitioned when indid > 1.
0 = Table is partitioned when indid is 0 or 1.
In SQL Server 2005, rowmodctr is not fully compatible with earlier versions. For more information, see Remarks.
xmaxlen smallint Maximum size of a row
maxirow smallint Maximum size of a nonleaf index row.
In SQL Server 2005, maxirow is not fully compatible with earlier versions.
origfillfactor tinyint Original fill factor value used when the index was created. This value is not maintained; however, it can be helpful if you have to re-create an index and do not remember the fill factor value that was used.
statversion tinyint Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
Returns 0.
reserved2 int Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
Returns 0.
firstiam binary(6) Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
NULL = Index is partitioned.
impid smallint Identified for informational purposes only. Not supported. Future compatibility is not guaranteed. Index implementation flag.
Returns 0.
lockflags smallint Used to constrain the considered lock granularities for an index. For example, to minimize locking cost, a lookup table that is essentially read-only could be set up to do only table-level locking.
pgmodctr int Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
Returns 0.
keys varbinary(816) List of the column IDs of the columns that make up the index key.
Returns NULL.
To display the index key columns, use sys.sysindexkeys (Transact-SQL)3.
name sysname Name of the index or statistic. Returns NULL when indid = 0. Modify your application to look for a NULL heap name.
statblob image Statistics binary large object (BLOB).
Returns NULL.
maxlen int Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
rows int Data-level row count based on indid = 0 and indid = 1, and the value is repeated for indid >1.
reserved3       int Returns 0.
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
reserved4       int Returns 0.
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

TSQL

Sql 2005
SELECT [id], [status], [first], [indid], [root], [minlen], [keycnt], [groupid], [dpages], [reserved], [used], [rowcnt], [rowmodctr], [xmaxlen], [maxirow], [origfillfactor], [statversion], [reserved2], [firstiam], [impid], [lockflags], [pgmodctr], [keys], [name], [statblob], [maxlen], [rows] FROM sys.sysindexes
Sql 2008
SELECT [id], [status], [first], [indid], [root], [minlen], [keycnt], [groupid], [dpages], [reserved], [used], [rowcnt], [rowmodctr], [xmaxlen], [maxirow], [origfillfactor], [statversion], [reserved2], [firstiam], [impid], [lockflags], [pgmodctr], [keys], [name], [statblob], [maxlen], [rows] FROM sys.sysindexes
Sql 2008 R2
SELECT [id], [status], [first], [indid], [root], [minlen], [keycnt], [groupid], [dpages], [reserved], [used], [rowcnt], [rowmodctr], [xmaxlen], [maxirow], [origfillfactor], [statversion], [reserved2], [firstiam], [impid], [lockflags], [pgmodctr], [keys], [name], [statblob], [maxlen], [rows] FROM sys.sysindexes
Sql 2012
SELECT [id], [status], [first], [indid], [root], [minlen], [keycnt], [groupid], [dpages], [reserved], [used], [rowcnt], [rowmodctr], [reserved3], [reserved4], [xmaxlen], [maxirow], [origfillfactor], [statversion], [reserved2], [firstiam], [impid], [lockflags], [pgmodctr], [keys], [name], [statblob], [maxlen], [rows] FROM sys.sysindexes

Back to Top


sys.sysindexkeys

Contains information about the keys or columns in an index of the database. Important This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
id int ID of the table.
indid smallint ID of the index.
colid smallint ID of the column.
keyno smallint Position of the column in the index.

TSQL

Sql 2005
SELECT [id], [indid], [colid], [keyno] FROM sys.sysindexkeys
Sql 2008
SELECT [id], [indid], [colid], [keyno] FROM sys.sysindexkeys
Sql 2008 R2
SELECT [id], [indid], [colid], [keyno] FROM sys.sysindexkeys
Sql 2012
SELECT [id], [indid], [colid], [keyno] FROM sys.sysindexkeys

Back to Top


sys.syslanguages

Contains one row for each language present in the instance of SQL Server.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
langid smallint Unique language ID.
dateformat nchar(3) Date order, for example, DMY.
datefirst tinyint First day of the week: 1 for Monday, 2 for Tuesday, and so on through 7 for Sunday.
upgrade int Reserved for system use.
name sysname Official language name, for example, Français.
alias sysname Alternative language name, for example, French.
months nvarchar(372) Comma-separated list of full-length month names in order from January through December, with each name having up to 20 characters.
shortmonths varchar(132) Comma-separated list of short-month names in order from January through December, with each name having up to 9 characters.
days nvarchar(217) Comma-separated list of day names in order from Monday through Sunday, with each name having up to 30 characters.
lcid int Microsoft Windows locale ID for the language.
msglangid smallint Database Engine message group ID.

TSQL

Sql 2005
SELECT [langid], [dateformat], [datefirst], [upgrade], [name], [alias], [months], [shortmonths], [days], [lcid], [msglangid] FROM sys.syslanguages
Sql 2008
SELECT [langid], [dateformat], [datefirst], [upgrade], [name], [alias], [months], [shortmonths], [days], [lcid], [msglangid] FROM sys.syslanguages
Sql 2008 R2
SELECT [langid], [dateformat], [datefirst], [upgrade], [name], [alias], [months], [shortmonths], [days], [lcid], [msglangid] FROM sys.syslanguages
Sql 2012
SELECT [langid], [dateformat], [datefirst], [upgrade], [name], [alias], [months], [shortmonths], [days], [lcid], [msglangid] FROM sys.syslanguages

Back to Top


sys.syslock_info

Contains information about all granted, converting, and waiting lock requests. Important This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Important This feature has changed from earlier versions of SQL Server. For more information, see Breaking Changes to Database Engine Features in SQL Server 20121.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
rsc_text nchar(32) Textual description of a lock resource. Contains a part of the resource name.
rsc_bin binary(16) Binary lock resource. Contains the actual lock resource that is contained in the lock manager. This column is included for tools that know about the lock resource format for generating their own formatted lock resource, and for performing self joins on syslockinfo.
rsc_valblk binary(16) Lock value block. Some resource types may include additional data in the lock resource that is not hashed by the lock manager to determine ownership of a particular lock resource. For example, page locks are not owned by a particular object ID. For lock escalation and other purposes. However, the object ID of a page lock may be included in the lock value block.
rsc_dbid smallint Database ID associated with the resource.
rsc_indid smallint Index ID associated with the resource, if appropriate.
rsc_objid int Object ID associated with the resource, if appropriate.
rsc_type tinyint Resource type:
1 = NULL Resource (not used)
2 = Database
3 = File
4 = Index
5 = Table
6 = Page
7 = Key
8 = Extent
9 = RID (Row ID)
10 = Application
rsc_flag tinyint Internal resource flags.
req_mode tinyint Lock request mode. This column is the lock mode of the requester and represents either the granted mode, or the convert or waiting mode.
0 = NULL. No access is granted to the resource. Serves as a placeholder.
1 = Sch-S (Schema stability). Ensures that a schema element, such as a table or index, is not dropped while any session holds a schema stability lock on the schema element.
2 = Sch-M (Schema modification). Must be held by any session that wants to change the schema of the specified resource. Ensures that no other sessions are referencing the indicated object.
3 = S (Shared). The holding session is granted shared access to the resource.
4 = U (Update). Indicates an update lock acquired on resources that may eventually be updated. It is used to prevent a common form of deadlock that occurs when multiple sessions lock resources for potential update in the future.
5 = X (Exclusive). The holding session is granted exclusive access to the resource.
6 = IS (Intent Shared). Indicates the intention to place S locks on some subordinate resource in the lock hierarchy.
7 = IU (Intent Update). Indicates the intention to place U locks on some subordinate resource in the lock hierarchy.
8 = IX (Intent Exclusive). Indicates the intention to place X locks on some subordinate resource in the lock hierarchy.
9 = SIU (Shared Intent Update). Indicates shared access to a resource with the intent of acquiring update locks on subordinate resources in the lock hierarchy.
10 = SIX (Shared Intent Exclusive). Indicates shared access to a resource with the intent of acquiring exclusive locks on subordinate resources in the lock hierarchy.
11 = UIX (Update Intent Exclusive). Indicates an update lock hold on a resource with the intent of acquiring exclusive locks on subordinate resources in the lock hierarchy.
12 = BU. Used by bulk operations.
13 = RangeS_S (Shared Key-Range and Shared Resource lock). Indicates serializable range scan.
14 = RangeS_U (Shared Key-Range and Update Resource lock). Indicates serializable update scan.
15 = RangeI_N (Insert Key-Range and Null Resource lock). Used to test ranges before inserting a new key into an index.
16 = RangeI_S. Key-Range Conversion lock, created by an overlap of RangeI_N and S locks.
17 = RangeI_U. Key-Range Conversion lock, created by an overlap of RangeI_N and U locks.
18 = RangeI_X. Key-Range Conversion lock, created by an overlap of RangeI_N and X locks.
19 = RangeX_S. Key-Range Conversion lock, created by an overlap of RangeI_N and RangeS_S. locks.
20 = RangeX_U. Key-Range Conversion lock, created by an overlap of RangeI_N and RangeS_U locks.
21 = RangeX_X (Exclusive Key-Range and Exclusive Resource lock). This is a conversion lock used when updating a key in a range.
req_status tinyint Status of the lock request:
1 = Granted
2 = Converting
3 = Waiting
req_refcnt smallint Lock reference count. Every time a transaction asks for a lock on a particular resource, a reference count is incremented. The lock cannot be released until the reference count equals 0.
req_cryrefcnt smallint Reserved for future used. Always set to 0.
req_lifetime int Lock lifetime bitmap. During certain query processing strategies, locks must be maintained on resources until the query processor has completed a particular phase of the query. The lock lifetime bitmap is used by the query processor and transaction manager to indicate groups of locks that can be released when a certain phase of a query has finished running. Certain bits in the bitmap are used to indicate locks that are held until the end of a transaction, even if their reference count equals 0.
req_spid int Internal Microsoft SQL Server Database Engine process ID of the session requesting the lock.
req_ecid int Execution context ID (ECID). Used to indicate which thread in a parallel operation owns a particular lock.
req_ownertype smallint Type of object associated with the lock:
1 = Transaction
2 = Cursor
3 = Session
4 = ExSession
Note that 3 and 4 represent a special version of session locks, tracking database and file group locks, respectively.
req_transactionid bigint Unique transaction ID used in syslockinfo and in profiler event
req_transactionuow uniqueidentifier Identifies the Unit of Work ID (UOW) of the DTC transaction. For non-MS DTC transactions, UOW is set to 0.

TSQL

Sql 2005
SELECT [rsc_text], [rsc_bin], [rsc_valblk], [rsc_dbid], [rsc_indid], [rsc_objid], [rsc_type], [rsc_flag], [req_mode], [req_status], [req_refcnt], [req_cryrefcnt], [req_lifetime], [req_spid], [req_ecid], [req_ownertype], [req_transactionid], [req_transactionuow] FROM sys.syslock_info
Sql 2008
SELECT [rsc_text], [rsc_bin], [rsc_valblk], [rsc_dbid], [rsc_indid], [rsc_objid], [rsc_type], [rsc_flag], [req_mode], [req_status], [req_refcnt], [req_cryrefcnt], [req_lifetime], [req_spid], [req_ecid], [req_ownertype], [req_transactionid], [req_transactionuow] FROM sys.syslock_info
Sql 2008 R2
SELECT [rsc_text], [rsc_bin], [rsc_valblk], [rsc_dbid], [rsc_indid], [rsc_objid], [rsc_type], [rsc_flag], [req_mode], [req_status], [req_refcnt], [req_cryrefcnt], [req_lifetime], [req_spid], [req_ecid], [req_ownertype], [req_transactionid], [req_transactionuow] FROM sys.syslock_info
Sql 2012
SELECT [rsc_text], [rsc_bin], [rsc_valblk], [rsc_dbid], [rsc_indid], [rsc_objid], [rsc_type], [rsc_flag], [req_mode], [req_status], [req_refcnt], [req_cryrefcnt], [req_lifetime], [req_spid], [req_ecid], [req_ownertype], [req_transactionid], [req_transactionuow] FROM sys.syslock_info

Back to Top


sys.syslogins

Contains one row for each login account. Important This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
sid varbinary(85) Security identifier.
status smallint Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
createdate datetime Date the login was added.
updatedate datetime Date the login was updated.
accdate datetime Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
totcpu int Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
totio int Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
spacelimit int Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
timelimit int Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
resultlimit int Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
name sysname Login name of the user.
dbname sysname Name of the default database of the user when a connection is established.
password nvarchar(128) Returns NULL.
language sysname Default language of the user.
denylogin int 1 = Login is a Microsoft Windows user or group and has been denied access.
hasaccess int 1 = Login has been granted access to the server.
isntname int 1 = Login is a Windows user or group.
0 = Login is a Microsoft SQL Server 2005 login.
isntgroup int 1 = Login is a Windows group.
isntuser int 1 = Login is a Windows user.
sysadmin int 1 = Login is a member of the sysadmin server role.
securityadmin int 1 = Login is a member of the securityadmin server role.
serveradmin int 1 = Login is a member of the serveradmin fixed server role.
setupadmin int 1 = Login is a member of the setupadmin fixed server role.
processadmin int 1 = Login is a member of the processadmin fixed server role.
diskadmin int 1 = Login is a member of the diskadmin fixed server role.
dbcreator int 1 = Login is a member of the dbcreator fixed server role.
bulkadmin int 1 = Login is a member of the bulkadmin fixed server role.
loginname nvarchar(128) Login name of the user. Provided for backward compatibility.

TSQL

Sql 2005
SELECT [sid], [status], [createdate], [updatedate], [accdate], [totcpu], [totio], [spacelimit], [timelimit], [resultlimit], [name], [dbname], [password], [language], [denylogin], [hasaccess], [isntname], [isntgroup], [isntuser], [sysadmin], [securityadmin], [serveradmin], [setupadmin], [processadmin], [diskadmin], [dbcreator], [bulkadmin], [loginname] FROM sys.syslogins
Sql 2008
SELECT [sid], [status], [createdate], [updatedate], [accdate], [totcpu], [totio], [spacelimit], [timelimit], [resultlimit], [name], [dbname], [password], [language], [denylogin], [hasaccess], [isntname], [isntgroup], [isntuser], [sysadmin], [securityadmin], [serveradmin], [setupadmin], [processadmin], [diskadmin], [dbcreator], [bulkadmin], [loginname] FROM sys.syslogins
Sql 2008 R2
SELECT [sid], [status], [createdate], [updatedate], [accdate], [totcpu], [totio], [spacelimit], [timelimit], [resultlimit], [name], [dbname], [password], [language], [denylogin], [hasaccess], [isntname], [isntgroup], [isntuser], [sysadmin], [securityadmin], [serveradmin], [setupadmin], [processadmin], [diskadmin], [dbcreator], [bulkadmin], [loginname] FROM sys.syslogins
Sql 2012
SELECT [sid], [status], [createdate], [updatedate], [accdate], [totcpu], [totio], [spacelimit], [timelimit], [resultlimit], [name], [dbname], [password], [language], [denylogin], [hasaccess], [isntname], [isntgroup], [isntuser], [sysadmin], [securityadmin], [serveradmin], [setupadmin], [processadmin], [diskadmin], [dbcreator], [bulkadmin], [loginname] FROM sys.syslogins

Back to Top


sys.sysmembers

member of a database role. Important This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
memberuid smallint User ID for the role member. Overflows or returns NULL if the number of users and roles exceeds 32,767. For more information, see Querying the SQL Server System Catalog2.
groupuid smallint User ID for the role. Overflows or returns NULL if the number of users and roles exceeds 32,767. For more information, see Querying the SQL Server System Catalog2.

TSQL

Sql 2005
SELECT [memberuid], [groupuid] FROM sys.sysmembers
Sql 2008
SELECT [memberuid], [groupuid] FROM sys.sysmembers
Sql 2008 R2
SELECT [memberuid], [groupuid] FROM sys.sysmembers
Sql 2012
SELECT [memberuid], [groupuid] FROM sys.sysmembers

Back to Top


sys.sysmessages

Contains one row for each system error or warning that can be returned by the SQL Server Database Engine. The Database Engine displays the error description on the user's screen. Important This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
error int Unique error number.
severity tinyint Severity level of the error.
dlevel smallint Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
description nvarchar(255) Explanation of the error with placeholders for parameters.
msglangid smallint System message group ID.

TSQL

Sql 2005
SELECT [error], [severity], [dlevel], [description], [msglangid] FROM sys.sysmessages
Sql 2008
SELECT [error], [severity], [dlevel], [description], [msglangid] FROM sys.sysmessages
Sql 2008 R2
SELECT [error], [severity], [dlevel], [description], [msglangid] FROM sys.sysmessages
Sql 2012
SELECT [error], [severity], [dlevel], [description], [msglangid] FROM sys.sysmessages

Back to Top


sys.sysobjects

Contains one row for each object that is created within a database, such as a constraint, default, log, rule, and stored procedure. Important This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
name sysname Object name
id int Object identification number
xtype char(2) Object type. For a list of valid object types, see sys.objects2.
uid smallint Schema ID of the owner of the object.
For databases upgraded from an earlier version of SQL Server, the schema ID is equal to the user ID of the owner.
Important: If you use any of the following SQL Server 2005 DDL statements, you must use the sys.objects2 catalog view instead of sys.sysobjects. CREATE | ALTER | DROP USER CREATE | ALTER | DROP ROLE CREATE | ALTER | DROP APPLICATION ROLE CREATE SCHEMA ALTER AUTHORIZATION ON OBJECT

Overflows or returns NULL if the number of users and roles exceeds 32,767.
For more information, see Querying the SQL Server System Catalog3.
info smallint Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
status int Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
base_schema_ver int Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
replinfo int Reserved for use by replication.
parent_obj int Object identification number of the parent object. For example, the table ID if it is a trigger or constraint.
crdate datetime Date the object was created.
ftcatid smallint Identifier of the full-text catalog for all user tables registered for full-text indexing, and 0 for all user tables that are not registered.
schema_ver int Version number that is incremented every time the schema for a table changes. Always returns 0.
stats_schema_ver int Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
type char(2) Object type. For a list of valid object types, see sys.objects2.
userstat smallint Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
sysstat smallint Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
indexdel smallint Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
refdate datetime Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
version int Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
deltrig int Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
instrig int Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
updtrig int Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
seltrig int Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
category int Used for publication, constraints, and identity.
cache smallint Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

TSQL

Sql 2005
SELECT [name], [id], [xtype], [uid], [info], [status], [base_schema_ver], [replinfo], [parent_obj], [crdate], [ftcatid], [schema_ver], [stats_schema_ver], [type], [userstat], [sysstat], [indexdel], [refdate], [version], [deltrig], [instrig], [updtrig], [seltrig], [category], [cache] FROM sys.sysobjects
Sql 2008
SELECT [name], [id], [xtype], [uid], [info], [status], [base_schema_ver], [replinfo], [parent_obj], [crdate], [ftcatid], [schema_ver], [stats_schema_ver], [type], [userstat], [sysstat], [indexdel], [refdate], [version], [deltrig], [instrig], [updtrig], [seltrig], [category], [cache] FROM sys.sysobjects
Sql 2008 R2
SELECT [name], [id], [xtype], [uid], [info], [status], [base_schema_ver], [replinfo], [parent_obj], [crdate], [ftcatid], [schema_ver], [stats_schema_ver], [type], [userstat], [sysstat], [indexdel], [refdate], [version], [deltrig], [instrig], [updtrig], [seltrig], [category], [cache] FROM sys.sysobjects
Sql 2012
SELECT [name], [id], [xtype], [uid], [info], [status], [base_schema_ver], [replinfo], [parent_obj], [crdate], [ftcatid], [schema_ver], [stats_schema_ver], [type], [userstat], [sysstat], [indexdel], [refdate], [version], [deltrig], [instrig], [updtrig], [seltrig], [category], [cache] FROM sys.sysobjects

Back to Top


sys.sysoledbusers

Important This SQL Server 2000 system table is included in SQL Server as a view for backward compatibility only. We recommend that you use catalog views1 instead. Contains one row for each user and password mapping for the specified linked server. sysoledbusers is stored in the master database.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
rmtsrvid smallint Security identification number (SID) of the server.
rmtloginame nvarchar(128) Name of the remote login that loginsid maps to for linked rmtservid.
rmtpassword nvarchar(128) Returns NULL.
loginsid varbinary(85) SID of the local login to be mapped.
status smallint If 1, the mapping should use the credentials of the user.
changedate datetime Date the mapping information was last changed.

TSQL

Sql 2005
SELECT [rmtsrvid], [rmtloginame], [rmtpassword], [loginsid], [status], [changedate] FROM sys.sysoledbusers
Sql 2008
SELECT [rmtsrvid], [rmtloginame], [rmtpassword], [loginsid], [status], [changedate] FROM sys.sysoledbusers
Sql 2008 R2
SELECT [rmtsrvid], [rmtloginame], [rmtpassword], [loginsid], [status], [changedate] FROM sys.sysoledbusers
Sql 2012
SELECT [rmtsrvid], [rmtloginame], [rmtpassword], [loginsid], [status], [changedate] FROM sys.sysoledbusers

Back to Top


sys.sysperf_info

Contains a Microsoft SQL Server Database Engine representation of the internal performance counters that can be displayed through the Windows System Monitor. Important This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
object_name nchar(128) Performance object name, such as SQL Server: Lock Manager or SQL Server: Buffer Manager.
counter_name nchar(128) Name of the performance counter within the object, such as Page Requests or Locks Requested.
instance_name nchar(128) Named instance of the counter. For example, there are counters maintained for each type of lock, such as Table, Page, Key, and so on. The instance name distinguishes between similar counters.
cntr_value bigint Actual counter value. Frequently, this will be a level or monotonically increasing counter that counts occurrences of the instance event.
cntr_type int Type of counter as defined by the Windows performance architecture.

TSQL

Sql 2005
SELECT [object_name], [counter_name], [instance_name], [cntr_value], [cntr_type] FROM sys.sysperf_info
Sql 2008
SELECT [object_name], [counter_name], [instance_name], [cntr_value], [cntr_type] FROM sys.sysperf_info
Sql 2008 R2
SELECT [object_name], [counter_name], [instance_name], [cntr_value], [cntr_type] FROM sys.sysperf_info
Sql 2012
SELECT [object_name], [counter_name], [instance_name], [cntr_value], [cntr_type] FROM sys.sysperf_info

Back to Top


sys.syspermissions

Contains information about permissions granted and denied to users, groups, and roles in the database. Important This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
id int ID of the object for object permissions.
0 = Statement permissions.
grantee smallint ID of the user, group, or role affected by the permission.
grantor smallint ID of the user, group, or role that granted or denied the permission.
actadd smallint Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
actmod smallint Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
seladd varbinary(4000) Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
selmod varbinary(4000) Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
updadd varbinary(4000) Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
updmod varbinary(4000) Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
refadd varbinary(4000) Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
refmod varbinary(4000) Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

TSQL

Sql 2005
SELECT [id], [grantee], [grantor], [actadd], [actmod], [seladd], [selmod], [updadd], [updmod], [refadd], [refmod] FROM sys.syspermissions
Sql 2008
SELECT [id], [grantee], [grantor], [actadd], [actmod], [seladd], [selmod], [updadd], [updmod], [refadd], [refmod] FROM sys.syspermissions
Sql 2008 R2
SELECT [id], [grantee], [grantor], [actadd], [actmod], [seladd], [selmod], [updadd], [updmod], [refadd], [refmod] FROM sys.syspermissions
Sql 2012
SELECT [id], [grantee], [grantor], [actadd], [actmod], [seladd], [selmod], [updadd], [updmod], [refadd], [refmod] FROM sys.syspermissions

Back to Top


sys.sysprocesses

Contains information about processes that are running on an instance of SQL Server. These processes can be client processes or system processes. To access sysprocesses, you must be in the master database context, or you must use the master.dbo.sysprocesses three-part name. Important This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
spid smallint SQL Server session ID.
kpid smallint Microsoft Windows thread ID.
blocked smallint ID of the session that is blocking the request. If this column is NULL, the request is not blocked, or the session information of the blocking session is not available (or cannot be identified).
-2 = The blocking resource is owned by an orphaned distributed transaction.
-3 = The blocking resource is owned by a deferred recovery transaction.
-4 = Session ID of the blocking latch owner could not be determined due to internal latch state transitions.
waittype binary(2) Reserved.
waittime bigint Current wait time in milliseconds.
0 = Process is not waiting.
lastwaittype nchar(32) A string indicating the name of the last or current wait type.
waitresource nchar(32) Textual representation of a lock resource.
dbid smallint ID of the database currently being used by the process.
uid smallint ID of the user that executed the command. Overflows or returns NULL if the number of users and roles exceeds 32,767. For more information, see Querying the SQL Server System Catalog2.
cpu int Cumulative CPU time for the process. The entry is updated for all processes, regardless of whether the SET STATISTICS TIME option is ON or OFF.
physical_io bigint Cumulative disk reads and writes for the process.
memusage int Number of pages in the procedure cache that are currently allocated to this process. A negative number indicates that the process is freeing memory allocated by another process.
login_time datetime Time at which a client process logged into the server. For system processes, the time at which the SQL Server startup occurred is stored.
last_batch datetime Last time a client process executed a remote stored procedure call or an EXECUTE statement. For system processes, the time at which the SQL Server startup occurred is stored.
ecid smallint Execution context ID used to uniquely identify the subthreads operating on behalf of a single process.
open_tran smallint Number of open transactions for the process.
status nchar(30) Process ID status. The possible values are:
dormant = SQL Server is resetting the session.
running = The session is running one or more batches. When Multiple Active Result Sets (MARS) is enabled, a session can run multiple batches. For more information, see Using Multiple Active Result Sets (MARS)3.
background = The session is running a background task, such as deadlock detection.
rollback = The session has a transaction rollback in process.
pending = The session is waiting for a worker thread to become available.
runnable = The task in the session is in the runnable queue of a scheduler while waiting to get a time quantum.
spinloop = The task in the session is waiting for a spinlock to become free.
suspended = The session is waiting for an event, such as I/O, to complete.
sid binary(86) Globally unique identifier (GUID) for the user.
hostname nchar(128) Name of the workstation.
program_name nchar(128) Name of the application program.
hostprocess nchar(10) Workstation process ID number.
cmd nchar(16) Command currently being executed.
nt_domain nchar(128) Microsoft Windows domain for the client, if using Windows Authentication, or a trusted connection.
nt_username nchar(128) Windows user name for the process, if using Windows Authentication, or a trusted connection.
net_address nchar(12) Assigned unique identifier for the network adapter on the workstation of each user. When a user logs in, this identifier is inserted in the net_address column.
net_library nchar(12) Column in which the client's network library is stored. Every client process comes in on a network connection. Network connections have a network library associated with them that enables them to make the connection. For more information, see Network Protocols, and TDS Endpoints4.
loginame nchar(128) Login name.
context_info binary(128) Data stored in a batch by using the SET CONTEXT_INFO statement.
sql_handle binary(20) Represents the currently executing batch or object.
Note This value is derived from the batch or memory address of the object. This value is not calculated by using the SQL Server 2005 hash-based algorithm.
stmt_start int Starting offset of the current SQL statement for the specified sql_handle.
stmt_end int Ending offset of the current SQL statement for the specified sql_handle.
-1 = Current statement runs to the end of the results returned by the fn_get_sql function for the specified sql_handle.
request_id int ID of request. Used to identify requests running in a specific session.

TSQL

Sql 2005
SELECT [spid], [kpid], [blocked], [waittype], [waittime], [lastwaittype], [waitresource], [dbid], [uid], [cpu], [physical_io], [memusage], [login_time], [last_batch], [ecid], [open_tran], [status], [sid], [hostname], [program_name], [hostprocess], [cmd], [nt_domain], [nt_username], [net_address], [net_library], [loginame], [context_info], [sql_handle], [stmt_start], [stmt_end], [request_id] FROM sys.sysprocesses
Sql 2008
SELECT [spid], [kpid], [blocked], [waittype], [waittime], [lastwaittype], [waitresource], [dbid], [uid], [cpu], [physical_io], [memusage], [login_time], [last_batch], [ecid], [open_tran], [status], [sid], [hostname], [program_name], [hostprocess], [cmd], [nt_domain], [nt_username], [net_address], [net_library], [loginame], [context_info], [sql_handle], [stmt_start], [stmt_end], [request_id] FROM sys.sysprocesses
Sql 2008 R2
SELECT [spid], [kpid], [blocked], [waittype], [waittime], [lastwaittype], [waitresource], [dbid], [uid], [cpu], [physical_io], [memusage], [login_time], [last_batch], [ecid], [open_tran], [status], [sid], [hostname], [program_name], [hostprocess], [cmd], [nt_domain], [nt_username], [net_address], [net_library], [loginame], [context_info], [sql_handle], [stmt_start], [stmt_end], [request_id] FROM sys.sysprocesses
Sql 2012
SELECT [spid], [kpid], [blocked], [waittype], [waittime], [lastwaittype], [waitresource], [dbid], [uid], [cpu], [physical_io], [memusage], [login_time], [last_batch], [ecid], [open_tran], [status], [sid], [hostname], [program_name], [hostprocess], [cmd], [nt_domain], [nt_username], [net_address], [net_library], [loginame], [context_info], [sql_handle], [stmt_start], [stmt_end], [request_id] FROM sys.sysprocesses

Back to Top


sys.sysprotects

Contains information about permissions that have been applied to security accounts in the database by using the GRANT and DENY statements. Important This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
id int ID of the object to which these permissions apply.
uid smallint ID of user or group to which these permissions apply. Overflows or returns NULL if the number of users and roles exceeds 32,767. For more information, see Querying the SQL Server System Catalog2.
action tinyint Can have one of the following permissions:
26 = REFERENCES
178 = CREATE FUNCTION
193 = SELECT
195 = INSERT
196 = DELETE
197 = UPDATE
198 = CREATE TABLE
203 = CREATE DATABASE
207 = CREATE VIEW
222 = CREATE PROCEDURE
224 = EXECUTE
228 = BACKUP DATABASE
233 = CREATE DEFAULT
235 = BACKUP LOG
236 = CREATE RULE
protecttype tinyint Can have the following values:
204 = GRANT_W_GRANT
205 = GRANT
206 = DENY
columns varbinary(8000) Bitmap of columns to which these SELECT or UPDATE permissions apply.
Bit 0 = All columns.
Bit 1 = Permissions apply to that column.
NULL = No information.
grantor smallint User ID of the user that issued the GRANT or DENY permissions. Overflows or returns NULL if the number of users and roles exceeds 32,767. For more information, see Querying the SQL Server System Catalog2.

TSQL

Sql 2005
SELECT [id], [uid], [action], [protecttype], [columns], [grantor] FROM sys.sysprotects
Sql 2008
SELECT [id], [uid], [action], [protecttype], [columns], [grantor] FROM sys.sysprotects
Sql 2008 R2
SELECT [id], [uid], [action], [protecttype], [columns], [grantor] FROM sys.sysprotects
Sql 2012
SELECT [id], [uid], [action], [protecttype], [columns], [grantor] FROM sys.sysprotects

Back to Top


sys.sysreferences

Contains mappings of the FOREIGN KEY constraint definitions to the referenced columns within the database. Important This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
constid int ID of the FOREIGN KEY constraint.
fkeyid int ID of the referencing table.
rkeyid int ID of the referenced table.
rkeyindid smallint Index ID of the unique index on the referenced table covering the referenced key-columns.
keycnt smallint Number of columns in the key.
forkeys varbinary(32) Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
refkeys varbinary(32) Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
fkeydbid smallint Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
rkeydbid smallint Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
fkey1 smallint Column ID of the referencing column.
fkey2 smallint Column ID of the referencing column.
fkey3 smallint Column ID of the referencing column.
fkey4 smallint Column ID of the referencing column.
fkey5 smallint Column ID of the referencing column.
fkey6 smallint Column ID of the referencing column.
fkey7 smallint Column ID of the referencing column.
fkey8 smallint Column ID of the referencing column.
fkey9 smallint Column ID of the referencing column.
fkey10 smallint Column ID of the referencing column.
fkey11 smallint Column ID of the referencing column.
fkey12 smallint Column ID of the referencing column.
fkey13 smallint Column ID of the referencing column.
fkey14 smallint Column ID of the referencing column.
fkey15 smallint Column ID of the referencing column.
fkey16 smallint Column ID of the referencing column.
rkey1 smallint Column ID of the referenced column.
rkey2 smallint Column ID of the referenced column.
rkey3 smallint Column ID of the referenced column.
rkey4 smallint Column ID of the referenced column.
rkey5 smallint Column ID of the referenced column.
rkey6 smallint Column ID of the referenced column.
rkey7 smallint Column ID of the referenced column.
rkey8 smallint Column ID of the referenced column.
rkey9 smallint Column ID of the referenced column.
rkey10 smallint Column ID of the referenced column.
rkey11 smallint Column ID of the referenced column.
rkey12 smallint Column ID of the referenced column.
rkey13 smallint Column ID of the referenced column.
rkey14 smallint Column ID of the referenced column.
rkey15 smallint Column ID of the referenced column.
rkey16 smallint Column ID of the referenced column.

TSQL

Sql 2005
SELECT [constid], [fkeyid], [rkeyid], [rkeyindid], [keycnt], [forkeys], [refkeys], [fkeydbid], [rkeydbid], [fkey1], [fkey2], [fkey3], [fkey4], [fkey5], [fkey6], [fkey7], [fkey8], [fkey9], [fkey10], [fkey11], [fkey12], [fkey13], [fkey14], [fkey15], [fkey16], [rkey1], [rkey2], [rkey3], [rkey4], [rkey5], [rkey6], [rkey7], [rkey8], [rkey9], [rkey10], [rkey11], [rkey12], [rkey13], [rkey14], [rkey15], [rkey16] FROM sys.sysreferences
Sql 2008
SELECT [constid], [fkeyid], [rkeyid], [rkeyindid], [keycnt], [forkeys], [refkeys], [fkeydbid], [rkeydbid], [fkey1], [fkey2], [fkey3], [fkey4], [fkey5], [fkey6], [fkey7], [fkey8], [fkey9], [fkey10], [fkey11], [fkey12], [fkey13], [fkey14], [fkey15], [fkey16], [rkey1], [rkey2], [rkey3], [rkey4], [rkey5], [rkey6], [rkey7], [rkey8], [rkey9], [rkey10], [rkey11], [rkey12], [rkey13], [rkey14], [rkey15], [rkey16] FROM sys.sysreferences
Sql 2008 R2
SELECT [constid], [fkeyid], [rkeyid], [rkeyindid], [keycnt], [forkeys], [refkeys], [fkeydbid], [rkeydbid], [fkey1], [fkey2], [fkey3], [fkey4], [fkey5], [fkey6], [fkey7], [fkey8], [fkey9], [fkey10], [fkey11], [fkey12], [fkey13], [fkey14], [fkey15], [fkey16], [rkey1], [rkey2], [rkey3], [rkey4], [rkey5], [rkey6], [rkey7], [rkey8], [rkey9], [rkey10], [rkey11], [rkey12], [rkey13], [rkey14], [rkey15], [rkey16] FROM sys.sysreferences
Sql 2012
SELECT [constid], [fkeyid], [rkeyid], [rkeyindid], [keycnt], [forkeys], [refkeys], [fkeydbid], [rkeydbid], [fkey1], [fkey2], [fkey3], [fkey4], [fkey5], [fkey6], [fkey7], [fkey8], [fkey9], [fkey10], [fkey11], [fkey12], [fkey13], [fkey14], [fkey15], [fkey16], [rkey1], [rkey2], [rkey3], [rkey4], [rkey5], [rkey6], [rkey7], [rkey8], [rkey9], [rkey10], [rkey11], [rkey12], [rkey13], [rkey14], [rkey15], [rkey16] FROM sys.sysreferences

Back to Top


sys.sysremote_logins

Contains one row for each remote user that is permitted to call remote stored procedures on an instance of Microsoft SQL Server. Important This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
remoteserverid smallint Remote server identification.
remoteusername sysname Login name of the user on a remote server.
status smallint Returns 0.
sid varbinary(85) Microsoft Windows user security ID.
changedate datetime Date and time the remote user was added.

TSQL

Sql 2005
SELECT [remoteserverid], [remoteusername], [status], [sid], [changedate] FROM sys.sysremote_logins
Sql 2008
SELECT [remoteserverid], [remoteusername], [status], [sid], [changedate] FROM sys.sysremote_logins
Sql 2008 R2
SELECT [remoteserverid], [remoteusername], [status], [sid], [changedate] FROM sys.sysremote_logins
Sql 2012
SELECT [remoteserverid], [remoteusername], [status], [sid], [changedate] FROM sys.sysremote_logins

Back to Top


sys.sysservers

Contains one row for each server that an instance of SQL Server can access as an OLE DB data source. Important This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
srvid smallint ID (for local use only) of the remote server.
srvstatus smallint Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
srvname sysname Name of the server.
srvproduct sysname Product name for the remote server.
providername sysname OLE DB provider name for access to this server.
datasource nvarchar(4000) OLE DB data source value.
location nvarchar(4000) OLE DB location value.
providerstring nvarchar(4000) OLE DB provider string value.
schemadate datetime Date this row was last updated.
topologyx int Not used.
topologyy int Not used.
catalog sysname Catalog that is used when a connection is made to an OLE DB provider.
connecttimeout int Time-out setting for the server-connection.
querytimeout int Time-out setting for queries against the server.
srvnetname char(30) Identified for informational purposes only. Not supported. Future compatibility is not guaranteed. .
isremote bit 1 = Server is a remote server.
0 = Server is a linked server.
rpc bit 1 = sp_serveroption @rpc set to true or on.
0 = sp_serveroption @rpc set to false or off.
pub bit 1 = sp_serveroption @pub set to true or on.
0 = sp_serveroption @pub set to false or off.
sub bit 1 = sp_serveroption @sub set to true or on.
0 = sp_serveroption @sub set to false or off.
dist bit 1 = sp_serveroption @dist set to true or on.
0 = sp_serveroption @dist set to false or off.
dpub bit 1 = sp_serveroption @dpub set to true or on.
0 = sp_serveroption @dpub set to false or off.
rpcout bit 1 = sp_serveroption @rpc out set to true or on.
0 = sp_serveroption @rpc out set to false or off.
dataaccess bit 1 = sp_serveroption @data access set to true or on.
0 = sp_serveroption @data access set to false or off.
collationcompatible bit 1 = sp_serveroption @collation compatible set to true or on.
0 = sp_serveroption @collation compatible set to false or off.
system bit 1 = sp_serveroption @system set to true or on.
0 = sp_serveroption @system set to false or off.
useremotecollation bit 1 = sp_serveroption @remote collation set to true or on.
0 = sp_serveroption @remote collation set to false or off.
lazyschemavalidation bit 1 = sp_serveroption @lazy schema validation set to true or on.
0 = sp_serveroption @lazy schema validation set to false or off.
collation sysname Server collation as set by sp_serveroption @collation name.
srvcollation     sysname The collation of the server.
nonsqlsub     bit 0 = server is an instance of SQL Server
1 = server is not an instance of SQL Server

TSQL

Sql 2005
SELECT [srvid], [srvstatus], [srvname], [srvproduct], [providername], [datasource], [location], [providerstring], [schemadate], [topologyx], [topologyy], [catalog], [connecttimeout], [querytimeout], [srvnetname], [isremote], [rpc], [pub], [sub], [dist], [dpub], [rpcout], [dataaccess], [collationcompatible], [system], [useremotecollation], [lazyschemavalidation], [collation] FROM sys.sysservers
Sql 2008
SELECT [srvid], [srvstatus], [srvname], [srvproduct], [providername], [datasource], [location], [providerstring], [schemadate], [topologyx], [topologyy], [catalog], [connecttimeout], [querytimeout], [srvnetname], [isremote], [rpc], [pub], [sub], [dist], [dpub], [rpcout], [dataaccess], [collationcompatible], [system], [useremotecollation], [lazyschemavalidation], [collation] FROM sys.sysservers
Sql 2008 R2
SELECT [srvid], [srvstatus], [srvname], [srvproduct], [providername], [datasource], [location], [providerstring], [schemadate], [topologyx], [topologyy], [catalog], [srvcollation], [connecttimeout], [querytimeout], [srvnetname], [isremote], [rpc], [pub], [sub], [dist], [dpub], [rpcout], [dataaccess], [collationcompatible], [system], [useremotecollation], [lazyschemavalidation], [collation], [nonsqlsub] FROM sys.sysservers
Sql 2012
SELECT [srvid], [srvstatus], [srvname], [srvproduct], [providername], [datasource], [location], [providerstring], [schemadate], [topologyx], [topologyy], [catalog], [srvcollation], [connecttimeout], [querytimeout], [srvnetname], [isremote], [rpc], [pub], [sub], [dist], [dpub], [rpcout], [dataaccess], [collationcompatible], [system], [useremotecollation], [lazyschemavalidation], [collation], [nonsqlsub] FROM sys.sysservers

Back to Top


sys.systypes

system-supplied and each user-defined data type defined in the database. Important This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
name sysname Data type name.
xtype tinyint Physical storage type.
status tinyint Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
xusertype smallint Extended user type. Overflows or returns NULL if the number of data types exceeds 32,767. For more information, see Querying the SQL Server System Catalog2.
length smallint Physical length of the data type.
xprec tinyint Internal precision, as used by the server. Not to be used in queries.
xscale tinyint Internal scale, as used by the server. Not to be used in queries.
tdefault int ID of the stored procedure that contains integrity checks for this data type.
domain int ID of the stored procedure that contains integrity checks for this data type.
uid smallint Schema ID of the owner of the type.
For databases upgraded from an earlier version of SQL Server, the schema ID is equal to the user ID of the owner.
Important: If you use any of the following SQL Server 2005 DDL statements, you must use the sys.types3 catalog view instead of sys.systypes. ALTER AUTHORIZATION ON TYPE CREATE TYPE

Overflows or returns NULL if the number of users and roles exceeds 32,767. For more information, see Querying the SQL Server System Catalog2.
reserved smallint Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
usertype smallint User type ID. Overflows or returns NULL if the number of data types exceeds 32,767. For more information, see Querying the SQL Server System Catalog2.
variable bit Variable-length data type.
1 = True
0 = False
allownulls bit Indicates the default nullability for this data type. This default value is overridden by if nullability is specified by using CREATE TABLE4 or ALTER TABLE5.
type tinyint Physical storage data type.
printfmt varchar(255) Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
prec smallint Level of precision for this data type.
-1 = xml or large value types.
scale tinyint Scale for this data type, based on precision.
NULL = Data type is nonnumeric.
collation sysname If character based, collation is the collation of the current database; otherwise, it is NULL.
collationid     int If character based, collationid is the id of the collation of the current database; otherwise, it is NULL.

TSQL

Sql 2005
SELECT [name], [xtype], [status], [xusertype], [length], [xprec], [xscale], [tdefault], [domain], [uid], [reserved], [usertype], [variable], [allownulls], [type], [printfmt], [prec], [scale], [collation] FROM sys.systypes
Sql 2008
SELECT [name], [xtype], [status], [xusertype], [length], [xprec], [xscale], [tdefault], [domain], [uid], [reserved], [usertype], [variable], [allownulls], [type], [printfmt], [prec], [scale], [collation] FROM sys.systypes
Sql 2008 R2
SELECT [name], [xtype], [status], [xusertype], [length], [xprec], [xscale], [tdefault], [domain], [uid], [reserved], [collationid], [usertype], [variable], [allownulls], [type], [printfmt], [prec], [scale], [collation] FROM sys.systypes
Sql 2012
SELECT [name], [xtype], [status], [xusertype], [length], [xprec], [xscale], [tdefault], [domain], [uid], [reserved], [collationid], [usertype], [variable], [allownulls], [type], [printfmt], [prec], [scale], [collation] FROM sys.systypes

Back to Top


sys.sysusers

Contains one row for each Microsoft Windows user, Windows group, Microsoft SQL Server user, or SQL Server role in the database. Important This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
uid smallint User ID, unique in this database.
1 = Database owner
Overflows or returns NULL if the number of users and roles exceeds 32,767. For more information, see Querying the SQL Server System Catalog2.
status smallint Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
name sysname User name or group name, unique in this database.
sid varbinary(85) Security identifier for this entry.
roles varbinary(2048) Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
createdate datetime Date the account was added.
updatedate datetime Date the account was last changed.
altuid smallint Identified for informational purposes only. Not supported. Future compatibility is not guaranteed. Overflows or returns NULL if the number of users and roles exceeds 32,767. For more information, see Querying the SQL Server System Catalog2.
password varbinary(256) Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
gid smallint Group ID to which this user belongs. If uid is the same as gid, this entry defines a group. Overflows or returns NULL if the combined number of groups and users exceeds 32,767. For more information, see Querying the SQL Server System Catalog2.
environ varchar(255) Reserved.
hasdbaccess int 1 = Account has database access.
islogin int 1 = Account is a Windows group, Windows user, or SQL Server user with a login account.
isntname int 1 = Account is a Windows group or Windows user.
isntgroup int 1 = Account is a Windows group.
isntuser int 1 = Account is a Windows user.
issqluser int 1 = Account is a SQL Server user.
isaliased int 1 = Account is aliased to another user.
issqlrole int 1 = Account is a SQL Server role.
isapprole int 1 = Account is an application role.

TSQL

Sql 2005
SELECT [uid], [status], [name], [sid], [roles], [createdate], [updatedate], [altuid], [password], [gid], [environ], [hasdbaccess], [islogin], [isntname], [isntgroup], [isntuser], [issqluser], [isaliased], [issqlrole], [isapprole] FROM sys.sysusers
Sql 2008
SELECT [uid], [status], [name], [sid], [roles], [createdate], [updatedate], [altuid], [password], [gid], [environ], [hasdbaccess], [islogin], [isntname], [isntgroup], [isntuser], [issqluser], [isaliased], [issqlrole], [isapprole] FROM sys.sysusers
Sql 2008 R2
SELECT [uid], [status], [name], [sid], [roles], [createdate], [updatedate], [altuid], [password], [gid], [environ], [hasdbaccess], [islogin], [isntname], [isntgroup], [isntuser], [issqluser], [isaliased], [issqlrole], [isapprole] FROM sys.sysusers
Sql 2012
SELECT [uid], [status], [name], [sid], [roles], [createdate], [updatedate], [altuid], [password], [gid], [environ], [hasdbaccess], [islogin], [isntname], [isntgroup], [isntuser], [issqluser], [isaliased], [issqlrole], [isapprole] FROM sys.sysusers

Back to Top

No comments:

Post a Comment

Total Pageviews