Functions of system tables in SQL Server databases

(1) system table: stores all SQL system information. The metadata stored in the database service is called metadata (for example, the user databases on the data server, the login accounts on the database server, and

(1) system table: stores all SQL system information. The metadata stored in the database service is called metadata (for example, the user databases on the data server, the login accounts on the database server, and

(1) system table: stores all SQL system information. 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.

(2) understand the system tables in the database, which can be used when writing SQL statements or programming. System tables are used in the following scenarios:

1. When creating a database, select whether the database exists; whether the objects (tables, views, stored procedures, indexes, etc.) in the database to be created exist, and whether the website space exists, if the statement does not exist, run the create statement.

2. Delete objects in a database in batches. For example, you can delete tables, views, indexes, and other objects created by all users in a specific database at a time. You can query the objects in a table and then use SQL statements to control the deletion.

(3) important system tables

Sysxlogins: it exists in the Master database and has no record filing space (users and roles in all databases). It records all accounts that can log on to SQL server. Because the system table cannot be deleted manually. to delete the table, execute sp_configure 'Allow Update', 1

Will not take effect immediately. Restart the service or reconfigure with override

Sysdatabases: records all databases in the current system. This system table is only available in Master data.

Policypes (available in each database): stores the default user types and user-defined user types in the system.

Sysusers: records users and roles in each database.

Sysobjects: each object (constraint, default, log, rule, stored procedure) created in the database occupies one row in the table.

(4) knowing the system tables in the database, you can complete the following tasks by writing SQL statements or using SQL statements during development:

1. query the databases in the database

Select * from dbo. sysdatabases

2. query tables created by users in the database.

Select * from Sysobjects where xtype = 'U'

3. query the stored procedures created by users in the databases created by users.

Select * from Sysobjects where xtype = 'P'

4. Generate SQL statements to delete all user tables in the database in batches

Select 'drop table' + name from Sysobjects where xtype = 'U'

Understanding the system tables and virtual hosts in the database as described above is of great help for SQL programming.

Advertisement