Go: Three ways to access Microsoft SQL Server metadata

Shanghai Microport Software Co., Ltd. Xiaoguidong

for readers: Microsoft SQL Server, advanced users

Introduction to Meta data

metadata (metadata) is most commonly defined as "structural data about data", or, more simply, "information about data", which can be seen as metadata for everyday legends, library catalogs, and business cards. In the relational database management system (DBMS), metadata describes the structure and significance of the data. For example, when managing, maintaining, or developing a database application, we often have to get some information that involves the database schema:

The 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, description, etc. of each column;

a constraint defined on a table;

The index defined on a table and the information for the primary key/foreign key.

Here are a few ways to get meta data.

get Meta Data

accessing metadata using system stored procedures and system functions

The most common way to get metadata is to use 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, which allows us to obtain the metadata of the current database object without querying the system tables directly.

common metadata-related system stored procedures include the following:


System Stored Procedure description
Sp_columns Returns the details of the column for the specified table or view.
Sp_databases returns basic information for all databases on the current server.
Sp_fkeys If the parameter is a table with a primary key, all tables that contain a foreign key to the table are returned, and if the parameter is a table name with a foreign key, all tables associated with the foreign key are returned for all the same primary key/foreign key relationships.
Sp_pkeys Returns the primary key information for the specified table.
Sp_server_info Returns the various features of the current server and their corresponding values.
Sp_sproc_columns returns information about the input and output parameters of the specified stored procedure.
Sp_statistics Returns all indexes on the specified table or indexed view, as well as statistical information.
Sp_stored_procedures Returns a list of stored procedures for the current database, including system stored procedures.
Sp_tables returns all the tables and views of the current database, including system tables.

common metadata-related system functions include the following:

system Function Description
ColumnProperty returns information about a column or procedure parameter, such as whether a null value is allowed, whether it is a computed column, and so on.
Col_length Returns the specified property value for the specified database, such as whether it is in read-only mode, and so on.
DATABASEPROPERTYEX returns the current settings for the specified option or property for the specified database, such as the state of the database, the recovery model, and so on.
OBJECT_ID Returns the identification number of the specified database object name
object_name returns the object name of the specified database object identification number.
ObjectProperty returns information about the specified database object identification number, such as whether it is a table, whether it is a constraint, and so on.
Fn_listextendedproperty returns extended property values for database objects, such as Object description, formatting rules, input masks, and so on.

since we cannot directly take advantage of the return results of stored procedures and functions, we use system stored procedures and system functions to query the metadata only when we are concerned with the results of the query and do not need to make further use of these results.

For example, if you want to get basic information about all the databases on the current server, we can run them in Query Analyzer:

EXEC sp_databases
GO

in the returned results we can see the database name, size and comments and other information.

But if you want to refer to this piece of information, or store this part of the information for later use, we have to do this with an intermediate table:

CREATE TABLE #sp_result
(
database_name sysname,
database_size int,
REMARKS varchar (254) NULL
)
GO
INSERT into #sp_result
EXEC (' sp_databases ')
GO


accessing metadata using the information Schema View
The information Schema view is based on the definition of the Schema view in the SQL-92 standard, which is independent of the system tables and provides an internal view of the SQL Server metadata. The great advantage of the information architecture view is that even if we make significant changes to the system tables, the application can use these views to access them normally. Therefore, for an application, the use of the information Schema View will always work as long as the database system conforms to the SQL-92 standard.

Common Information Schema views have the following:


Information Schema View Description
information_schema. Check_constraints returns information about a column or procedure parameter, such as whether a null value is allowed, whether it is a computed column, and so on.
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 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 for the current user and their basic information.
information_schema. TABLES returns all the tables or views and their basic information in the current database that the current user has permissions for.
information_schema. Views returns information about the view and its owner, definition, and so on, that the current user can access in the current database.

because these information architectures exist as views, we can easily obtain and leverage the information we need.

For example, to get how many columns a table has, you can use the following statement:

SELECT COUNT (*) from INFORMATION_SCHEMA. COLUMNS
WHERE table_name= ' mytable '

using system tables to access meta data

Although the use of system stored procedures, system functions and information Schema View has been able to provide us with a considerable amount of metadata information, but for some special metadata information, we still need to directly query the system tables. Because SQL Server stores the information for all database objects in the system tables, as a management and developer of SQL Server, understanding the role of the individual system tables will help us understand how SQL Server works internally.

There are a number of SQL Server system tables, the most commonly used for metadata query related tables are the following:


system table Description
syscolumns stores information for each column in each table and view, and for each parameter in the stored procedure.
syscomments Stores the original SQL text statements that contain each view, rule, default value, Trigger, CHECK constraint, defaults constraint, and stored procedure.
Sysconstraints Stores basic information about each constraint in the current database.
sysdatabases stores basic information about each database on the current server.
sysindexes stores information for each index in the current database.
sysobjects stores basic information about each object within the database (constraints, defaults, logs, rules, stored procedures, and so on).
sysreferences stores all columns that include the FOREIGN KEY constraint.
The systypes Storage System provides detailed information about each data type and user-defined data type.

the use of system stored procedures, system functions, information schema views, and system tables makes it easy to get all the metadata information we need.

Example:
1. Obtain the name of all user tables for the current database.

SELECT object_name (ID)
From sysobjects
WHERE xtype = ' U ' and objectproperty (ID, ' ismsshipped ') = 0

The main use of the system table sysobjects and its properties xtype, there is a objectproperty system function to determine whether to install SQL Server in the process of creating objects.

2. Get all index names on the specified table

SELECT name from sysindexes
WHERE id = object_id (' mytable ') and indid > 0


Comprehensive Examples

here is a stored procedure that automatically encrypts the user stored procedure of the current database.

DECLARE @sp_name nvarchar (+)
DECLARE @sp_content nvarchar (+)
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 '
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 (+ '% '), @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_cursor

the stored procedure takes advantage of the sysobjects and syscomments tables and subtly modifies the SQL definition statement of the original stored procedure, modifying as to with encryption as, thus achieving the purpose of encrypting the stored procedure. This stored procedure is passed on SQL Server 2000.

Three ways to access Microsoft SQL Server metadata