Since there are recent things to use this, I have found some articles in this area. In order to facilitate viewing, they are included here, but they have retained the original author information.
Original address: http://www.microsoft.com/china/technet/columns/tipsfortechs/wsqs3.asp
Shanghai Mini Software Co., Ltd. Xiao Guidong
Applicable readers: Microsoft SQL Server, senior users
Data introduction
Metadata is most common to define "structural data about data", or simple little is "information about data", the legend in daily life, library catalog card and business cards can be seen as metadata . In the relational database management system (DBMS), metadata describes the structure and significance of the data. For example, when management, maintain SQL Server or develop database applications, we often have to get some information involved in the database architecture:
The number and name of the table and view in a database; the number of lists or views in a table or the name, data type, length, accuracy, description, etc. of each column; a constraint defined on a table; Table defined indexes and information on the primary key / foreign key.
Below we will introduce several ways to get metadata.
Get metadata
Use the system stored procedure and system function to access metadata
The most common way to get metadata is to use the system stored procedures and system functions provided by SQL Server.
The system stored procedures and system functions provide an abstraction layer between system tables and metadata, so that we can obtain metadata of the current database object without direct query system tables.
Some system stored procedures related to metadata have the following:
System stored procedure description sp_columns Returns the details of the column of the specified table or view. Sp_Databases returns basic information about all databases on the current server. If the sp_fkeys is a table with the primary key, it returns all the tables that contain the foreign key to the table; if the parameter is a table name with foreign key, return all the main key / foreign key relationship with the foreign key All tables. Sp_pkeys returns primary key information for the specified table. Sp_server_info Returns Various Characteristics of the Current Server and its corresponding value. Sp_sproc_columns returns the information of the input and output parameters of the specified stored procedure. Sp_statistics returns all indexes and statistics on the specified table or index view. sp_stored_procedures returns a list of stored procedures for the current database that contains the system stored procedure. Sp_tables Returns all tables and views of the current database, including system tables.
Some commonly used system functions related to metadata:
System function describes ColumnProperty Returns information about column or process parameters, such as whether or not no null value is allowed, whether it is computational column. COL_LENGTH Returns the specified property value of the specified database, if it is in read-only mode, etc. DatabasePropertyEx Returns the current settings of the specified options or attributes of the specified database, such as the status of the database, recovery model, etc. Object_id Returns the identification number Object_name of the specified database object name Returns the name of the object identification number of the specified database object identification number. ObjectProperty returns information about the specified database object identification number, if it is a table, whether it is constrained. FN_ListextendedProperty returns the extension attribute value of the database object, such as object description, format rule, input mask, etc.
Since we cannot use the return result of the stored procedure and function, only the results of the query is only the result of the query, without further utilization of these results, we use the system stored procedure to query the metadata.
For example, if you want to get basic information on all databases on the current server, we can run in the query analyzer:
EXEC SP_DATABASESGO
In the return result, we can see information such as name, size, and note of the database.
But if you want to reference this part of the information, or store this part of the information for later use, then we must complete this action with an intermediate table: create Table #sp_result (Database_name sysname, database_size int, remarks varchar (254) null) GoInsert Into # Sp_Result EXEC ('sp_databases') Go
Use information architecture view to access metadata
The information architectural view is based on the definition of architectural views based on SQL-92 standards, which provides internal views on SQL Server metadata based on the system table. The biggest advantage of the information architectural view is that even if we have important modifications to the system table, the application can access these views normally. Therefore, for the application, as long as it is a database system that conforms to the SQL-92 standard, the use of the information architectural view can always work normally.
Common information architecture views have some:
Information Architecture View Description Information_SChema .Check_constraint returns information about column or process parameters, if a null value is allowed, whether it is computational column. INFORMATION_SCHEMA .COLUMNS Returns all columns and basic information you can access in the current database. Information_schema .constraint_column_usage Returns all columns and constraints of constraints in the current database. INFORMATION_SCHEMA .CONSTRAINT_TABLE_USAGE Returns all tables that define constraints in the current database and their constraints. Information_schema .key_column_usage Returns all columns as the primary key / foreign key constraint in the current database. Information_schema .schemata Returns all databases and basic information of the current user with permissions. INFORMATION_SCHEMA .TABLES Returns all tables or views and their basic information in the current database with privileges. Information_schema .Views Returns the view of the current user in the current database and its owner, definition, and other information.
Since these information architectures are present in a view, we can easily obtain and utilize information requested.
For example, we have to get how many columns of a table, you can use the following statement:
SELECT Count (*) from information_schema.columns where table_name = 'myTable'
Use system table to access metadata
While using the system stored procedure, the system function and information architectural view can have provided us with considerable metadata information, but for some special metadata information, we still need to query the system table directly. Because SQL Server stores all database objects in the system table, as SQL Server management, developers, understand the role of each system table will help us understand the inherent work of SQL Server.
SQL Server system tables are large, and the most commonly used forms related to metadata queries are as follows:
The system table describes the information of each of each column in each table and view of each table and view in the store. The SysComments stores the original SQL text statement containing each view, rule, default, trigger, check constraint, default constraint, and stored procedures. SysConstraints stores basic information about each constraint in the current database. SysDatabases stores basic information for each database on the current server. SysIndexes stores information about each index in the current database. SysObjects stores basic information for each object (constraint, default, log, rule, storage procedure, etc.) within the database. SysReferences stores all columns that include Foreign Key constraints. The SYSTYPES storage system provides details of each data type and user-defined data type. Combining system stored procedures, system functions, information schema views, and system tables, which can easily get all of the required metadata information.
Example: 1. Get the name of all user tables for the current database.
Select Object_name (ID) from sysobjectswhere xtype = 'u' and Objectproperty (ID, 'ismshipped') = 0
It is mainly used to system table sysObjects and its attribute Xtype, and it is to use the objectProperty system function to determine whether the object created during the process of installing SQL Server.
2. Get all index names on the specified table
Select name from sysindexeswhere id = Object_id ('mytable') and indid> 0
Integrated example
A stored procedure is given below, and its role is to automatically encrypt 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 FOR SELECT object_name (id) FROM sysobjectsWHERE xtype = 'P' AND type = 'P' AND CRDATE <@nowand objectproperty (ID, 'ismshipped ") = 0
Open sp_cursor
Fetch next from sp_cursor into @sp_name
WHILE @@ FETCH_STATUS = 0BEGINSELECT @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_ContentFetch next from sp_cursor @sp_nameeendclose sp_cursor deallocate sp_cursor
The stored procedure uses the sysObjects and syscomments tables, and cleverly modifies the SQL definition statement of the original store, and modifies the AS to with Encryption AS to achieve the purpose of the encrypted stored procedure. This stored procedure is passed on SQL Server 2000.
Reference
Microsoft SQL Server 2000 Books Http://www.microsoft.com/china/sqlhtp://msdn.microsoft.com/library/default.asp?url=/nhp/default.asp?contentid=28000409
