[Source] Main system table descriptions in sqlserver2008

sysaltfiles primary database holds the files of the database
syscharsets main database character set and sort order
sysco nfigures primary database configuration options

Syscurco nfigs primary database current configuration options

  sysdatabases database in the primary database server

  syslanguages is the main database language

  syslogins main database Login account information

sysoledbusers main database links server login information

  sysprocesses primary database process

  sysremotelogins Primary database Login to the account remotely

  syscolumns per database column

  sysco nstrains per database limit

  sysfilegroups per database filegroup

  sysfiles for each database file

  sysforeignkeys for each database external keyword

  sysindexes per database index

  sysmenbers for each database role member

  sysobjects for each database

syscomments database object

  syspermissio ns Per database permissions

  systypes per database user-defined data types

  sysusers per database user

sysaltfiles header
In special cases, contain the rows corresponding to the files in the database. The table is stored in the master database.

The column name Data Type description
fileid smallint A unique file identification number for each database.
groupid smallint file group identification number.
size int file size (in 8 KB pages).
maxsize int The maximum file size (in 8 KB pages). A value of 0 means no growth, and a value of –1 means that the file should always be

Grow to disk full.
growth int The growth size of the database. A value of 0 indicates no growth. Depending on the value of the status, it can be hundreds of pages or file size

Divide the ratio. If status is 0x100000, growth is a percentage of the file size; Otherwise it is the number of pages.  
status int for internal use only.
perf int retained.
dbid smallint The database identification number of the database to which the file belongs.
name nchar(128) The logical name of the file.
filename nchar(260) The name of the physical device, including the full path to the file.

> syscharsets top page

Each character set occupies a row in the table, which also contains definitions for Microsoft? SQL Server? The sort order used. row

One of the sequence sequences is marked as the default sort order in sysco nfigures, which is the only order actually used.

The column name Data Type description
type smallint The type of entity represented by this line. 1001 is the character set; 2001 is the sorting order.
id tinyint character set or unique ID of the sort order. Note that the sort order and character set cannot share the same ID number. Guaranteed

Leave the ID range from 1 to 240 for SQL Server to use.
csid tinyint field is not used if the line represents a character set. If the row represents the sort order, the field is in

On top of it, the character set ID of the sort order is generated. Suppose a character set row with that ID exists in that table.
status smallint The internal system status information bit.
name sysname The unique name of the character set or sort order. The field must contain only the letters A-Z or a-z, the number 0 –

9 and underscore (_). It must start with a letter.
descriptio n nvarchar(255) character set or optional description of the sort order function.
BinaryDefinitio N Varbinary(255) for internal use only.
Definitio n image character set or internal definition of sort order. The data structure in this field depends on the type.

Sysco nfigures top of page

Each configuration option set by the user occupies a row in the table. sysco nfigures include recently launched Microsoft? SQL

Server? The previously defined configuration options also contain all the dynamic configuration options set after the recent launch. The table is located only in master

database.

The column name Data Type description
value int variable (used by SQL Server only after RECo nFIGURE has been executed).
co nfig smallint configuration variable number.
comment nvarchar(255) on configuration options.
status smallint represents a bitmap of the status of an option. Possible values include:
0 = static (this setting takes effect when the server restarts).
1 = Dynamic (this variable takes effect when the RECo nFIGURE statement is executed).
2 = Advanced (the variable is displayed only if the Show Advanced option is set).
3 = Dynamic and Advanced.

Syscurco nfigs top of page

Each current configuration option is one for each one. In addition, the table contains four items that describe the configuration structure. Syscurco NFIGS is in use

Generated dynamically when querying. For more information, see Sysco nfigures.

The column name Data Type description
value int user-modifiable variable value (only if RECo nFIGURE has been executed by Microsoft?) SQL

Server? use).
co nfig smallint configuration variable number.
comment nvarchar(255) on configuration options.
status smallint represents a bitmap of the status of an option. Possible values include:
0 = static (this setting takes effect when the server restarts).
1 = Dynamic (this variable takes effect when the RECo nFIGURE statement is executed).
2 = Advanced (the variable is displayed only if the Show Advanced option is set).
3 = Dynamic and Advanced.

sysdatabases page

Microsoft? SQL Server? Each database on the table occupies a row. When you initially install SQL Server,

sysdatabases contains items for master, model, msdb, mssqlweb, and tempdb databases. The table only exists

stored in the master database.

The column name Data Type description
name sysname The name of the database.
dbid smallint database ID.
sid varbinary(85) The system ID of the database creator.
mode smallint is used to lock the database internally when it is created.
status int status bits, some of which can be used by the user sp_dboptio n (read o nly, dbo use).

o nly, single user, etc.):
1 = autoclose ； Use the sp_dboptio n settings.
4 = select into/bulkcopy ； Use the sp_dboptio n settings.
8 = trunc. log o n chkpt ； Use the sp_dboptio n settings.
16 = torn page detectio n, using sp_dboptio n setting.
32 = loading 。
64 = pre recovery 。
128 = recovering 。
256 = not recovered 。
512 = offline ； Use the sp_dboptio n settings.
1024 = read o nly ； Use the sp_dboptio n settings.
2048 = dbo use o nly ； use
sp_dboptio n settings.
4096 = single user ； Use the sp_dboptio n settings.
32768 = emergency mode 。
4194304 = autoshrink 。
1073741824 = cleanly shutdown 。

Multiple bits can be opened at the same time.

status2  int  16384 = ANSI null default ； Use the sp_dboptio n settings.
65536 = co ncat null yields null with sp_dboptio n setting.
131072 = recursive triggers, using sp_dboptio n settings.
1048576 = default to local cursor, using sp_dboptio n setting.
8388608 = quoted identifier, used
sp_dboptio n settings.
33554432 = cursor close o n commit, using sp_dboptio n setting.
67108864 = ANSI nulls, using the sp_dboptio n setting.
268435456 = ANSI warnings, using sp_dboptio n setting.
536870912 = full text enabled, use
sp_fulltext_database Settings.
crdate datetime created date.
reserved datetime.
category int contains bitmaps of information for copying:
1 = Published.
2 = subscribed.
4 = Merge published.
8 = Merge Subscribed.

CMPTLEVEL TinyINT database. See sp_dbcmptlevel for more information.  
filename nvarchar(260) The operating system path and name of the database master file.
The build number of the SQL Server code used to create the database. SQL Server tools only

Internally for upgrade processing.

syslanguages header

Appearing at Microsoft? SQL Server? Each language in the table has a row. Although American English is not there

syslanguages, but the table is always available to SQL Server. The table is stored only in the master database.

The column name Data Type description
langid smallint unique language ID.
dateformat nchar(3) date order (e.g. DMY).
datefirst tinyint The first day of the week: 1 for Monday, 2 for Tuesday, and so on until 7 means star

Date.
upgrade int for system use.
name sysname official language name (e.g., fran?ais).
alias sysname Alternate language name (such as French).
mo nths nvarchar(372) A comma-separated list of full names for months in order from January to December, each name

Contains a maximum of 20 characters.
shortmo nths varchar(132) a comma-separated list of abbreviated month names in order from January to December,

Each name can contain up to 9 characters.
days nvarchar(217) A comma-separated list of day names in order from January to December, with the most of each name

Contains 30 characters.
lcid int for this language Microsoft Windows NT? Locale ID.
mslangid smallint SQL Server message group ID.

Thirty-three languages provided by SQL Server are installed. Below is a list of languages.

The name in English is NT LCID SQL Server Message Group ID
English 1033 1033
German 1031 1031
French 1036 1036
Japanese 1041 1041
Danish 1030 1030
Spanish 3082 3082
Italian 1040 1040
Dutch 1043 1043
Norwegian 2068 2068
Portuguese 2070 2070
Finnish 1035 1035
Swedish 1053 1053
Czech 1029 1029
Hungarian 1038 1038
Polish 1045 1045
Romanian 1048 1048
Croatian 1050 1050
Slovak 1051 1051
Slovene 1060 1060
Greek 1032 1032
Bulgarian 1026 1026
Russian 1049 1049
Turkish 1055 1055
British English 2057 1033
Esto nian 1061 1061
Latvian 1062 1062
Lithuanian 1063 1063
Brazilian 1046 1046
Traditio nal Chinese 1028 1028
Korean 1042 1042
Simplified Chinese 2052 2052
Arabic 1025 1025
Thai 1054 1054

syslogins header

Each login account occupies a row in the table.

The column name Data Type description
sid varbinary(85) security identifier.
status smallint for internal use only.
CreateDate DateTime adds the date of login.
updateDate DateTime Update the date of the login.
accdate datetime for internal use only.
totcpu int for internal use only.
Totio int is for internal use only.
spacelimit int for internal use only.
Timelimit int for internal use only.
ResultLimit int for internal use only.
name varchar(30) The login ID of the user.
dbname nvarchar(128) The default database name of the user when establishing a connection.
password nvarchar(128) The user's encrypted password (can be NULL).
language nvarchar(128) The default language for users.
denylogin int if sign-in is Microsoft? Windows NT? 1 for a user or group that has been denied access.
hasaccess int is 1 if login access is authorized.
isntname int is 1 if the sign-in is a Windows NT user or group, and if the sign-in is Microsoft SQL

Server? Login, it is 0.
isntgroup int if logged in

If it is a Windows NT group, it is 1.
isntuser int is 1 if the sign-in is a Windows NT user.
sysadmin int is 1 if the login is a sysadmin server role member.
securityadmin int is 1 if the login is a member of the securityadmin server role.
serveradmin int is 1 if the login is a serveradmin fixed server role member.
setupadmin int is 1 if the login is a setupadmin fixed server role member.
processadmin int is 1 if the login is a processadmin fixed server role member.
diskadmin int is 1 if the login is a diskadmin fixed server role member.
dbcreator int is 1 if the login is a dbcreator fixed server role member.
loginname nvarchar(128) The actual name of the login, which may be different from the login used by SQL Server

。

sysoledbusers header

The user and password mapping for each specified linked server occupies a row in the table. The table is stored in the master database.

The column name Data Type description
The SID (Security Identification Number) of the rmtsrvid smallint server.
rmtloginame nvarchar(128) loginsid maps to the remote login of the linked rmtservid.
rmtpassword nvarchar(128) links the encrypted password for the specified remote login within rmtsrvid.
loginsid varbinary(85) The local login SID to be mapped.
status smallint If the value is 1, the mapping should use the user's own credentials.
ChangeDateDateTime The date the mapping information was last changed.

sysprocesses top page

sysprocesses table about running on Microsoft? SQL Server? information about the process on the These processes can be:

Client process or system process. sysprocesses are stored only in the master database.

The column name Data Type description
spid smallint SQL Server process ID.
kpid  smallint  Microsoft Windows NT 4.0? Thread ID.
blocked smallint The process ID (spid) of the blocked process.
waittype binary(2) reservation.
waittime int The current wait time in milliseconds. 0 when the process is not waiting.
lastwaittype nchar(32) indicates the string of the last or current waiting type name.
waitresource nchar(32) A textual representation of a lock resource.
dbid smallint is currently used by the process.
uid smallint The user ID that executed the command.
The cumulative CPU time of the CPU int process. It doesn't matter if the SET STATISTICS TIME o n option is o n or OFF

There is a process to update the entry.
physical_io cumulative disk reads and writes for int processes.
Memusage int The number of pages in the process cache that is currently allocated to that process. A negative number indicates that the process is being released by another

The memory allocated by a process.
login_time datetime The time when the client process logged in to the server. For system processes, it is SQL Server storage

The time when the startup occurred.
last_batch datetime The time when the client process last executed a remote stored procedure call or EXECUTE statement. For the department

The system process is the time when SQL Server starts to be stored.
ecid smallint is used to uniquely identify the execution context ID of a child thread that operates on behalf of a single process.
open_tran The number of open transactions for the smallint process.
status nchar(30) The process ID status (e.g., running, hibernating, etc.).
sid binary(85) The user's globally unique identifier (GUID).
hostname nchar(128) The name of the workstation.
program_name nchar(128) the name of the application.
hostprocess nchar(8) workstation process ID number.
cmd nchar(16) command that is currently being executed.
nt_domain nchar(128) client's Windows NT 4.0 domain (if using Windows Authentication) or trust

Connected Windows NT 4.0 domain.
nt_username nchar(128) The Windows NT 4.0 username (if using Windows authentication) or letter for the process

Any connected Windows NT 4.0 username.
net_address nchar(12) is assigned to a unique identifier for the network interface card on each user workstation. When a user logs in,

The identifier is inserted into the net_address column.
net_library nchar(12) column used to store the client network library. Each client process enters on a network connection.

Network connections have a network library associated with these processes that allows these processes to establish connections. For more information

, see Client and Server Net-Library.
loginame nchar(128) login.

sysremotelogins to the top of the page

Each is allowed to call Microsoft? SQL Server? The remote user of the remote stored procedure on the

The column name Data Type description
remoteserverid smallint The remote server identity.
remoteusername nvarchar(128) The user login on the remote server.
status smallint option.
sid  varbinary(85)  Microsoft Windows NT? User Security ID.
ChangeDate DateTime adds the date and time of the remote user.

syscolumns header

Each column in each table and view occupies a row in the table, and each parameter in the stored process also occupies a row in the table. The table is located at each number

According to the warehouse.

The column name Data Type description
name sysname The name of the column name or procedure parameter.
id int The table object ID to which the column belongs, or the stored procedure ID associated with the parameter.
xtype Tinyint systypes.
typestat tinyint for internal use only.
xusertype smallint extension.
length smallint The maximum physical storage length in systypes.
Xprec Tinyint is for internal use only.
Xscale Tinyint is for internal use only.
colid smallint column or parameter ID.
xoffset smallint for internal use only.
BitPOS Tinyint is for internal use only.
Reserved Tinyint for internal use only.
colstat smallint for internal use only.
cdefault int The default value ID of the column.
domain int the rule or CHECK constraint ID of the column.
number smallint The subprocedure number when the procedure is grouped (0 indicates a non-process item).
colorder smallint for internal use only.
Autoval Varbinary(255) for internal use only.
offset smallint The offset of the row in which the column is located; If negative, it means a variable length row.
status tinyint is a bitmap used to describe column or parameter properties:
0x08 = Column allows null values.
0x10 = ANSI padding takes effect when varchar or varbinary columns are added. Keep the trailing space of the varchar column, keep

Leave the trailing zero in the varbinary column.
0x40 = parameter is the OUTPUT parameter.
0x80 = Column as the identity column.

type tinyint The type of physical storage in systypes.
usertype smallint systype.
printfmt varchar(255) for internal use only.
prec smallint The precision level of the column.
scale int The number of decimal places in the column.
iscomputed int indicates whether the flag for that column has been calculated:
0 = Not calculated.
1 = Calculated.

isoutparam int indicates whether the process parameter is an output parameter:
1 = true.
0 = false.

isnullable int indicates whether the column allows null values:
1 = true.
0 = false.

Sysco nstraints top of page

Contains a constraint map, mapped to the object that owns the constraint. This system directory is stored in each database.

The column name Data Type description
co nstid int constraint number.
id int has the table ID of that constraint.
colid smallint defines the column ID of the constraint on it, or 0 if it is a table constraint.
spare1 tinyint retained.
status int bitmap indicates status. Possible values include:
1 = PRIMARY KEY constraint.
2 = UNIQUE KEY constraints.
3 = FOREIGN KEY constraints.
4 = CHECK constraint.
5 = DEFAULT constraint.
16 = column-level constraints.
32 = table-level constraints.

actio ns int retained.
error int remains.

sysfilegroups header

Each file group in the database occupies a row in the table. The table is stored in each database. At least one item in the table is used for the main text

Piece set.

The column name Data Type description
groupid smallint A unique group identification number for each database.
allocpolicy smallint retained.
status  int  0x8 = READ o nLY
0x10 = DEFAULT
groupname sysname filegroup.

sysfiles header

Each file in the database occupies a row in the table. This system table is a virtual table and cannot be updated or modified directly.

The column name Data Type description
fileid smallint A unique file identification number for each database.
groupid smallint file group identification number.
size int file size (in 8 KB pages).
maxsize int The maximum file size (in 8 KB pages). A value of 0 means no growth, and a value of –1 means that the file should always be

Grow to disk full.
growth int The growth size of the database. A value of 0 indicates no growth. Depending on the value of the status, it can be hundreds of pages or file size

Divide the ratio. If status contains 0x100000, growth is a percentage of the file size; Otherwise, it's the number of pages.
The status int growth value in megabytes (MB) or kilobytes (KB)).
0x1 = Default device.
0x2 = Disk file.
0x40 = Log device.
0x80 = Files written since the last backup.
0x4000 = by the CREATE DATABASE statement
Implicitly created devices.
0x8000 = Devices created during database creation.
0x100000 = Growth by percentage, not by pages.

perf int retained.
name nchar(128) The logical name of the file.
filename nchar(260) The name of the physical device, including the full path to the file.

sysforeignkeys top of page

Contains information about the FOREIGN KEY constraints in the table definition. The table is stored in each database.

The column name Data Type description
co nstid int the ID of the FOREIGN KEY constraint.
fkeyid int The table object ID with the FOREIGN KEY constraint.
rkeyid int The table object ID referenced in the FOREIGN KEY constraint.
fkey smallint is referencing the column ID.
rkey smallint referenced column ID.
keyno smallint The position of the column in the list of referenced columns.

sysindexes to the top of the page

Each index and table in the database occupies a row in the table. The table is stored in each database.

The column name Data Type description
id int table ID (if indid = 0 or 255). Otherwise, the ID of the table to which the index belongs.
status int internal system status information.
first binary(6) pointers to the first or root page.
indid smallint index ID:
1 = Clustered index
>1 = non-aggregated
255 = Table entries with text or image data

root binary(6) If indid >= 1 and < 255, root is the pointer to the root page. If indid = 0 or

indid = 255, root is the pointer to the last page.  
minlen smallint minimum line size.
keycnt smallint key.
groupid smallint on which the filegroup ID of the object is created.
dpages int If indid = 0 or indid = 1, dpages is the count of pages used. If indid =

255, which is set to 0. Otherwise it is the count of pages that have been indexed.
reserved int If indid = 0 or indid = 1, reserved is the page meter assigned to all indexes and table data

number. If indid = 255, reserved is the page count assigned to the text or image data. Otherwise it is assigned to an index

page count.
used&

nbsp; int If indid = 0 or indid = 1, used is the total number of pages used for all index and table data. If

indid = 255, used is the page count used for text or image data. Otherwise it is the page count used for indexing.
Rowcnt Bigint is based on data-level row counts of Indid = 0 and Indid = 1. If indid = 255, rowcnt

set to 0.
RowModctr int counts the total number of rows inserted, removed, or updated since the last count of the table was updated.
xmaxlen smallint maximum row size.
maxirow smallint maximum non-leaf index row size.
OrigFillFactor tinyint The initial fill factor value used when creating the index. The value is not retained; However, if it needs to be heavy

This value can be helpful if you create a new index but can't remember the fill factor you used in the first place.
reserved1 tinyint retained.
reserved2 int retained.
FirstIAM binary(6) hold.
impid smallint retained. Index implementation flag.
lockflags smallint is used to constrain the considered index lock granularity. For example, for lookup tables that are read-only in nature, you can

to set it to only table-level locking to minimize locking costs.
pgmodctr int retained.
keys varbinary(816) A list of column IDs that make up the index key.
name sysname table name (if indid = 0 or 255). Otherwise, the name of the index.
statblob image Statistics blob.
maxlen int retained.
Rows int is based on the number of data-level rows with indid = 0 and indid = 1, which is repeated for indid > 1. If

indid = 255, rows set to 0. The column is provided for backwards compatibility.

sysmembers page
Each database role member occupies a row in the table. The table is stored in each database.

The column name Data Type description
memberuid smallint The user ID of the role member.
The user ID of the groupuid smallint role.

sysobjects header
Each object (constraints, defaults, logs, rules, stored procedures, etc.) created within the database takes up a row in the table. Only in

tempdb, each temporary object occupies a row in the table.

The column name Data Type description
name sysname object name.
Id int object identification number.
xtype char(2) object type. It can be one of the following object types:
C = CHECK constraint
D = DEFAULT or DEFAULT constraint
F = FOREIGN KEY constraint
L = log
FN = scalar function
IF = Inline Table Function
P = stored procedure
PK = PRIMARY KEY constraint (type is K)
RF = Replication Filter Stored Procedure
S = System table
TF = table function
TR = Trigger
U = User Table
UQ = UNIQUE constraint (type is K)
V = view
X = Extended stored procedure

The user ID of the uid smallint owner object.
info smallint retained. For internal use only.
status int retained. For internal use only.
base_schema_
ver int retained. For internal use only.
replinfo int retained. For reproduction use.
parent_obj int the object identifier of the parent object (for example, for triggers or constraints, the table ID).
The creation date of the crdate datetime object.
ftcatid smallint is the full-text directory identifier for all user tables registered for the full-text index, for all users who are not registered

The table is 0.  
schema_ver int version number, which is incremented with each schema change of the table.
stats_schema_
ver int retained. For internal use only.
type char(2) object type. It can be one of the following values:
C = CHECK constraint
D = DEFAULT or DEFAULT constraint
F = FOREIGN KEY constraint
FN = scalar function
IF = Inline Table Function
K = PRIMARY KEY or UNIQUE constraints
L = log
P = stored procedure
R = rule
RF = Replication Filter Stored Procedure
S = System table
TF = table function
TR = Trigger
U = User Table
V = view
X = Extended stored procedure

userstat smallint retained.
sysstat smallint internal state information.
indexdel smallint retained.
refdate datetime for later.
versio n int for later use.
deltrig int retained.
instrig int retained.
updtrig int retained.
seltrig int retained.
category int is used for publishing, constraints, and identification.
cache smallint retained.

syspermissio ns top of page
Contains information about permissions granted and denied to users, groups, and roles within the database. The table is stored in each database.

The column name Data Type description
id int object ID of object permissions; 0 indicates statement permissions.
grantee smallint The ID of the user, group, or role affected by the permission.
Grantor Smallint The ID of the user, group, or role to which the permission is granted or revoked.
ActAdd Smallint for internal use only.
Actmod Smallint is for internal use only.
Seladd Varbinary(4000) for internal use only.
Selmod Varbinary(4000) for internal use only.
updadd varbinary(4000) for internal use only.
updmod varbinary(4000) for internal use only.
refadd varbinary(4000) for internal use only.
refmod varbinary(4000) for internal use only.

systypes header
Each system provides a single line of information for both the data type and the user-defined data type. The table is stored in each database.

These are the types of data provided by the system and their ID numbers.

The column name Data Type description
name sysname data type name.
xtype tinyint physical storage type.
Status Tinyint is for internal use only.
xusertype smallint extends the user type.
length smallint The physical length of the data type.
The internal precision used by the Xprec Tinyint server. (Not available in queries.) ）
The number of internal decimal places used by the Xscale Tinyint server. (Not available in queries.) ）
tdefault int The ID of the stored procedure that performs integrity checks for this data type.
domain int The ID of the stored procedure that performs integrity checks for this data type.
uid smallint The user ID of the data type creator.
Reserved smallint for internal use only.
usertype smallint The user type ID.
variable bit is 1 for variable length data type, otherwise 0.
allownulls bit indicates that this data type defaults to nullity. If CREATE or ALTER TABLE is specified as empty

, then the value will override the default null for this data type.
type tinyint Physically stores data types.
printfmt varchar(255) retained.
prec smallint The level of precision of this data type.
scale tinyint The number of decimal places (based on precision) for this data type.

sysusers header
Every Microsoft in the database? Windows users, Windows groups, Microsoft SQL Server? User or SQL

The Server role occupies a row in the table.

The column name Data Type description
uid smallint user ID, which is unique in this database. 1 is the database owner.
status smallint for internal use only.
Name sysname username or group name, which is unique in this database.
sid varbinary(85) The security identifier for this entry.
roles varbinary(2048) for internal use only.
createDate DateTime of the account's added date.
UpdateDate DateTime The last modified date of the account.
Altuid Smallint is for internal use only.
password varbinary(256) for internal use only.
gid smallint The group ID to which this user belongs. If uid = gid, then this entry defines a group.
Enviro N Varchar (255) reserved.
hasdbaccess int is 1 if the account has database access.
islogin int if the account is a Windows group, a Windows user, or a SQL Server user with a login account

for 1.
isntname int is 1 if the account is a Windows group or a Windows user.
isntgroup int is 1 if the account is a Windows group.
isntuser int is 1 if the account is a Windows user.
issqluser int is 1 if the account is a SQL Server user.
isaliased int is 1 if the account is aliased by another user.
issqlrole int is 1 if the account is a SQL Server role.
isapprole int is 1 if the account is an application role.