Using the information Schema view to access database metadata __ Database

Introduction to meta-data

The most common definition of metadata (metadata) is "structural data about data," or, more simply, "information about data," which can be viewed as metadata in everyday life, such as illustrations, library catalogs, and business cards. In relational database management system (DBMS), metadata describes the structure and significance of data. For example, in management, when maintaining SQL Server or developing database applications, we often get information about the database schema: the number and names 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; A constraint defined on a table, the index defined on a table, and the information for a primary key/foreign key.

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 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 normally for access. So for an application, the use of an information Schema view can always work as long as it is a database system that conforms to the SQL-92 standard.

The common information schema views are as follows:

Information Schema View	description
in Formation_schema. Check_constraints	Returns information about a column or procedure parameter, such as whether to allow null values, computed columns, and so on.
information_schema. COLUMNS	returns all columns and their basic information that the current user can access in the current database.
information_schema. Constraint_column_usage	Returns all the columns with constraints defined in the current database and their constraint names.
information_schema. Constraint_table_usage	Returns all the tables and their constraint names for which the constraint is defined in the current database.
information_schema. Key_column_usage	Returns all columns in the current database that are constraints as primary key/foreign key.
information_schema. Schemata	returns all databases and their basic information for which the current user has permissions.
information_schema. Tables	Returns all tables or views and their basic information in the current database to which the current user has permissions.
information_schema. The views	returns information such as the view and its owner, definition, and so on that the current user in the current database can access.

Because these information architectures are all in the form of views, we can easily get and take advantage of the information we need.

So it is easy to use database metadata information to achieve ORM function, the following is a few commonly used SQL

Get all columns of a table
Public Const string Table_column_sql = "Select Table_catalog as [Database], table_schema as Owner, table_name as TABL ename, column_name as ColumnName, "+
"Ordinal_position as OrdinalPosition, Column_default as defaultsetting, is_nullable as IsNullable, DATA_TYPE as DataType, " +
"Character_maximum_length as MaxLength, numeric_precision as PRECISION, datetime_precision as dateprecision" +
"From INFORMATION_SCHEMA." COLUMNS "+
"WHERE (table_name = @tblName)";

Get all stored procedures and parameters for a table
Public const string Sp_param_sql= "SELECT specific_catalog as [Database], Specific_schema as Owner, specific_name as SP Name, ordinal_position as OrdinalPosition, "+
"Parameter_mode as Paramtype, Is_result as Isresult, parameter_name as NAME, data_type as DataType," +
"Character_maximum_length as Datalength, REPLACE (parameter_name, ' @ ', ') as Cleanname" +
"From INFORMATION_SCHEMA." PARAMETERS "+
"WHERE specific_name= @spName";

       //Get SQL for all stored procedures of the table
          Public const string sp_sql = " SELECT     specific_catalog as [Database], Specific_ SCHEMA as Owner, specific_name as NAME, routine_definition as SQL, CREATED as CreatedOn, "+
    &N bsp;                      "last_altered as ModifiedOn" +
                           "from          information_schema. Routines "+
                            "where    " (Specific_name = @ Spname) ";

     //Get all stored procedures for a table  
      Public const string Table_ SQL = "select     table_catalog as [Database], table_schema as Owner, table_name as NAME, Table_type" +
             "from          information_schema. TABLES "+
               " WHERE      (table_type = ' BASE TABLE ') and (table_name <> N ' sysdiagrams ') "+
   & nbsp;         "and Table_name= @tblName";