Introduction to metadata
The most common definition of metadata is “structural data about data”, or simply “information about data”. Legends, library catalog cards and business cards in daily life can be regarded as metadata. In relational database management system (DBMS), metadata describes the structure and meaning of data. For example, when managing and maintaining SQL server or developing database applications, we often need to obtain some information related to database architecture
Number and name of tables and views in a database;
The number of columns in a table or view and the name, data type, length, precision and description of each column;
Constraints defined on a table;
Index defined on a table and primary / foreign key information.
Next, we will introduce several ways to get metadata.
Acquiring Metadata
Using system stored procedures and system functions to access metadata
The most common way to get metadata is to use the system stored procedures and system functions provided by SQL server.
System stored procedures and system functions provide an abstraction layer between system tables and metadata, so that we can get metadata of current database objects without directly querying system tables.
The commonly used system stored procedures related to metadata are as follows:
System stored procedure
◆sp_ Columns returns the details of the columns of 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 the tables containing the foreign key pointing to the table are returned; if the parameter is a table name with a foreign key, all the tables associated with the foreign key through the primary key / foreign key relationship are returned.
◆sp_ Pkeys returns the primary key information of the specified table.
◆sp_ server_ Info returns various characteristics of the current server and their corresponding values.
◆sp_ sproc_ Columns returns the input and output parameters of the specified stored procedure.
◆sp_ Statistics returns all indexes and statistics on the specified table or indexed view.
◆sp_ stored_ Procedures returns the list of stored procedures in the current database, including system stored procedures.
◆sp_ Tables returns all tables and views of the current database, including system tables.
The commonly used system functions related to metadata are as follows:
System function
Column property returns information about column or procedure parameters, such as whether null value is allowed, whether it is a calculated column, etc.
◆COL_ Length returns the specified attribute value of the specified database, such as whether it is in read-only mode or not.
Database propertyex returns the current settings of the specified options or attributes of the specified database, such as the state of the database and the recovery model.
◆OBJECT_ ID returns the identification number of the specified database object name
◆OBJECT_ Name returns the object name of the specified database object ID.
Objectproperty returns the information about the ID number of the specified database object, such as whether it is a table, whether it is a constraint, etc.
◆fn_ Listening property returns the extended property values of database objects, such as object description, format rule, input mask, etc.
Because we can’t directly use the returned results of stored procedures and functions, we will use system stored procedures and functions to query metadata only when we care about the query results and don’t need to further use these results.
For example, to obtain the basic information of all databases on the current server, we can run it in the query analyzer
EXEC sp_databases
GO
In the returned results, we can see the name, size and remarks of the database.
But if we want to refer to this part of information, or store this part of information for later use, we 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')
GOUsing information architecture view to access metadata
Information architecture view is based on the definition of architecture view in SQL-92 standard. These views are independent of system tables and provide internal views about SQL Server metadata. The biggest advantage of information architecture view is that even if we make important changes to the system table, the application can use these views to access normally. Therefore, for the application, as long as it is in line with the SQL-92 standard database system, the use of information architecture view can always work normally.
Information architecture view
◆INFORMATION_ SCHEMA.CHECK_ Constraints: returns information about column or procedure parameters, such as whether null values are allowed, whether calculated columns are allowed, etc.
◆INFORMATION_ SCHEMA.COLUMNS : returns all the columns and their basic information that the current user can access in the current database.
◆INFORMATION_ SCHEMA.CONSTRAINT_ COLUMN_ Usage: returns all columns in the current database with constraints defined and their constraint names.
◆INFORMATION_ SCHEMA.CONSTRAINT_ TABLE_ Usage: returns all tables in the current database with constraints defined and their constraint names.
◆INFORMATION_ SCHEMA.KEY_ COLUMN_ Usage: returns all columns in the current database that are used as primary / foreign key constraints.
◆INFORMATION_ SCHEMA.SCHEMATA : returns all the databases that the current user has permissions and their basic information.
◆INFORMATION_ SCHEMA.TABLES : returns all tables or views and their basic information in the current database with the permission of the current user.
◆INFORMATION_ SCHEMA.VIEWS : returns the view and its owner, definition and other information that the current user can access in the current database.
Because these information architectures exist in the form of views, we can easily obtain and use the information we need.
For example, to find out how many columns there are in a table, we can use the following statement:
SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME=’mytable’
Using system tables to access metadata
Although the use of system stored procedures, system functions and information architecture view can provide us with quite rich metadata information, we still need to query the system table directly for some special metadata information. Because SQL server stores the information of all database objects in the system table, as the administrator and developer of SQL server, understanding the function of each system table will help us understand the internal working principle of SQL server.
There are many system tables in SQL server, among which the most commonly used tables related to metadata query are as follows:
Syscolumns stores the information of each column in each table and view, as well as 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 the basic information of 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 the basic information of each object (constraints, default values, logs, rules, stored procedures, etc.) in the database.
Sysreferences stores all columns including foreign key constraints.
Systypes stores the details of each data type and user-defined data type provided by the system.
By combining system stored procedure, system function, information architecture view with system table, we can easily get all the required metadata information.
Example:
1. Gets the names of all user tables in the current database.
SELECT OBJECT_NAME (id)
FROM sysobjects
WHERE xtype = 'U' AND OBJECTPROPERTY (id, 'IsMSShipped') = 0The system table sysobjects and its attribute xtype are mainly used, and the objectproperty system function is used to determine whether the object is created in the process of installing SQL server.
2. Gets the names of all indexes on the specified table.
SELECT name FROM sysindexes
WHERE id = OBJECT_ID (‘mytable’) AND indid > 0
Comprehensive examples
The following is a stored procedure, whose function is to automatically encrypt 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 FOR
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 AS'
+ 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_cursorThe stored procedure makes use of the sysobjects and syscomments tables, and cleverly modifies the SQL definition statement of the original stored procedure, changing as to with encryption as, so as to achieve the purpose of encrypting the stored procedure. This stored procedure is implemented on SQL Server 2000.