使用信息架构视图访问数据库元数据
元数据简介
元数据 (metadata) 最常见的定义为"有关数据的结构数据"，或者再简单一点就是"关于数据的信息"，日常生活中的图例、图书馆目录卡和名片等都可以看作是元数据。在关系型数据库管理系统 (DBMS) 中，元数据描述了数据的结构和意义。比如在管理、维护 SQL Server 或者是开发数据库应用程序的时候，我们经常要获取一些涉及到数据库架构的信息：
- 某个数据库中的表和视图的个数以及名称 ；
- 某个表或者视图中列的个数以及每一列的名称、数据类型、长度、精度、描述等；
- 某个表上定义的约束；
- 某个表上定义的索引以及主键/外键的信息。 信息架构视图基于 SQL-92 标准中针对架构视图的定义，这些视图独立于系统表，提供了关于 SQL Server 元数据的内部视图。信息架构视图的最大优点是，即使我们对系统表进行了重要的修改，应用程序也可以正常地使用这些视图进行访问。因此对于应用程序来说，只要是符合 SQL-92 标准的数据库系统，使用信息架构视图总是可以正常工作的。
常用的信息架构视图有以下一些：
信息架构视图 | 描述 |
|---|---|
INFORMATION_SCHEMA .CHECK_CONSTRAINTS | 返回有关列或过程参数的信息，如是否允许空值，是否为计算列等。 |
INFORMATION_SCHEMA .COLUMNS | 返回当前数据库中当前用户可以访问的所有列及其基本信息。 |
INFORMATION_SCHEMA .CONSTRAINT_COLUMN_USAGE | 返回当前数据库中定义了约束的所有列及其约束名。 |
INFORMATION_SCHEMA .CONSTRAINT_TABLE_USAGE | 返回当前数据库中定义了约束的所有表及其约束名。 |
INFORMATION_SCHEMA .KEY_COLUMN_USAGE | 返回当前数据库中作为主键/外键约束的所有列。 |
INFORMATION_SCHEMA .SCHEMATA | 返回当前用户具有权限的所有数据库及其基本信息。 |
INFORMATION_SCHEMA .TABLES | 返回当前用户具有权限的当前数据库中的所有表或者视图及其基本信息。 |
INFORMATION_SCHEMA .VIEWS | 返回当前数据库中的当前用户可以访问的视图及其所有者、定义等信息。 |
由于这些信息架构都是以视图的方式存在的，因此我们可以很方便地获得并利用需要的信息。 所以很容易的利用数据库元数据信息实现ORM功能，下面是几个常用的SQL
//获取表的所有列
public const string TABLE_COLUMN_SQL = " SELECT TABLE_CATALOG AS [Database], TABLE_SCHEMA AS Owner, TABLE_NAME AS TableName, 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) ";
//获取表的所有存储过程和参数
public const string SP_PARAM_SQL="SELECT SPECIFIC_CATALOG AS [Database], SPECIFIC_SCHEMA AS Owner, SPECIFIC_NAME AS SPName, 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";
//获取表的所有存储过程的Sql
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, " +
"LAST_ALTERED AS ModifiedOn " +
"FROM INFORMATION_SCHEMA.ROUTINES " +
"WHERE (SPECIFIC_NAME = @spName) ";
//获取表的所有存储过程
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') " +
"AND TABLE_NAME=@tblName";
腾讯云开发者
