Appendix A: System Tables and Undocumented DBCC Commands

In this appendix, I'll cover some of the tables and DBCC commands that you can use while administering SQL Server. Most of the parts in this appendix are undocumented and should be used with care.

Note 

Some unimportant columns and tables have been left out of this appendix to save space. These would include obsolete, unimportant, and reserved tables and columns.

System Tables

The SQL Server system tables are broken into three parts: those tables that are only in the master database, those that are located in each database independently, and those that are only in the other system tables, such as the msdb database. Most of these tables are unsupported by Microsoft Product Support for direct use. It is recommended that you use these tables for reporting purposes only and not modify them directly. Keep in mind that these tables are likely to change between releases or even service packs of SQL Server. If you modify them, make sure that you back up the appropriate database. Before you can modify any system table, you must first enable updates to the system catalog by using the sp_configure stored procedure, as shown here:

EXEC sp_configure 'allow updates', '1' RECONFIGURE WITH OVERRIDE
Caution 

Referential integrity for system tables is handled by the tools that use it (such as Enterprise Manager). If you make a modification to system tables, make sure the value you're changing is a valid one.

Tables in the Master Database Only

There are a number of tables that can be found in the master database only. These tables hold instance-level information like logins and server names.

sysaltfiles

The sysaltfiles table contains a list of files on the server.

Column

Data Type

Purpose

fileid

smallint

Unique identification for each file in the database.

groupid

smallint

Identification number for the file group the database belongs to.

size

int

The size of the file in 8K pages.

maxsize

int

Maximum size of the database in 8K pages. A value of -1 is default and specifies that the database will grow until it fills the hard drive. A value of 0 specifies a fixed database size.

growth

int

This can represent either the number of pages or percentage to grow the database. This is dependent on the status flag.

status

int

The status of the file used internally in SQL Server. For example, a status of 0x100000 sets the file to grow automatically to a given percentage based on the growth column.

perf

int

Internal reserved field.

dbid

smallint

Database ID that the file belongs to. This can be related to the dbid column in the sysdatabases table.

name

nchar(128)

The file's logical name.

filename

nchar(260)

Full path and filename of the file.

syscacheobjects

The syscacheobjects table shows you how SQL Server is using its cache.

Column

Data Type

Purpose

bucketid

int

The bucket ID where the object is cached.

cacheobjtype

nvarchar(34)

Type of cached object.

Included types:

Compiled Plan
Executable Plan
Parse Tree
Cursor Parse Tree
Extended Stored Procedure

objtype

nvarchar(16)

More detailed type of object. For example, this would display whether the cached object is an ad hoc query or prepared query. Valid types include:

Stored procedure
Prepared statement
Ad hoc query
ReplProc
Trigger
View
Default
User table
System table
Check
Rule

objid

int

Used to find the object in cache. For a prepared query such as a stored procedure, this would relate to the objid in the sysobjects table. If this is an ad hoc item in cache, this would represent an internally generated number.

dbid

smallint

Database ID the cached item belongs to. This can be related to the dbid column in the sysdatabases table.

uid

smallint

If this value is -2, the cached object is from a batch not corresponding to a user. Otherwise, this represents a user ID.

refcounts

int

Number of other objects that reference the cached object.

usecounts

int

Number of times the cached object has been used since it was created.

pagesused

int

Amount of memory space in 8K pages used by the cached object.

setopts

int

Any time a user changes options, such as ANSI_PADDING using the SET command,
it is stored here.

langid

smallint

Language ID for the cached object's creator. This relates to the langid in the syslanguages table.

sqlbytes

int

Length of the procedure name or batch in cache. Used to distinguish when first 128 bytes are the same.

Sql

nvarchar(256)

Stored procedure name or the first 128 bytes of the ad hoc query submitted.

syscharsets

The syscharsets table designates which character sets and sort orders are available to SQL Server.

Column

Data Type

Purpose

type

smallint

Defines which type of entry the row is. A value of 1001 represents a character set and 2001 represents a sort order.

id

tinyint

Unique identifier for the character set or sort order.

csid

tinyint

If the type value is 2001 (sort order), this value is used to designate the character set the sort order is built on.

name

sysname

Represents the name of the character set or sort order.

description

nvarchar(255)

Represents the optional long name for the sort order or character set.

definition

image

Used internally to define the sort order or character set.

sysconfigures

The sysconfigures table contains an entry for each configuration option set in SQL Server. These options are read by the sp_configure stored procedure. Once you change the configuration of the server, this table is adjusted and not moved to the syscurconfigs table until the server is restarted or the RECONFIGURE command run.

Column

Data Type

Purpose

value

int

The value that the server setting is configured for when the server is restarted or when the RECONFIGURE command is issued.

config

smallint

Variable number for the configuration option.

comment

nvarchar(255)

The name of the option.

status

smallint

Specifies whether the setting is static or dynamic. Possible settings are

0 = Static
1 = Dynamic
2 = Advanced
3 = Dynamic and advanced

syscurconfigs

The syscurconfigs table is identical to the sysconfigures table. The only difference
is that the syscurconfigs table shows what settings the server is running currently, while the sysconfigures table shows what the server will be configured for once the server is restarted or RECONFIGURE issued. Whether you must restart or issue a RECONFIGURE command is based on the status flag.

sysdatabases

The sysdatabases table contains a list of the databases installed on your SQL Server.

Column

Data Type

Purpose

name

sysname

Database's name.

dbid

smallint

Database's ID.

sid

varbinary(85)

Login ID for the database's creator.

mode

smallint

Prohibits a database from being accessed while it is being created.

status

int

Database properties represented as a bitwise field.

status2

int

Extended database properties represented as a bitwise field.

crdate

datetime

Database's creation date.

reserved

datetime

Internal SQL Server field reserved for future use.

cmptlevel

tinyint

Database compatibility level. Valid compatibility levels are 60, 65, 70, and 80.

filename

nvarchar(260)

Primary database path and filename.

version

smallint

Internal version number that SQL Server uses when upgrading.

syslanguages

The syslanguages table contains a list of languages installed on the SQL Server. U.S. English is always included.

Column

Data Type

Purpose

langid

smallint

Unique language ID.

dateformat

nchar(3)

Order that the date uses for the language. For example, U.S. English uses MDY (month, day, year).

datefirst

tinyint

First day in the week for the language. The number 1 represents Monday and the other days are incremented by 1. This is in place because some regions use Monday as the first day of the week while others, such as U.S. English, use Sunday (7).

name

sysname

Official regional name of the language, such as Español.

alias

sysname

English name for the language, such as Spanish.

months

nvarchar(372)

Comma-separated list of months in the native language.

shortmonths

nvarchar(132)

Comma-separated list of the months in the native language using the month's short name.

days

nvarchar(217)

Comma-separated list of days in the native language.

lcid

int

Microsoft Windows locale ID for the language.

syslockinfo

The syslockinfo table gives you a glimpse at what the SQL Server lock manager is granting locks to. It contains information about all granted, waiting, and converting locks.

Column

Data Type

Purpose

rsc_text

nchar(32)

Description of the locking resource.

rsc_bin

binary(16)

Lock resource contained in lock manager in binary.

rsc_valblk

binary(16)

Source resources may provide additional lock block values that are stored here.

rsc_dbid

smallint

Database ID for the locked resource.

rsc_indid

smallint

The index ID (if used) for the locked resource.

rsc_objid

int

The object ID (if used) for the locked resource.

rsc_type

tinyint

Locked resource:

1 = NULL
2 = Database
3 = File
4 = Index
5 = Table
6 = Page
7 = Key
8 = Extent
9 = Row ID (RID)
10 = Application

req_mode

tinyint

Type of lock requested on the resource type. There are lots of various lock methods used, but a few of those would include:

0 = NULL
3 = Shared
4 = Update
5 = Exclusive

req_status

tinyint

Represents the status of the lock's request. Values could be any of the following:

1 = Granted
2 = Converting
3 = Waiting

req_refcnt

smallint

Each time the lock is requested, this number is incremented by 1. The lock cannot be released until this is equal to 0.

req_lifetime

int

Lifetime of the lock represented as a bitmap.

req_spid

intint

Process ID that is requesting the lock.

req_ownertype

smallint

Object associated with the lock such as a transaction (1) or a cursor (2).

req_transactionID

bigint

Requesting transaction ID.

req_transactionUOW

uniqueidentifier

This column is used for DTC transactions.

syslogins

The syslogins table contains a list of logins that are assigned to the server.

Column

Data Type

Purpose

sid

varbinary(85)

Security ID for the user.

createdate

datetime

Date the login was created.

updatedate

datetime

Date the login was last updated.

name

varchar(30)

Login ID for the user.

dbname

nvarchar(128)

Default database for the login when the login connects to the server.

password

navarchar(128)

Login's encrypted password.

language

navarchar(128)

Language name that the user will use by default.

denylogin

int

Flag (1 if active) to deny the Windows user or group access to the SQL Server.

has access

int

Flag (1 if active) to grant the Windows user or group access to the SQL Server.

isntname

int

Set to 1 if the login is a Windows user or group and 0 if the login is a SQL Server login.

isntgroup

int

Set to 1 if the login represents a Windows group.

isntuser

int

Set to 1 if the login represents a Windows user.

sysadmin

int

Set to 1 if the user is a member of the sysadmin server role.

securityadmin

int

Set to 1 if the user is a member of the securityadmin server role.

serveradmin

int

Set to 1 if the user is a member of the serveradmin server role.

setupadmin

int

Set to 1 if the user is a member of the setupadmin server role.

processadmin

int

Set to 1 if the user is a member of the processadmin server role.

diskadmin

int

Set to 1 if the user is a member of the diskadmin server role.

dbcreator

int

Set to 1 if the user is a member of the dbcreator server role.

loginname

nvarchar(128)

Name of the login. Windows logins are represented as domainname\ loginname.

sysmessages

The sysmessages table contains a list of the available error and informational messages installed on the SQL Server. You can use sp_addmessage to add a new message to the table.

Column

Data Type

Purpose

error

int

Unique error number. Numbers above 50001 are available for user-defined messages.

severity

smallint

Severity level of the message.

description

nvarchar(255)

Description of the error that will be outputted to the client.

msglangid

smallint

Language the error is in.

sysoledbusers

The sysoledbusers table contains a record for each user used for linked servers.

Column

Data Type

Purpose

rmtsrvid

smallint

Security ID (SID) for the remote server.

rmtloginame

navrchar(128)

Remote login name.

rmtpassword

navarchar(128)

Encrypted password for the remote login.

loginsid

varbinary(85)

Security ID (SID) of the local login to be mapped to the remote login.

status

smallint

Set to 1 if SQL Server should use the login credentials of the currently logged-in user.

changedate

datetime

Date the login mapping was last adjusted.

sysperfinfo

The sysperfinfo database contains a list of performance counters used when monitoring SQL Server in System Monitor. This table can be invaluable when you're trying to perform automated trend analysis of your system. This table only can track the first 99 databases installed on a server.

Column

Data Type

Purpose

object_name

nchar(128)

Performance object name.

counter_name

nchar(128)

Counter name under the object name.

instance_name

nchar(128)

Named instance of the SQL Server if applicable.

cntr_value

int

Current counter value for the setting.

cntr_type

int

Windows NT or Windows 2000 type of counter.

sysprocesses

The sysprocesses table contains a list of the system and user processes currently running on the server. You can also see in this table how much of your system resources is being taken by the individual process.

Column

Data Type

Purpose

spid

smallint

Unique SQL Server process ID.

kpid

smallint

The Windows NT or Windows 2000 thread ID being used by the process.

blocked

smallint

Process ID of the process that is blocking this row's process.

waittime

int

Represents how long the database process has been waiting in milliseconds.

lastwaittype

nchar(32)

Name of the current or last wait type.

waitresource

nchar(256)

Description of the lock resource.

dbid

smallint

Database ID that is currently using the process.

uid

smallint

User ID for the user who executed the command. This links to the UID in the sysusers table in each database.

cpu

int

Amount of CPU the process has used.

physical_io

bigint

Amount of disk I/O the process has caused.

memusage

int

Amount of procedure cache in pages that is currently allocated to the process. If this number is negative, the process is freeing memory allocated to another process.

login_time

datetime

Represents the time when the process began. For system processes, this will represent the time that SQL Server was started.

last_batch

datetime

Time of the last executed stored procedure by the client process.

ecid

smallint

Represents any subthreads that are operating under the process.

open_tran

smallint

Number of transactions currently open for the given process.

status

nchar(30)

Current status of the process. Some of the values you may see here are sleeping, background, and running.

sid

binary(86)

Login ID for the user.

hostname

nchar(128)

Name of the workstation that created the process. System processes will not have values for this setting.

program_name

nchar(128)

Name of the program that created the process. For example, a query run in Query Analyzer would show the value of SQL Query Analyzer.

hostprocess

nchar(8)

Process ID number for the workstation.

cmd

nchar(16)

Command that is currently being run against the server to create the process.

nt_domain

nchar(128)

Domain name if the user is connecting with Windows Authentication.

nt_username

nchar(128)

Windows user name if the user is connecting with Windows Authentication.

net_address

nchar(12)

Represents the MAC address for the user who is connecting to the system.

net_library

nchar(12)

Represents the method of communication to the server for the user (for example, TCP/IP).

loginame

nchar(128)

The login name for the connecting user.

sysremotelogins

The sysremotelogins table contains information about users who are allowed to execute remote stored procedures.

Column

Data Type

Purpose

remoteserverid

smallint

Remote server's ID.

remoteusername

nvarchar(128)

Login name on a remote server.

sid

varbinary(85)

Windows NT or Windows 2000 security ID for the user.

status

smallint

Bitmap of options that have been set.

changedate

datetime

Last time the information in the row was modified.

sysservers

The sysservers table contains a record for the local server as well as any linked servers or servers participating in replication. Most of the options in this table are discussed in detail in Chapter 9.

Column

Data Type

Purpose

srvid

smallint

Unique server ID for the remote or local server.

srvname

sysname

Name of the server. This is generally the name of the SQL Server instance, but this can also represent the linked server's name.

srvproduct

nvarchar(128)

Type of product that OLE DB uses to connect to the server.

providername

nvarchar(128)

Type of provider that OLE DB will use to connect to the server. This is SQLOLEDB for other SQL Server sources.

datasource

nvarchar(4000)

Name or IP address of the remote or local server.

location

nvarchar(4000)

Name of the OLE DB location value.

providerstring

nvarchar(4000)

Value for any customized provider string used when connecting to the provider.

schemadate

datetime

Date the entry was last updated.

topologyx

int

Used in Enterprise Manager for drawing a topology diagram of replication.

topologvy

int

Used in Enterprise Manager for drawing a topology diagram of replication.

catalog

sysname

The default catalog to connect to on a remote server.

srvcollation

int

The collation of the remote server if the remote collation option is specified.

connecttimeout

int

The point at which a server connection is timed out (in seconds).

querytimeout

int

The point at which a query is timed out (in seconds).

isremote

bit

If this option is set to 1, the entry represents a remote server. Otherwise it is a linked server.

rpc

bit

If this option is set to 1, users are allowed to make RPC calls from the remote server.

pub

bit

If this option is set to 1, the remote server is a publisher.

sub

bit

If this option is set to 1, the remote server is a subscriber.

dist

bit

If this option is set to 1, the remote server is a distributor.

dpub

bit

If this option is set to 1, the remote server is a distributor and a publisher.

rpcout

bit

If this option is set to 1, the user is allowed to make RPC calls to the remote server.

dataaccess

bit

If this option is set to 1, the user is able to access data on the remote server.

collationcompatible

bit

If this option is set to 1, the collation compatible option is enabled.

system

bit

If this option is set to 1, enables the system option.

userremotecollation

bit

If this option is set to 1, specifies that the user's query will use the remote system's collation.

lazyschemavalidation

bit

Turns on the lazy schema validation option. This option is used heavily in the distributed partitioned views feature.

collation

sysname

Name of the collation used.

Tables in Every Database

The following tables are represented in every database in SQL Server. These allow databases to be moved from server to server without trouble. Many of these tables have views that can be used instead of the system tables. It is much less unlikely that the views will be adjusted from version to version where the system tables are often modified or removed.

syscolumns

There is an entry in the syscolumns table for every column in the database. The entries in this table do include columns from the system tables.

Column

Data Type

Purpose

name

sysname

Name of the column.

id

int

Object's unique identifier for the column.

xtype

tinyint

What data type the column is storing data in. Related to the systypes table.

xusertype

smallint

ID for user-defined data types if used for the column.

length

smallint

Maximum storage capacity for the column.

colid

smallint

Column or parameter ID.

cdefault

int

ID for the column's default value, which links to the sysobjects table.

domain

int

ID for the rule or CHECK constraint for the column.

colorder

smallint

Column order for the table.

collantionid

int

Collation ID for the column.

language

int

Language ID for the column.

status

tinyint

Bitmap of properties for the column.

type

tinyint

Type of column which is linked to the systypes table.

usertype

smallint

Type of user-defined data type from systypes.

prec

smallint

Precision of the column if applicable.

scale

int

Scale of the column if applicable.

iscomputed

int

Specifies if the column is computed with a flag of 1.

isoutparam

int

Specifies if the procedure parameter is an output parameter.

isnullable

int

Specifies if the column allows nulls.

collation

sysname

Collation of the column.

syscomments

The syscomments table is an important table, which holds all the stored procedures, views, rules, triggers, DEFAULT constraints, and CHECK constraints for a given database. It is important to never edit this table directly because it is highly dependent on sequence. If you delete one entry, all the others that are linked to the deleted stored procedure will cease to function.

Column

Data Type

Purpose

id

int

Object ID for the row.

number

smallint

This column groups the stored procedure. If there is a 0 in this column, the procedure is not grouped.

colid

smallint

Sequence of the stored procedure if it's longer than 4000 bytes. For example, if a stored procedure is over 4000 bytes, it will create a new row and be flagged in this column with a 2.

ctext

varbinary(8000)

Actual text of the stored procedure in binary format.

texttype

smallint

0 = User-supplied comment.
1 = System-supplied comment.
4 = Encrypted comment.

language

smallint

Language ID the stored procedure is in. Links to the syslanguages table in the master database.

encrypted

bit

Flag indicates whether the stored procedure is encrypted. A value of 0 means the procedure is not encrypted and 1 means it is.

compressed

bit

Flag indicates whether the stored procedure is compressed. A value of 0 means the procedure is not compressed and 1 means it is.

text

nvarchar(4000)

The actual text for the stored procedure. This column is handy when you need to find a stored procedure that has a certain word in it.

sysconstraints

The sysconstraints table contains a list of the constraints in a database.

Column

Data Type

Purpose

constid

int

Represents the constraint number.

id

int

Object ID for the table the constraint is in.

colid

smallint

Object ID for the column the constraint is on.

status

int

Represents the type of constraint (PK, FK, UNIQUE).

sysfilegroups

The sysfilegroups table includes a table for each file group used by your database.

Column

Data Type

Purpose

groupid

smallint

Unique group ID for each database.

status

int

Specifies whether the file group is read only (0x8) or the default file group (0x10).

groupname

sysname

Logical name of the file group.

sysfiles

The sysfiles table contains a record for each data and log file used in a given database.

Column

Data Type

Purpose

fileid

smallint

Unique file ID for each data and log file that your database uses.

groupid

smallint

File group ID that the file is a member of.

size

int

Size of the file in 8K pages.

maxsize

int

Maximum size the file can grow to in 8K pages. If the value is 0, no file growth is allowed. If the number is -1, the file will grow until it runs out of hard drive space.

growth

int

Specifies how large the increments will be that the database grows in. A setting of 0 specifies no growth. The setting is in either pages or a percentage of growth based on the status flag.

status

int

Bitmap value for the database growth.

name

nchar(128)

Logical name for the file.

filename

nchar(260)

Full path and filename for the file.

sysforeignkeys

The sysforeignkeys table contains information for any foreign keys a table may use.

Column

Data Type

Purpose

constid

int

Unique ID for the constraint.

fkeyid

int

Object ID for the table that has the foreign key constraint.

rkeyid

int

Object ID for the table that is referenced in the key.

fkey

smallint

ID for the column that has the foreign key on it.

rkey

smallint

ID for the column that the foreign key links to.

keyno

smallint

Ordinal position of the column in the column list.

sysfulltextcatalogs

The sysfulltextcatalogs table lists all the full-text catalogs in a given database.

Column

Data Type

Purpose

ftcatid

smallint

Unique ID for the full-text catalog.

name

sysname

Name of the catalog.

path

nvarchar(260)

Path for the catalog given by the server. If the value of this column is NULL, the default is used.

sysindexes

The sysindexes table contains a indexes installed in a given table.

Column

Data Type

Purpose

id

int

ID for the table where the index is created.

first

binary(6)

Pointer to the index's first or root page.

indid

smallint

Type of index:

1 = Clustered index
>1 = Nonclustered
255 = Tables that have image or text columns.

root

binary(6)

For clustered indexes, the root is a pointer to the root page. Otherwise, this is a pointer to the last page.

minlen

smallint

Row's minimum size.

keycnt

smallint

The number of keys on the index.

groupid

smallint

File group ID where the index was created.

dpages

int

Number of data pages used in the index if applicable.

reserved

int

Number of data pages reserved for the index where applicable.

used

int

Number of pages used for the index where applicable.

rowcnt

bigint

Number of rows in the table.

rowmodctr

int

Total number of inserts, deletes, and updates since the last time statistics were updated.

xmaxlen

smallint

Maximum width of a row.

maxirow

smallint

Maximum width of a row for a nonleaf index row.

origfillfactor

tinyint

Original fill factor used when creating the index.

keys

varbinary(1088)

List of keys by their column IDs.

statblob

image

Binary large object (BLOB) that contains the statistics.

name

sysname

Name of the index.

rows

int

Number of rows in the table.

sysindexkeys

The sysindexkeys table maps which indexes are being used by which tables.

Column

Data Type

Purpose

id

int

Identifier for the table.

indid

smallint

Identifier for the index.

colid

smallint

Identifier for the column.

keyno

smallint

Ordinal position of the column for the index.

sysmembers

The sysmembers table maps user names to a database role.

Column

Data Type

Purpose

memberuid

smallint

User ID to be mapped.

groupuid

smallint

Database role ID.

sysobjects

The sysobjects table is one of the most important tables in the catalog. It contains a record for each database object in it.

Column

Data Type

Purpose

name

sysname

Name of the object.

id

int

Object ID.

xtype

char(2)

Type of object. A few common ones would include:

C = CHECK constraint
D = DEFAULT constraint
F = Foreign Key
P = Stored procedure
PK = Primary Key
S = SQL Server system table
TR = Trigger
U = User-defined table
V = View
X = Extended stored procedure

uid

smallint

Creator of the object.

parent_obj

int

Object ID for the parent of the object. For example, if you were to have a DEFAULT constraint, its parent would be the table's object ID.

crdate

datetime

Date on which the object was created.

ftcatid

smallint

Full-text catalog ID if applicable for a table.

schema_ver

int

Incremented version each time the schema changes for a table.

type

char(2)

Type of object. The values for this are the same as the xtype column mentioned previously.

syspermissions

The syspermissions provides a matrix of permissions assigned to the users, groups, or roles of a given database. More permission information is in the sysprotects table.

Column

Data Type

Purpose

id

int

Object ID the permission is being assigned to.

grantee

smallint

User ID of the user, group, or role that is affected by the permission.

grantor

smallint

User ID of the user, group, or role that assigned the permission to the object.

sysprotects

The sysprotects table expands on the syspermissions table and specifies which type of permissions the user has.

Column

Data Type

Purpose

id

int

Object ID that the user or group has permissions set.

uid

smallint

User ID or group where the permissions have been applied.

action

tinyint

What type of permission has been applied to the object. Some of the many valid command values you may see are

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

Specifies whether the user has granted access (204 and 205) to the object or revoked access (206).

columns

varbinary(4000)

List of columns that have permissions applied where applicable.

grantor

smallint

User ID of the user who granted access in each instance.

sysreferences

The sysreferences table shows the relationships between the tables.

Column

Data Type

Purpose

constid

int

Object ID of the foreign key constraint.

fkeyid

int

Object ID of the table with the foreign key (referencing table).

rkeyid

int

Object ID of the table being referenced.

rkeyindid

smallint

Index ID for the unique index on the referenced table.

keycnt

smallint

Number of columns involved in the foreign key.

fkey1-16

smallint

Column IDs for the columns referencing the table.

rkey1-16

smallint

Column IDs for the columns being referenced.

systypes

The systypes table contains a list of system and user-defined data types.

Column

Data Type

Purpose

name

sysname

Name of the data type.

xtype

tinyint

Physical storage type.

xusertype

smallint

Extended user type.

length

smallint

Length of the data type.

xprec

tinyint

Default precision of the data type.

xscale

tinyint

Default scale of the data type.

tdefault

int

Object ID for the stored procedure that checks default integrity information.

domain

int

Object ID for the stored procedure that checks default integrity information.

uid

smallint

User ID for the data type's creator.

usertype

smallint

User type ID.

variable

bit

If the value is set to 1, specifies that the data type is a variable-length column.

allownulls

bit

Specifies whether the data type allows NULL values by default.

type

tinyint

Specifies how the data type is stored physically.

prec

smallint

Default precision of the data type.

scale

tinyint

Default scale of the data type.

sysusers

The sysusers table contains a list of users and roles in a given database.

Column

Data Type

Purpose

uid

smallint

Unique user ID for the user or role in the database. The ID is only unique in the database and not across all databases.

status

smallint

Type of user, whether a role, Windows Authentication login, or standard login.

name

sysname

Name of the login, group, or role.

sid

varbinary(85)

Login security ID for the user.

createdate

datetime

Date on which the user was created.

updatedate

datetime

Date on which the user was last updated.

gid

smallint

Group ID where the user belongs.

hasdbaccess

int

Set to 1 if the user has access to the database.

islogin

int

Set to 1 if the user is a Windows group or user, or SQL Server user with a login account.

isntname

int

Set to 1 if the user is a Windows NT group or user.

isntgroup

int

Set to 1 if the user is a Windows NT group.

isntuser

int

Set to 1 if the user is a Windows NT user.

issqluser

int

Set to 1 if the user is a SQL Server standard user.

isaliased

int

Set to 1 if the user is using an alias to another account.

issqlrole

int

Set to 1 if the user is a SQL Server role.

isappprole

int

Set to 1 if the user is an application role.




SQL Server 2000 for Experienced DBAs
ppk on JavaScript, 1/e
ISBN: 72227885
EAN: 2147483647
Year: 2005
Pages: 126

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net