如何获取SQL Server数据库元数据[转]

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,基础系列 2核4GB
简介:
from :  http://www.chinaz.com/Program/MSSQL/0119631c2009.html

元数据简介

元数据 (metadata) 最常见的定义为“有关数据的结构数据”,或者再简单一点就是“关于数据的信息”,日常生活中的图例、图书馆目录卡和名片等都可以看作是元数据。在关系型数据库管理系统 (DBMS) 中,元数据描述了数据的结构和意义。比如在管理、维护 SQL Server 或者是开发数据库应用程序的时候,我们经常要获取一些涉及到数据库架构的信息:

◆某个数据库中的表和视图的个数以及名称;

◆某个表或者视图中列的个数以及每一列的名称、数据类型、长度、精度、描述等;

◆某个表上定义的约束;

◆某个表上定义的索引以及主键/外键的信息。

下面我们将介绍几种获取元数据的方法。

获取元数据

使用系统存储过程与系统函数访问元数据

获取元数据最常用的方法是使用 SQL Server 提供的系统存储过程与系统函数。

系统存储过程与系统函数在系统表和元数据之间提供了一个抽象层,使得我们不用直接查询系统表就能获得当前数据库对象的元数据。

常用的与元数据有关的系统存储过程有以下一些:

系统存储过程

◆sp_columns 返回指定表或视图的列的详细信息。

◆sp_databases 返回当前服务器上的所有数据库的基本信息。

◆sp_fkeys 若参数为带有主键的表,则返回包含指向该表的外键的所有表;若参数为带有外键的表名,则返回所有同过主键/外键关系与该外键相关联的所有表。

◆sp_pkeys 返回指定表的主键信息。

◆sp_server_info 返回当前服务器的各种特性及其对应取值。

◆sp_sproc_columns 返回指定存储过程的的输入、输出参数的信息。

◆sp_statistics 返回指定的表或索引视图上的所有索引以及统计的信息。

◆sp_stored_procedures 返回当前数据库的存储过程列表,包含系统存储过程。

◆sp_tables 返回当前数据库的所有表和视图,包含系统表。

常用的与元数据有关的系统函数有以下一些:

系统函数

◆COLUMNPROPERTY 返回有关列或过程参数的信息,如是否允许空值,是否为计算列等。

◆COL_LENGTH 返回指定数据库的指定属性值,如是否处于只读模式等。

◆DATABASEPROPERTYEX 返回指定数据库的指定选项或属性的当前设置,如数据库的状态、恢复模型等。

◆OBJECT_ID 返回指定数据库对象名的标识号

◆OBJECT_NAME 返回指定数据库对象标识号的对象名。

◆OBJECTPROPERTY 返回指定数据库对象标识号的有关信息,如是否为表,是否为约束等。

◆fn_listextendedproperty 返回数据库对象的扩展属性值,如对象描述、格式规则、输入掩码等。

由于我们无法直接利用到存储过程与函数的返回结果,因此只有在我们关心的只是查询的结果,而不需要进一步利用这些结果的时候,我们会使用系统存储过程与系统函数来查询元数据。

例如,如果要获得当前服务器上所有数据库的基本信息,我们可以在查询分析器里面运行:

EXEC sp_databases
GO
 
在返回结果中我们可以看到数据库的名称、大小及备注等信息。

但是如果要引用这部分信息,或者存储这部分信息以供后面使用,那么我们必须借助中间表来完成这个操作:

 

以下为引用的内容:

CREATE TABLE #sp_result
(
DATABASE_NAME sysname,
DATABASE_SIZE int,
REMARKS varchar(254) NULL
)
GO
INSERT INTO #sp_result 
EXEC ('sp_databases')
GO


使用信息架构视图访问元数据 

信息架构视图基于 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:返回当前数据库中的当前用户可以访问的视图及其所有者、定义等信息。

由于这些信息架构都是以视图的方式存在的,因此我们可以很方便地获得并利用需要的信息。

例如,我们要得到某个表有多少列,可以使用以下语句:

SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME='mytable'

使用系统表访问元数据

虽然使用系统存储过程、系统函数与信息架构视图已经可以为我们提供了相当丰富的元数据信息,但是对于某些特殊的元数据信息,我们仍然需要直接对系统表进行查询。因为SQL Server 将所有数据库对象的信息均存放在系统表中,作为 SQL Server 的管理、开发人员,了解各个系统表的作用将有助于我们了解 SQL Server 的内在工作原理。

SQL Server 的系统表非常多,其中最常用的与元数据查询有关的表有如下一些:

syscolumns 存储每个表和视图中的每一列的信息以及存储过程中的每个参数的信息。

syscomments 存储包含每个视图、规则、默认值、触发器、CHECK 约束、DEFAULT 约束和存储过程的原始 SQL 文本语句。

sysconstraints 存储当前数据库中每一个约束的基本信息。

sysdatabases 存储当前服务器上每一个数据库的基本信息。

sysindexes 存储当前数据库中的每个索引的信息。

sysobjects 存储数据库内的每个对象(约束、默认值、日志、规则、存储过程等)的基本信息。

sysreferences 存储所有包括 FOREIGN KEY 约束的列。

systypes 存储系统提供的每种数据类型和用户定义数据类型的详细信息。

将系统存储过程、系统函数、信息架构视图与系统表结合使用,可以方便地让我们获得所有需要的元数据信息。

示例:

1、 获得当前数据库所有用户表的名称。

SELECT OBJECT_NAME (id)
FROM sysobjects
WHERE xtype = 'U' AND OBJECTPROPERTY (id, 'IsMSShipped') = 0
 
其中主要用到了系统表 sysobjects以及其属性 xtype,还有就是用到了 OBJECTPROPERTY 系统函数来判断是不是安装 SQL Server 的过程中创建的对象。

2、获得指定表上所有的索引名称。

SELECT name FROM sysindexes
WHERE id = OBJECT_ID ('mytable') AND indid > 0
 
综合实例

下面给出了一个存储过程,它的作用是自动将当前数据库的用户存储过程加密。

 

以下为引用的内容:

DECLARE @sp_name nvarchar(400)
DECLARE @sp_content nvarchar(2000)
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' 
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 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

 
该存储过程利用了 sysobjects 和 syscomments 表,并巧妙地修改了原存储过程的 SQL 定义语句,将 AS 修改为了 WITH ENCRYPTION AS,从而达到了加密存储过程的目的。本存储过程在 SQL Server 2000 上通过。
欢迎加群互相学习,共同进步。QQ群:iOS: 58099570 | Android: 330987132 | Go:217696290 | Python:336880185 | 做人要厚道,转载请注明出处!http://www.cnblogs.com/sunshine-anycall/archive/2009/09/17/1568874.html
相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
相关文章
|
4月前
|
SQL IDE Java
Java连接SQL Server数据库的详细操作流程
Java连接SQL Server数据库的详细操作流程
|
12天前
|
关系型数据库 MySQL 网络安全
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
|
3月前
|
SQL 存储 监控
SQL Server的并行实施如何优化?
【7月更文挑战第23天】SQL Server的并行实施如何优化?
69 13
|
3月前
|
SQL
解锁 SQL Server 2022的时间序列数据功能
【7月更文挑战第14天】要解锁SQL Server 2022的时间序列数据功能,可使用`generate_series`函数生成整数序列,例如:`SELECT value FROM generate_series(1, 10)。此外,`date_bucket`函数能按指定间隔(如周)对日期时间值分组,这些工具结合窗口函数和其他时间日期函数,能高效处理和分析时间序列数据。更多信息请参考官方文档和技术资料。
|
3月前
|
SQL 存储 网络安全
关系数据库SQLserver 安装 SQL Server
【7月更文挑战第26天】
51 6
|
2月前
|
SQL 安全 Java
驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接。错误:“The server selected protocol version TLS10 is not accepted by client
驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接。错误:“The server selected protocol version TLS10 is not accepted by client
252 0
|
3月前
|
存储 SQL C++
对比 SQL Server中的VARCHAR(max) 与VARCHAR(n) 数据类型
【7月更文挑战7天】SQL Server 中的 VARCHAR(max) vs VARCHAR(n): - VARCHAR(n) 存储最多 n 个字符(1-8000),适合短文本。 - VARCHAR(max) 可存储约 21 亿个字符,适合大量文本。 - VARCHAR(n) 在处理小数据时性能更好,空间固定。 - VARCHAR(max) 对于大文本更合适,但可能影响性能。 - 选择取决于数据长度预期和业务需求。
167 1
|
3月前
|
SQL Oracle 关系型数据库
MySQL、SQL Server和Oracle数据库安装部署教程
数据库的安装部署教程因不同的数据库管理系统(DBMS)而异,以下将以MySQL、SQL Server和Oracle为例,分别概述其安装部署的基本步骤。请注意,由于软件版本和操作系统的不同,具体步骤可能会有所变化。
183 3
|
3月前
|
SQL 存储 安全
数据库数据恢复—SQL Server数据库出现逻辑错误的数据恢复案例
SQL Server数据库数据恢复环境: 某品牌服务器存储中有两组raid5磁盘阵列。操作系统层面跑着SQL Server数据库,SQL Server数据库存放在D盘分区中。 SQL Server数据库故障: 存放SQL Server数据库的D盘分区容量不足,管理员在E盘中生成了一个.ndf的文件并且将数据库路径指向E盘继续使用。数据库继续运行一段时间后出现故障并报错,连接失效,SqlServer数据库无法附加查询。管理员多次尝试恢复数据库数据但是没有成功。
|
3月前
|
SQL 存储 关系型数据库
关系型数据库SQL Server学习
【7月更文挑战第4天】
54 2