通過系統資料表擷取SQL,Access,Oracle資料庫的中繼資料資訊

中繼資料簡介

　　中繼資料 (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 上通過。

------------------------- MS SQLServer -----------------------
--表說明

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

--欄位說明

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

 

--主鍵、外鍵資訊(簡化)

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
 ,t_obj.name    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 t_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

--主鍵、外鍵對照(簡化)

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

--表資訊

select * from all_tab_comments t
where owner='DBO'

--列資訊

select * from all_col_comments t
where owner='DBO'

--主鍵、外鍵對照

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')

--主鍵、外鍵資訊

select *
from all_cons_columns
where owner='DBO'
order by Constraint_Name, Position

------------------ Access --------------------------
//Access中的系統資料表MSysobjects儲存屬性的欄位是二進位格式，不能直接分析
//可以採用ADO內建的OpenSchema方法獲得相關資訊

  //use ADOInt.pas
  //po: TableName
  //DBCon:TADOConnection
  /ds:TADODataSet
 
--表資訊
 

DBCon.OpenSchema(siTables, VarArrayOf([Null, Null, 'Table']), EmptyParam, ds);

--列資訊
 

DBCon.OpenSchema(siColumns, VarArrayOf([Null, Null, 'po']), EmptyParam, ds);

 
--主鍵
 

DBCon.OpenSchema(siPrimaryKeys, EmptyParam, EmptyParam, ds);

 
--主鍵、外鍵對照
 

DBCon.OpenSchema(siForeignKeys, EmptyParam, EmptyParam, ds);