SQL Server System table and SQL Server System Table

SQL Server System table and SQL Server System Table

Stores all the system information of SQL. Stores metadata related to database services (for example, user databases on the data server, logon accounts on the database server, and tables on the database server, which fields are contained in each table? What are stored procedures and views in each database?) system tables generally start with sys.

No user should directly change the system table. For example, do not try to use DELETE, UPDATE, INSERT statements, or user-defined triggers to modify the system table.

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

• System stored procedures

• Transact-SQL statements and functions

• SQL Server Management object (SMO)

• Copy management object (RMO)

• Database API directory Functions

Several important system tables

1. master. dbo. sysdatabases system table:

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

When you first Install SQL Server, sysdatabases contains the master, model, msdb, mssqlweb, and tempdb database items.

Keyword segment:

Name: name of the database;

Dbid: Database ID. dbid ranges from 1 to 5. These databases are master, model, msdb, mssqlweb, and tempdb respectively.

Select * from master. dbo. sysdatabases to query all database names.

Ii. Sysobjects: (previous blogs have introduced this table)

Each database of the SQL-SERVER has this system table, which stores all the objects created in the database, such as constraints, default values, logs, rules, stored procedures, and so on.

Keyword Field
Name: Object Name

Id: Object ID

Xtype (type): Object type

Uid: User ID of the owner object

Status: Object status.

Object Type (xtype ). 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
When xtype = 'U' and status> 0 indicates that the table is created by the user, the object name is the table name, and the Object ID is the table ID value (object_id ('table name ')).

Select * fromwyl_db.dbo.sysobjects where xtype = 'U' and status> 0 to list the table names created by all users in wyl_db.

Iii. syscolumns:

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

Keyword fields:

Name: field name

Id: id of the table

Colid: ID of the field.

The ID is the ID of the table we just obtained using sysobjects.

Select name fromwyl_db.dbo.syscolumns where id = object_id ('table name'): Obtain fields in a wyl_db.dbo.syscolumns table

Other system tables

Sysaltfiles: Save database files
Syscharsets Character Set and sorting order
Sysconfigures configuration options
Syscurconfigs current configuration option
Syslanguages Language
Syslogins Login Account Information
Sysoledbusers link Server login information
Sysprocesses Process
Sysremotelogins remote Logon account
Sysconstrains restrictions
Sysfilegroups File Group
Sysfiles
Sysforeignkeys external keyword
Sysindexs Index
Sysmenbers role member
Syspermissions permission
Systypes user-defined data type
Sysusers user