目次

  1. システムカタログビューについて

  2. どんなシステムカタログがあるのか

  3. 参考クエリ

1.システムカタログビューについて

SQL Serverデータベースのメタデータ情報を提供するビューです。 これらのビューには、データベースオブジェクトに関する情報が含まれています。 これらのビューにアクセスすることにより、データベースオブジェクトに関する情報を取得できます。

2.どんなシステムカタログビューがあるのか

sys.objectsから継承されたカタログ ビューなどで以下が存在する

ビュー内容
sys.objectsデータベース内のすべてのオブジェクトに関する情報
sys.columnsテーブルやビューの各列に関する情報
sys.indexesインデックスに関する情報を提供
sys.databasesサーバーに存在するすべてのデータベースに関する情報を提供
sys.schemasデータベース内のスキーマに関する情報を提供
sys.sysusersデータベース内のユーザーに関する情報
sys.sysloginsSQL Serverインスタンスに登録されているログインに関する情報
sys.sysprocesses現在実行中のプロセスに関する情報
sys.dm_exec_sessions現在接続されているセッションに関する情報
sys.dm_exec_requests現在実行中のクエリに関する情報

3.参考クエリ

SQLで特定テーブルやストアドプロシージャを任意のスキーマにフィルターしたり、抽出条件を設定するクエリ 

--特定条件でフィルターしたオブジェクトの取得
select
s.schema_id
,s.name as schema_name
,t.name as table_name
,t.object_id
from sys.schemas s inner join sys.tables t 
on t.schema_id = s.schema_id 
where -- フィルターを行いたい条件を追加していく
t.type = 'U'  --ユーザー定義のテーブル
AND s.name = '任意' --作成したスキーマ名 
AND t.modify_date  > DATEADD(DAY,-7,GETDATE()) --過去7日に更新された

SQLで特定テーブルやストアドプロシージャを削除したい場合などにスキーマでフィルターしたり抽出条件を設定するのに利用できそうなクエリ 

--DBのIDよりDB名を取得してテーブルのDROP文を作成
--database_idの確認用
select database_id,name as db_name from sys.databases

--database_idを指定してDROP文を生成
select
 'DROP TABLE [' + DB_NAME(任意のdatabase_id) + '].[' + s.name + '].[' + t.name +']'  AS 'DROPクエリ'
from sys.schemas s inner join sys.tables t 
on t.schema_id = s.schema_id

感想

SQLで更新したテーブルやストアドの反映時間を確認したり、対象のオブジェクトを削除するためのクエリとして利用もできそう。

注意点：SQLServerバージョンアップなどカラム変更に注意して利用

本コンテンツはプロモーションが含まれます。