Several methods for obtaining SQL Server database metadata: SQL metadata

Several methods for obtaining SQL Server database metadata: SQL metadata

Metadata Overview

The most common definition of metadata (metadata) is "structured data about data", or simply "information about 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:

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

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:

System stored procedures

◆ Sp_columns: return the detailed information of the columns in the specified table or view.
◆ Sp_databases: returns the basic information of all databases on the current server.
◆ Sp_fkeys if the parameter is a table with a primary key, all tables containing the foreign key pointing to the table are returned. If the parameter is a table with a foreign key, returns all tables associated with the foreign key.
◆ Sp_pkeys: The primary key information of the specified table is returned.
◆ Sp_server_info: various features of the current server and their corresponding values are returned.
◆ Sp_sproc_columns: return the input and output parameters of the specified stored procedure.
◆ Sp_statistics: return all indexes and statistical information on the specified table or index view.
◆ Sp_stored_procedures: return the list of Stored Procedures of the current database, including system stored procedures.
◆ Sp_tables: returns all tables and views of the current database, including system tables.

Common Metadata-related system functions include the following:

System Functions

◆ COLUMNPROPERTY returns information about columns or process parameters, such as whether null values are allowed and whether columns are computed.
◆ COL_LENGTH: return the specified attribute value of the specified database, such as whether the database is in read-only mode.
◆ DATABASEPROPERTYEX returns the current settings of the specified options or attributes of the specified database, such as the database status and recovery model.
◆ OBJECT_ID: return the ID number of the specified database object name
◆ OBJECT_NAME: return the Object Name of the specified database object identification number.
◆ OBJECTPROPERTY returns information about the ID of the specified database object, such as whether it is a table or a constraint.
◆ Fn_listextendedproperty: return the extended property value of the database object, such as the object description, format rules, and input mask.

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)GOINSERT INTO #sp_resultEXEC ('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.

Information Architecture View

◆ INFORMATION_SCHEMA.CHECK_CONSTRAINTS: returns information about columns or process parameters, such as whether null values are allowed and whether columns are computed.
◆ INFORMATION_SCHEMA.COLUMNS: returns all columns and basic information that can be accessed by the current user in the current database.
◆ INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE: returns all columns with constraints defined in the current database and their constraint names.
◆ INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE: returns all tables with constraints defined in the current database and their constraint names.
◆ INFORMATION_SCHEMA.KEY_COLUMN_USAGE: returns all columns in the current database as primary key/foreign key constraints.
◆ INFORMATION_SCHEMA.SCHEMATA: returns all databases with permissions and basic information of the current user.
◆ INFORMATION_SCHEMA.TABLES: returns all tables, views, and basic information in the current database that the current user has permissions on.
◆ INFORMATION_SCHEMA.VIEWS: returns the views that can be accessed by the current user in the current database, their owners, definitions, and other information.

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:
Syscolumns stores the information of each column in each table and view and the information of each parameter in the stored procedure.
Syscomments stores the original SQL text statements that contain each view, rule, DEFAULT value, trigger, CHECK constraint, DEFAULT constraint, and stored procedure.
Sysconstraints stores basic information about each constraint in the current database.
Sysdatabases stores the basic information of each database on the current server.
Sysindexes stores information about each index in the current database.
Sysobjects stores basic information about each object in the database (constraints, default values, logs, rules, stored procedures, etc.
Sysreferences stores all columns that contain the foreign key constraint.
The policypes storage system provides detailed information about each data type and user-defined data type.

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 sysobjectsWHERE 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 the names of all indexes 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 intdeclare @now datetimeselect @now = getdate()DECLARE sp_cursor CURSOR FORSELECT object_name(id)FROM sysobjectsWHERE xtype = 'P'AND type = 'P'AND crdate < @nowAND OBJECTPROPERTY(id, 'IsMSShipped')=0OPEN sp_cursorFETCH NEXT FROM sp_cursorINTO @sp_nameWHILE @@FETCH_STATUS = 0BEGINSELECT @sp_content = text FROMsyscomments 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 AS'+ SUBSTRING (@sp_content, @asbegin+2, LEN(@sp_content))SELECT @sp_name = 'DROP PROCEDURE [' + @sp_name + ']'EXEC sp_executesql @sp_nameEXEC sp_executesql @sp_contentFETCH NEXT FROM sp_cursorINTO @sp_nameENDCLOSE sp_cursorDEALLOCATE 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.