SQL Server System table and Its Application

 

Keywords: SQL Server System Table Storage Process

I. SQL Server System Table

Microsoft SQL Server is a scalable and high-performance database management system designed for Distributed Client/Server environments, SQL Server stores almost all configuration information, security information, and object information in its own system table, and the system table exists in each independent database, the system table that stores the information of a specific database object is usually called the database directory. The master database has its own system table used to store the information of the entire system and all databases, usually called the server directory or system directory.

All databases on the server, including module and master, contain 18 system tables with the same name and structure. For example, the table sysobjects is used to describe the objects in the database-tables, views, and stored procedures, table sysuser is used to describe database users, while the master database also has 13 separate global system tables, for example, the table syslogins is used to save the login name, password, and configuration information of each server, and the table sysdatabase stores all the database names, owner, status, and other information on the server.

Stored procedures are embedded in databases.ProgramCodeIt is a component of a database like tables and views. Unlike general external program code, it is a pre-compiled code, therefore, it features fast operation speed and high efficiency. stored procedures are also an important function of SQL Server. Many operations for background databases are generally performed by stored procedures to improve system efficiency. This article provides a stored procedure to describe the application of SQL Server System tables.

Ii. SQL Server System Table Application

In the development of an information system that uses SQL Server Based on the client/server architecture, sometimes the table structure of a database on SQL Server needs to be printed in the background, this allows developers to access and form final documents. SQL Server itself provides a system stored procedure (sp_columns) to complete a query of a single table structure, as long as you type sp_columns "table name" in SQL Server's ISQL-W tool ", run the command to obtain the result set. However, this method has many shortcomings:

1) operations can only be performed on a single table. to query all the tables in a database, execute sp_columns multiple times, so it is very complicated.

2) the query result contains a lot of unnecessary information.

Next, we create a stored procedure to query all the table structures in a database.

When creating a database, the system will automatically create some system tables. Due to space limitations, we will only introduce three system tables (sysobjects, syscolumns, policypes) related to the application instance) and related fields.

Table sysobjects creates a record for each object (constraints, rules, tables, views, triggers, etc.) created in the database.

The meanings of related fields in the table are as follows:

Sysobjects. name indicates the object name, for example, table name and view name.

The ID of the sysobjects. ID object.

Sysobjects. Type object type (P stored procedure, V view, s system table, u User table ).

The syscolumns table creates a record for each column in each table and view and each parameter in each stored procedure.

The meanings of related fields in the table are as follows: (the column here refers to the columns in each table and view in the database)

Syscolumns. ID the ID of the table to which the column belongs, which can be associated with sysobjects. ID

Syscolumns. colid indicates the column ID of a table or view.

Syscolumns. Type: physical storage type, which can be associated with policypes. type.

The physical length of syscolumns. length data.

Syscolumns. Name column name, that is, field name.

Syscolumns. Pre column precision level.

The scale level of the syscolumns. Scale column.

The systypes table creates a record for each system and the data types provided by each user. If they exist, the rows describing the data types provided by the system are not changeable given the domain and default value.

The meanings of related fields in the table are as follows:

Policypes. name indicates the name of the data type.

Policypes. Type physical storage data type.

In SQL Server Enterprise Manager, select a database and create the storage process print_dbstructure.

Source codeAs follows:
If exists (select * From sysobjects where id = object_id ('dbo. print_dbstructure

') And sysstat & 0xf = 4) Stored Procedure

Drop procedure DBO. print_dbstructure

Go

Create procedure print_dbstructure

As

Select distinct sysobjects. Name, syscolumns. colid,
Syscolumns. Name, policypes. Name, syscolumns. prec, syscolumns. Scale
From syscolumns, sysobjects, policypes
Where sysobjects. ID = syscolumns. ID and policypes. type = syscolumns. Type and (sysobjects. type = 'U '))
Go

 

First, determine whether a stored procedure named print_dbstructure exists. If so, remove it. Otherwise, define an SQL statement to create a new stored procedure. Select the matching records from the three system tables (that is, the database contains the user table information in the system table ).

During execution, in the isql_w tool, select the database where print_dbstructure is located and execute the stored procedure to obtain the result set (that is, the structure information of the User table used in the database ).

Iii. SQL Server System Table promotion and application

The main defect of the stored procedure described above is that you can only query a specific database on the server (the database where print_dbstructure is located, we can solve this problem by establishing a system storage process with database name parameters in the master database of SQL Server. Please complete it yourself. Of course, SQL Server System tables are used far more than that. Readers can also use the methods described in this article to mine the applications of several other system tables. Transferred from:Http://www.ithome-cn.net/technology/data/data027.htm