Compatibility Views

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

• sys.sysalt_files
• sys.syscache_objects
• sys.syscharsets
• sys.syscolumns
• sys.syscomments
• sys.sysconfigures
• sys.sysconstraints
• sys.syscur_configs
• sys.sysdatabases
• sys.sysdepends
• sys.sysdevices
• sys.sysfile_groups
• sys.sysfiles
• sys.sysforeign_keys
• sys.sysfulltext_catalogs
• sys.sysindexes
• sys.sysindexkeys
• sys.syslanguages
• sys.syslock_info
• sys.syslogins
• sys.sysmembers
• sys.sysmessages
• sys.sysobjects
• sys.sysoledbusers
• sys.sysperf_info
• sys.syspermissions
• sys.sysprocesses
• sys.sysprotects
• sys.sysreferences
• sys.sysremote_logins
• sys.sysservers
• sys.systypes
• sys.sysusers

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

---