SQL Server System Table (3)

Sysmembers Top

Each database role member occupies one row in the table. This table is stored in each database.

Column name	Data Type	Description
Memberuid	Smallint	The User ID of the role Member.
Groupuid	Smallint	The User ID of the role.

 

Sysobjects Top

Each object created in the database (constraints, default values, logs, rules, stored procedures, etc.) occupies one row in the table. OnlyTempdbEach temporary object occupies a row in the table.

Column name	Data Type	Description
Name	Sysname	Object Name.
ID	Int	Object ID number.
Xtype	Char (2)	Object type. It can be one of the following object types: C = check Constraints D = default value or default Constraint F = foreign key constraint L = Log Fn = scalar function If = embedded table functions P = Stored Procedure PK = primary key constraint (type: K) Rf = copy and filter the Stored Procedure S = system table TF = table functions Tr = trigger U = User table Uq = unique constraint (type is K) V = View X = Extended Stored Procedure
UID	Smallint	User ID of the 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 replication.
Parent_obj	Int	ID of the parent object (for example, this ID is the table ID for triggers or constraints ).
Crdate	Datetime	Object creation date.
Ftcatid	Smallint	The full-text directory identifier of all user tables registered for the full-text index. The value is 0 for all user tables not registered.
Schema_ver	Int	Version number, which is added each time the schema of the table is changed.
Stats_schema _ Ver	Int	Retained. For internal use only.
Type	Char (2)	Object type. It can be one of the following values: C = check Constraints D = default value or default Constraint F = foreign key constraint Fn = scalar function If = embedded table functions K = primary key or unique constraint L = Log P = Stored Procedure R = rule Rf = copy and filter the Stored Procedure S = system table TF = table functions Tr = trigger U = User table V = View X = Extended Stored Procedure
Userstat	Smallint	Retained.
Sysstat	Smallint	Internal status information.
Indexdel	Smallint	Retained.
Refdate	Datetime	For future use.
Version	Int	For future use.
Deltrig	Int	Retained.
Instrig	Int	Retained.
Updtrig	Int	Retained.
Seltrig	Int	Retained.
Category	Int	Used for publishing, binding, and identification.
Cache	Smallint	Retained.

 

Syspermissions Top

Contains information about permissions granted and denied to users, groups, and roles in the database. This table is stored in each database.

Column name	Data Type	Description
ID	Int	The object ID of the object permission; 0 indicates the statement permission.
Grantee	Smallint	The ID of the user, group, or role affected by the permission.
Grantor	Smallint	The ID of the user, group, or role that is granted or revoked.
Actadd	Smallint	For internal use only.
Actmod	Smallint	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.

 

Policypes Top

Each system provides data types and user-defined data types, including a row of information. This table is stored in each database.

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

Column name	Data Type	Description
Name	Sysname	Data Type name.
Xtype	Tinyint	Physical storage type.
Status	Tinyint	For internal use only.
Xusertype	Smallint	Extend the user type.
Length	Smallint	The physical length of the data type.
Xprec	Tinyint	The internal precision used by the server. (Cannot be used in queries .)
XScale	Tinyint	The number of internal decimal places used by the server. (Cannot be used in queries .)
Tdefault	Int	The ID of the stored procedure for integrity check on this data type.
Domain	Int	The ID of the stored procedure for integrity check on this data type.
UID	Smallint	The User ID of the Data Type creator.
Reserved	Smallint	For internal use only.
Usertype	Smallint	User Type ID.
Variable	Bit	The variable-length data type is 1; otherwise, it is 0.
Allownulls	Bit	It indicates that this data type is null by default. If the value of create or alter table is null, this value will replace the default null value of this data type.
Type	Tinyint	Physical storage data type.
Printfmt	Varchar (255)	Retained.
Prec	Smallint	The precision level of this data type.
Scale	Tinyint	Decimal places of this data type (based on precision ).

 

Sysusers Top

Every Microsoft? Windows users, Windows groups, Microsoft SQL server? The user or SQL Server role occupies one row in the table.

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 of the entry.
Roles	Varbinary (2048)	For internal use only.
Createdate	Datetime	Account addition date.
Updatedate	Datetime	The last modification date of the account.
Altuid	Smallint	For internal use only.
Password	Varbinary (256)	For internal use only.
GID	Smallint	The ID of the group to which the user belongs. IfUID=GIDThis entry defines a group.
Environ	Varchar (255)	Retained.
Hasdbaccess	Int	If the account has the database access permission, it is 1.
Islogin	Int	If the account is a Windows Group, Windows user, or SQL Server user with a Logon account, the value is 1.
Isntname	Int	If the account is a Windows group or a Windows user, it is 1.
Isntgroup	Int	If the account is in a Windows Group, it is 1.
Isntuser	Int	If the account is a Windows user, it is 1.
Issqluser	Int	If the account is an SQL Server user, it is 1.
Isaliased	Int	If this account uses another user as its alias, it is 1.
Issqlrole	Int	If the account is an SQL Server role, it is 1.
Isapprole	Int	If this account is an applicationProgramRole.