访问 Microsoft SQL Server 元数据的三种方法

上海微创软硬件有限公司 肖桂东 实用读者群：microsoft sql server 中、高档用户 元数据简介 　　元数据 (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_databasesgo在归来截止中咱们不妨看到数据库的称呼、巨细及备注等消息。然而即使要援用这局部消息，大概保存这局部消息以供反面运用，那么咱们必需借助中央表来实行这个操纵：create table #sp_result(database_name sysname,database_size int,remarks varchar(254) null)goinsert 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 sysobjectswhere xtype = 'u' and objectproperty (id, 'ismsshipped') = 0个中重要用到了体例表 sysobjects以及其属性 xtype，再有即是用到了 objectproperty 体例因变量来确定是否安置 sql server 的进程中创造的东西。2、 赢得指定表上一切的索引称呼select name from sysindexeswhere id = object_id ('mytable') and indid > 0归纳范例 底下给出了一个保存进程，它的效率是机动将暂时数据库的用户保存进程加密。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, 'ismsshipped')=0open sp_cursorfetch next from sp_cursor into @sp_namewhile @@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 into @sp_nameendclose sp_cursor deallocate sp_cursor该保存进程运用了 sysobjects 和 syscomments 表，并精巧地窜改了原保存进程的 sql 设置语句，将 as 窜改为了 with encryption as，进而到达了加密保存进程的手段。本保存进程在 sql server 2000 上经过。参考材料 microsoft sql server 2000 联机丛刊http://www.microsoft.com/china/sqlhttp://msdn.microsoft.com/library/default.asp?url=/nhp/default.asp?contentid=28000409