Three methods to access Microsoft SQL Server metadata

 

Shanghai mini-invasive software Co., Ltd. Xiao guidong

Target readers: Medium and advanced Microsoft SQL Server users

Metadata Overview

The most common definition of metadata (metadata) is the structure data about the data, or simply the information about the data, legends, library directory cards, and business cards in daily life can be viewed as metadata. In the relational database management system (DBMS), metadata describes the structure and significance of data. For example, when managing and maintaining SQL Server or developing database applications, we often need to obtain information related to the database architecture:

1. Number and name of tables and views in a database;
2. The number of columns in a table or view, as well as the name, data type, length, precision, and description of each column;
3. Constraints defined on a table;
4. The index and primary key/foreign key information defined on a table.

The following describes several methods to obtain metadata.

Get metadata

Access metadata using system stored procedures and system functions

The most common way to obtain metadata is to use the system stored procedures and system functions provided by SQL Server.

The system stored procedure and system functions provide an abstraction layer between the system table and metadata, so that you do not have to directly query the table to obtain the metadata of the current database object.

Common Metadata-related system stored procedures include:

Common Metadata-related system functions include the following:

Because we cannot directly use the returned results of stored procedures and functions, we only need to focus on the query results without further using these results, we use system stored procedures and system functions to query metadata.

For example, to obtain basic information about all databases on the current server, run the following command in the query Analyzer:

Exec sp_databases
Go

In the returned results, we can see the database name, size, remarks, and other information.

However, if you want to reference this part of information or store this part of information for later use, you must use the intermediate table to complete this operation:

Create Table # sp_result
(
Database_name sysname,
Database_size int,
Remarks varchar (254) null
)
Go
Insert into # sp_result
Exec (sp_databases)
Go

Access metadata using the information architecture View

Information Architecture views are based on definitions of architecture views in SQL-92 standards that are independent of system tables and provide an internal view of SQL Server metadata. The biggest advantage of the Information Architecture view is that even if we make important changes to the system table, the application can normally use these views for access. So for applications, as long as it is a database system that complies with SQL-92 standards, the use of the information architecture view can always work properly.

Common Information Architecture views include the following:

Because these information architectures exist in the form of views, we can easily obtain and utilize the required information.

For example, to obtain the number of columns in a table, use the following statement:

Select count (*) from information_schema.columns
Where table_name = mytable

Access metadata using system tables

Although the system stored procedures, system functions, and Information Architecture view can provide us with a wealth of metadata information, but for some special metadata information, we still need to directly query the system table. Because SQL server stores the information of all database objects in the system table as the administrator and developer of SQL Server, understanding the functions of various system tables will help us understand the internal working principles of SQL Server.

SQL Server has many system tables. The most common tables related to Metadata Query are as follows:

By combining system stored procedures, system functions, Information Architecture view, and system tables, we can easily obtain all the required metadata information.

Example:
1. Obtain the names of all user tables in the current database.

Select object_name (ID)
From sysobjects
Where xtype = u and objectproperty (ID, ismsshipped) = 0

The system table sysobjects and Its Attribute xtype are used, and the objectproperty system function is used to determine whether the object is created during the installation of SQL Server.

2. Obtain all index names on the specified table.

Select name from sysindexes
Where id = object_id (mytable) and indid> 0

Integrated instance

The following describes a stored procedure, which automatically encrypts the user stored procedure of the current database.

Declare @ sp_name nvarchar (400)
Declare @ sp_content nvarchar (2000)
Declare @ asbegin int
Declare @ now datetime
Select @ now = getdate ()
Declare sp_cursor cursor
Select object_name (ID)
From sysobjects
Where xtype = P
And type = P
And crdate <@ now
And objectproperty (ID, ismsshipped) = 0

Open sp_cursor

Fetch next from sp_cursor
Into @ sp_name

While @ fetch_status = 0
Begin
Select @ sp_content = text from syscomments where id = object_id (@ sp_name)
Select @ asbegin = patindex (% as char (13) %, @ sp_content)
Select @ sp_content = substring (@ sp_content, 1, @ asbegin-1)
With encryption
Substring (@ sp_content, @ asbegin 2, Len (@ sp_content ))
Select @ sp_name = drop procedure [@ sp_name]
Exec sp_executesql @ sp_name
Exec sp_executesql @ sp_content
Fetch next from sp_cursor
Into @ sp_name
End

Close sp_cursor
Deallocate sp_cursor

The stored procedure uses the sysobjects and syscomments tables, cleverly modifies the SQL definition statements of the original stored procedure, and modifies the as to with encryption as, thus achieving the purpose of encrypting the stored procedure. This stored procedure is passed on SQL Server 2000.