SQL-SERVER SYSTEM TABLE1

syscolumns (all databases)

Contains one row for every column in every table and view, and a row for each parameter in a stored procedure.

Column	Datatype	Description
id	int	ID of the table to which this column belongs or of the stored procedure with which this parameter is associated.
number	smallint	Subprocedure number when the procedure is grouped (0 for nonprocedure entries).
colid	tinyint	Column ID.
status	tinyint	Bitmap used to describe a property of the column or the parameter: 0x08 The column allows null values. 0x40 The parameter is an OUTPUT parameter. 0x80 The column is an identity column.
type	tinyint	Physical storage type; copied from systypes.
length	tinyint	Physical length of data; copied from systypes or supplied by the user.
offset	smallint	Offset into the row where this column appears; if negative, variable-length column.
usertype	smallint	User type ID; copied from systypes.
cdefault	int	ID of the stored procedure that generates the default value for this column.
domain	int	ID of the stored procedure that contains the rule for this column.
name	varchar(30)	Column name.
printfmt	varchar(255)	Reserved.
prec	tinyint	Level of precision for this column.
scale	tinyint	Scale for this column.

 

 

sysconstraints (all databases)

Contains mappings of constraints to the objects that own the constraints.

Column	Datatype	Description
constid	int	Constraint ID.
id	int	ID of the table that owns the constraint.
colid	tinyint	ID of the column on which the constraint is defined. 0 if a table constraint.
spare1	tinyint	Reserved.
status	int	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.

 

 

sysdepends (all databases)

Contains one row for each procedure, view, or table that is referenced by a procedure, view, or trigger.

Column	Datatype	Description
id	int	Object ID.
number	smallint	Procedure number.
depid	int	Dependent object ID.
depnumber	smallint	Dependent procedure number.
depdbid	smallint	Reserved.
depsiteid	smallint	Reserved.
status	smallint	Internal status information.
selall	bit	On if the object is used in a SELECT * statement.
resultobj	bit	On if the object is being updated.
readobj	bit	On if the object is being read.

 

 

sysindexes (all databases)

Contains one row for each clustered index and one row for each nonclustered index. These indexes are the result of a CREATE INDEX statement or the CREATE TABLE statement with a PRIMARY KEY or UNIQUE constraint. Additionally, sysindexes contains one row for each table that has no clustered index and one row for each table that contains text or image columns.

 

Column	Datatype	Description
name	varchar(30)	Name of table (for indid = 0 or 255). Otherwise, name of index.
id	int	ID of table (for indid = 0 or 255). Otherwise, ID of table to which the index belongs.
indid	smallint	ID of index: 0        Table 1        Clustered index >1        Nonclustered 255    Entry for tables that have text         or image data
dpages	int	For indid = 0 or indid = 1, dpages is the count of used data-only pages. For indid = 255, rows is set to 0. Otherwise, dpages is the count of leaf-level index pages.
reserved	int	For indid = 0 or indid = 1, reserved is the total of pages allocated for all indexes on the table and the data pages.  For indid = 255, reserved is the total pages allocated for text or image data. Otherwise, reserved is the total count of pages allocated only for this index.
used	int	For indid = 0 or indid = 1, used is the total count of pages used for all indexes on the table and the data pages.  For indid = 255, used is the total pages used for text or image data. Otherwise, used is the total count of pages used only for this index.
rows	int	The data-level row count based on indid = 0 or indid = 1. This value is repeated for indid > 1. For indid = 255, rows is set to 0.
first	int	Pointer to first data or leaf page.
root	int	For indid >= 1 and < 255, root is the pointer to the root page. For indid = 0 or indid = 255, root is the pointer to the last page.
distribution	int	Pointer to distribution page (if entry is an index).
OrigFillFactor	tinyint	The original fillfactor value used when the index was created. This value is not maintained; however, it can be helpful if you need to re-create an index and do not remember what fillfactor was used.
segment	smallint	Number of segment in which this object resides.
status	smallint	Internal system-status information: 1        Cancel command if attempt to  insert duplicate key 2        Unique index 4        Cancel command if attempt to insert duplicate row 16        Clustered index 64        Index allows duplicate rows 2048    Index used to enforce   PRIMARY KEY constraint 4096    Index used to enforce UNIQUE    constraint
rowpage	smallint	Maximum count of rows per page.
minlen	smallint	Minimum size of a row.
maxlen	smallint	Maximum size of a row.
maxirow	smallint	Maximum size of a nonleaf index row.
keycnt	smallint	Number of keys.
keys1	varbinary(255)	Description of key columns (if entry is an index).
keys2	varbinary(255)	Description of key columns (if entry is an index).
soid	tinyint	Sort order ID that the index was created with. 0 if there is no character data in the keys.
csid	tinyint	Character set ID that the index was created with. 0 if there is no character data in the keys.

 

 

sysobjects (all databases)

Contains one row for each object (constraint, default, log, rule, stored procedure, and so on) created within a database. In tempdb only, this table includes a row for each temporary object.

 

Column	Datatype	Description
name	varchar(30)	Object name.
id	int	Object ID.
uid	smallint	User ID of owner object.
type	char(2)	One of the following object types: C    CHECK constraint D    Default or DEFAULT constraint F    FOREIGN KEY constraint K    PRIMARY KEY or UNIQUE constraint L    Log P    Stored procedure R    Rule RF    Stored procedure for replication S    System table TR    Trigger U    User table V    View X    Extended stored procedure
userstat	smallint	Application-dependent type information.
sysstat	smallint	Internal-status information.
indexdel	smallint	Index delete count (incremented if an index is deleted).
schema	smallint	Count of changes in schema of a given object (incremented if a rule or default is added).
refdate	datetime	Reserved for future use.
crdate	datetime	Indicates the date that the object was created.
version	datetime	Reserved for future use.
deltrig	int	Stored-procedure ID of a delete trigger.
instrig	int	Stored-procedure ID of an insert trigger.
updtrig	int	Stored-procedure ID of an update trigger.
seltrig	int	Reserved.
category	int	Used for publication, constraints, and identity.
cache	smallint	Reserved.

 

 

systypes (all databases)

Contains one row for each system-supplied and each user-defined datatype. Domains (defined by rules) and defaults are given if they exist. The rows that describe system-supplied datatypes cannot be altered. The system-supplied datatypes and their ID numbers (the contents of the name and type fields, respectively) are as follows:

 

 

Column	Datatype	Description
uid	smallint	User ID of datatype creator.
usertype	smallint	User type ID.
variable	bit	Variable-length datatype is 1, otherwise, 0.
allownulls	bit	Indicates the default nullability for this datatype. If nullability is specified with the CREATE or ALTER TABLE statement then that value will override the default nullability for this datatype.
type	tinyint	Physical storage datatype.
length	tinyint	Physical length of datatype.
tdefault	int	ID of stored procedure that generates default for this datatype.
domain	int	ID of stored procedure that contains integrity checks for this datatype.
name	varchar(30)	Datatype name.
printfmt	varchar(255)	Reserved.
prec	tinyint	Level of precision for this datatype.
scale	tinyint	Scale for this datatype. Scale is based on precision.