Wiki Home

System Tables


Namespace: SQL
Some SQL system tables -- where SQL Server stores its metadata.
System Table Description
sysforeignkeys Resides in each database and stores information about the foreign keys in that database.
sysindexes Contains a row for each index within a database. Specifies the table with which the index is associated, whether it's clustered or nonclustered, the number of keys, and so on.
sysindexkeys Stores information on the keys or columns in an index. Specifies the table ID, the index ID, the column, and the position of the column in the index.
sysmessages Contains one row for each system error or warning that can be returned by SQL Server.
sysobjects Contains a row for each database object such as a user table, system table, check constraint, default constraint, foreign key, log, stored procedure, primary key, trigger, and new or extended stored procedure. This table is the hub of all the system tables and provides information about these objects, including name, object ID, the owner object, and creation date.
syspermissions Stores information about the permissions granted and denied to users, groups, and roles for the objects in a database.
systypes Contains a row for each datatype in the current database. Datatypes detailed in this table include the SQL Server standard datatypes as well as user-defined datatypes.
syscolumns Contains a row for each column in a table or each parameter in a stored procedure. This table maintains the specifics for each column, including name, datatype, length, precision, scale, and nullability. The ID field specifies the table or stored procedure with which the column or parameter is associated.
syscomments Contains a row for each stored procedure, view, rule, default, trigger, or check constraint. The rows in this table store details about each of these objects, including the actual text of the object.
sysconstraints Each database has a sysconstraints table with a row for each constraint. Specifies the table and column with which the constraint is associated as well as the type of constraint: primary key, foreign key, unique constraint, check constraint, or default constraint.
sysdatabases Contains information on each database residing on the server and is part of the master system database. Includes the database name, ID, status, the database owner, and the date it was created.
sysdepends Contains object dependencies for views, stored procedures, and triggers in the current database.

Category Database Structure
( Topic last updated: 2000.06.13 10:20:19 PM )