system tables in SQL Server

Stores all system information for SQL. Stores metadata about the database service so-called metadata (such as which user databases the data server has, which login accounts the database server has, which tables are in the database, what fields each table has, which stored procedures, views, and so on), and the system table General SYS begins.

any user should not directly change the system tables. For example, do not attempt to modify a system table with a DELETE, UPDATE, INSERT statement, or user-defined trigger.

To retrieve the information stored in the system tables, the application should use one of the following components:

• system stored procedure

• transact-sql statements and functions

• sql Server Management Object (SMO)

• Replication Management Objects (RMO)

• database API directory functions

A few important system tables

First,master.dbo. sysdatabases system table:

Each database on Microsoft SQL Server occupies a row in the table.

When you initially install SQL Server, sysdatabases contains entries for the master, model, msdb, mssqlweb, and tempdb databases.

Key fields:

Name: The names of the libraries;

Dbid: The id,dbid of the library is system from 1 to 5. These are the five libraries: master, model, msdb, mssqlweb, tempdb.

SELECT * from master.dbo.sysdatabases can query out all the library names.

Second, Sysobjects: (Previous blog has an introduction to this table)

Each database in Sql-server has this system table, which holds all objects created within the database, such as constraints, defaults, logs, rules, stored procedures, and so on.

Key fields
Name: Object names

ID: Object ID

Xtype (Type): Object type

UID: Owner object User ID

Status: Object state.

The object Type (xtype). Can be one of the following object types:
C = CHECK constraint
D = defaults or Default constraints
F = FOREIGN KEY constraint
L = log
FN = Scalar function
IF = inline table function
P = stored procedure
PK = PRIMARY KEY constraint (type is K)
RF = copy 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
when xtype= ' U ' and status>0 represent a table created by the user, the object name is the table name, and the object ID is the ID value of the table (object_id (' table name ')).

SELECT * fromwyl_db. dbo.sysobjects where xtype= ' U ' and status>0 can list all the user-created table names in the library wyl_db .

Third, syscolumns:

Each column in the table and view occupies a row in the table, and each parameter in the stored procedure also occupies a row in the table. The table is located in each database.

Key fields are:

Name: Field names

ID: ID Number of the table

colid: Field ID number.

The ID is the ID number of the table we got with sysobjects just above.

  select name from wyl_db .dbo. syscolumns where id=object_id (' table name '): Get wyl_db .dbo. syscolumns a field in a table

Other system tables

sysaltfiles Saving a database file
syscharsets Character Set and sort order
sysconfigures configuration options
Syscurconfigs Current configuration options
syslanguages language
syslogins login account information
sysoledbusers Link Server login information
sysprocesses process
sysremotelogins telnet account
sysconstrains Restrictions
sysfilegroups file group
sysfiles file
sysforeignkeys External keywords
Sysindexs Index
sysmenbers role members
syspermissions Permissions
systypes user-defined data types
sysusers users

system tables in SQL Server