On the function of system table in SQL Server database

(a) system table: stores all system information for SQL. Storing data about metadata in database services, such as what user databases the data server has, what login accounts the database server has, what tables are in the database, what fields each table has stored procedures, views, and so on, and so on, the system table General SYS begins.

(b) Knowledge of the system tables in the database, which can be used when writing SQL statements or programming. The following scenario uses the system table

1, when the database is created to determine whether the existence of the database, the creation of objects (tables, views, stored procedures, indexes, etc.) exist, there is a return, does not exist to execute the creation statement.

2, the bulk deletion of the database objects, such as a one-time deletion of a specific database of all users created tables, views, indexes and other objects, you can query the system table objects and then use the SQL statement to control the deletion.

(iii) A few important system tables

Sysxlogins: exists in the master database (users and roles in all databases) and records all accounts that can log on to SQL Server. Because the system table is not allowed to manually delete, if you want to delete the execution sp_configure ' allow update ', 1

will not take effect immediately, restart service or reconfigure with override

sysdatabases: Records all databases of the current system. This system table is only available in master data.

Systypes (per database): Store the default user types and user-defined user types in the system.

Sysusers: Records the 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.

(iv) Know the system tables in the database, you can write SQL statements or in the development process through SQL statements to complete the following tasks

1. What databases are in the query database

SELECT * FROM Dbo.sysdatabases

2, query users create a database in which users create tables

SELECT * from sysobjects where xtype= ' U '

3. Querying which user-created stored procedures in a user-created database

SELECT * from sysobjects where xtype= ' P '

4. Batch generate SQL statements that delete all user tables in the database

Select ' DROP table ' + name from sysobjects where xtype= ' U '

The above mentioned, master Database system table, for our SQL programming has a great help