SQL Serverデータベースのメタデータを取得する方法
6112 ワード
メタデータの概要
メタデータ(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
戻り結果には、データベースの名前、サイズ、コメントなどの情報が表示されます.
しかし、この情報を参照したり、後で使用するために保存したりするには、中間テーブルを使用してこの操作を完了する必要があります.
情報スキーマビューを使用したメタデータへのアクセス
情報アーキテクチャビューは、SQL-92規格におけるアーキテクチャビューの定義に基づいており、これらのビューはシステムテーブルとは独立しており、SQLサーバメタデータに関する内部ビューを提供している.情報アーキテクチャビューの最大の利点は、システムテーブルを重要に変更しても、アプリケーションがこれらのビューを正常に使用してアクセスできることです.したがって、アプリケーションでは、SQL-92規格に準拠するデータベースシステムであれば、情報アーキテクチャビューを使用して常に正常に動作します.
情報スキーマビュー
◆INFORMATION_SCHEMA.CHECK_CONTRAINTS:NULL値が許可されているかどうか、計算カラムであるかどうかなど、カラムまたはプロセスパラメータに関する情報を返します.◆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は、FOREGN KEY制約を含むすべてのカラムを格納します.Systypesストレージシステムは、各データ型とユーザー定義データ型の詳細を提供します.
システム・ストレージ・プロシージャ、システム関数、情報アーキテクチャ・ビューをシステム・テーブルと組み合わせて使用すると、必要なメタデータ情報をすべて簡単に入手できます.
例:
1.現在のデータベースのすべてのユーザー・テーブルの名前を取得します.
主にシステムテーブルsysobjectsとその属性xtypeを用い,またOBJECTPROPERTYシステム関数を用いてSQL Serverをインストールする過程で作成したオブジェクトかどうかを判断する.
2、指定したテーブルのすべてのインデックス名を取得します.
SELECT name FROM sysindexes WHERE id = OBJECT_ID ('mytable') AND indid > 0
総合例
次に、現在のデータベースのユーザー・ストレージ・プロシージャを自動的に暗号化するストレージ・プロシージャを示します.
このストレージ・プロシージャはsysobjectsとsyscommentsテーブルを用い,元のストレージ・プロシージャのSQL定義文を巧みに修正し,ASをWITH ENCRYPTION ASに修正して暗号化ストレージ・プロシージャの目的を達成した.このストレージ・プロシージャは、SQL Server 2000を通過します.
メタデータ(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サーバメタデータに関する内部ビューを提供している.情報アーキテクチャビューの最大の利点は、システムテーブルを重要に変更しても、アプリケーションがこれらのビューを正常に使用してアクセスできることです.したがって、アプリケーションでは、SQL-92規格に準拠するデータベースシステムであれば、情報アーキテクチャビューを使用して常に正常に動作します.
情報スキーマビュー
◆INFORMATION_SCHEMA.CHECK_CONTRAINTS:NULL値が許可されているかどうか、計算カラムであるかどうかなど、カラムまたはプロセスパラメータに関する情報を返します.◆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は、FOREGN 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を通過します.