Obtain the metadata of a database from a system table.

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, you can manage and maintain SQL Server or develop database applications.ProgramWe 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;

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:

System stored procedure description

Sp_columns returns the details 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 returns the primary key information of the specified table.
Sp_server_info returns the features of the current server and their 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 index view.
Sp_stored_procedures returns 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 Function Description

Columnproperty returns information about columns or process parameters, such as whether null values are allowed and whether columns are computed.
Col_length returns 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: returns the ID of the specified database object name.
Object_name: returns 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 returns the extended attribute values of database objects, such as object descriptions, format rules, and input masks.

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:

Description of Information Architecture View

Information_schema.check_constraints returns information about columns or process parameters, such as whether to allow null values and whether they are computed columns.
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:

System table description

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 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.

-------------------------SQL Server MS-----------------------
-- Table description

Select DBO. sysobjects. Name as tablename,
DBO. sysproperties. [value] As tabledesc
From DBO. sysproperties inner join
DBO. sysobjects on DBO. sysproperties. ID = DBO. sysobjects. ID
Where (DBO. sysproperties. smallid = 0)
Order by DBO. sysobjects. Name

-- Field description

Select DBO. sysobjects. Name as tablename, DBO. syscolumns. colid,
DBO. syscolumns. Name as colname, DBO. sysproperties. [value] As coldesc
From DBO. sysproperties inner join
DBO. sysobjects on DBO. sysproperties. ID = DBO. sysobjects. ID inner join
DBO. syscolumns on DBO. sysobjects. ID = DBO. syscolumns. ID and
DBO. sysproperties. smallid = DBO. syscolumns. colid
Order by DBO. sysobjects. Name, DBO. syscolumns. colid

 

-- Primary key and foreign key information (simplified)

Select
C_obj.name as constraint_name
, T_obj.name as table_name
, Col. Name as column_name
, Case col. colid
When ref. fkey1 then 1
When ref. fkey2 then 2
When ref. fkey3 then 3
When ref. fkey4 then 4
When ref. fkey5 then 5
When ref. fkey6 then 6
When ref. fkey7 then 7
When ref. fkey8 then 8
When ref. fkey9 then 9
When ref. fkey10 then 10
When ref. fkey11 then 11
When ref. fkey12 then 12
When ref. fkey13 then 13
When ref. fkey14 then 14
When ref. fkey15 then 15
When ref. fkey16 then 16
End as ordinal_position
From
Sysobjects c_obj
, Sysobjects t_obj
, Syscolumns col
, Sysreferences ref
Where
Permissions (t_obj.id )! = 0
And c_obj.xtype in ('F ')
And t_obj.id = c_obj.parent_obj
And t_obj.id = col. ID
And col. colid in
(Ref. fkey1, ref. fkey2, ref. fkey3, ref. fkey4, ref. fkey5, ref. fkey6,
Ref. fkey7, ref. fkey8, ref. fkey9, ref. fkey10, ref. fkey11, ref. fkey12,
Ref. fkey13, ref. fkey14, ref. fkey15, ref. fkey16)
And c_obj.id = ref. constid
Union
Select
I. Name as constraint_name
, Maid as table_name
, Col. Name as column_name
, V. Number as ordinal_position
From
Sysobjects c_obj
, Sysobjects t_obj
, Syscolumns col
, Master. DBO. spt_values v
, Sysindexes I
Where
Permissions (t_obj.id )! = 0
And c_obj.xtype in ('uq', 'pk ')
And t_obj.id = c_obj.parent_obj
And _ obj.xtype = 'U'
And t_obj.id = col. ID
And col. Name = index_col (t_obj.name, I. indid, V. Number)
And t_obj.id = I. ID
And c_obj.name = I. Name
And v. Number> 0
And v. Number <= I. keycnt
And v. type = 'P'

Order by constraint_name, ordinal_position

-- Primary key and foreign key comparison (simplified)

Select
Fc_obj.name as constraint_name
, I. Name as unique_constraint_name
From
Sysobjects fc_obj
, Sysreferences R
, Sysindexes I
, Sysobjects pc_obj
Where
Permissions (fc_obj.parent_obj )! = 0
And fc_obj.xtype = 'F'
And R. constid = fc_obj.id
And R. rkeyid = I. ID
And R. rkeyindid = I. indid
And R. rkeyid = pc_obj.id

 

-------------------Oracle-----------------------

-- Table information

Select * From all_tab_comments t
Where owner = 'dbo'

-- Column information

Select * From all_col_comments t
Where owner = 'dbo'

-- Primary key and foreign key comparison

Select owner, constraint_name, constraint_type, table_name, r_owner, r_constraint_name
From all_constraints
Where owner = 'dbo' and (constraint_type = 'P' or constraint_type = 'R ')

-- Primary key and foreign key information

Select *
From all_cons_columns
Where owner = 'dbo'
Order by constraint_name, position

------------------Access--------------------------
// The system table msysobjects in access stores the fields in binary format and cannot be directly analyzed.
// You can use the openschema method provided by ADO to obtain relevant information.

// Use adoint. Pas
// Po: tablename
// Dbcon: tadoconnection
/DS: TADODataSet

-- Table information
 

Dbcon. openschema (sitables, vararrayof ([null, null, 'table']), emptyparam, DS );

-- Column information

Dbcon. openschema (sicolumns, vararrayof ([null, null, 'po']), emptyparam, DS );

-- Primary key

Dbcon. openschema (siprimarykeys, emptyparam, emptyparam, DS );

-- Primary key and foreign key comparison

Dbcon. openschema (siforeignkeys, emptyparam, emptyparam, DS );

 

From: http://www.cnblogs.com/basibasi/archive/2007/11/29/976544.html