こんにちは。システムの運用・開発をしているM.Iです。
私が日頃からよく利用している、運用時に役立つSQL(SQL Server)を紹介します。
・オブジェクトの一覧を取得
『sys.sysobjects』を利用します。
『xtype』を検索条件に取得すれば、テーブル(U)、ビュー(V)、ストアドプロシージャ(P)、関数(TF/FN)などに分類が可能です。
テーブルは『sys.tables』、ビューは『sys.views』からも取得できますが、『sys.sysobjects』を覚えておくと便利です。
| — テーブル一覧 SELECT name AS テーブル名 FROM sys.tables ORDER BY name; SELECT name AS テーブル名 FROM sys.sysobjects WHERE xtype = ‘U’ ORDER BY name; — ビュー一覧 SELECT name AS ビュー名 FROM sys.sysobjects WHERE xtype = ‘V’ ORDER BY name; — ストアドプロシージャ一覧 SELECT name AS ストアドプロシージャ名 FROM sys.sysobjects WHERE xtype = ‘P’ ORDER BY name; |
関数は『INFORMATION_SCHEMA.ROUTINES』から取得すると、戻り値も取得できます。
| — 関数一覧 SELECT SPECIFIC_NAME AS 関数名, DATA_TYPE AS 戻り値の型 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = ‘FUNCTION’ ORDER BY SPECIFIC_NAME; |
・テーブルのカラム一覧を取得
複数のテーブルのカラムを確認したい時、SSMSや資料で確認するより早いです。
| — テーブルのカラム一覧 DECLARE @TblNm VARCHAR(50) = ‘Person’; SELECT @TblNm AS テーブル名; SELECT c.name AS カラム名 ,TYPE_NAME(c.system_type_id) AS 型 ,CASE WHEN TYPE_NAME(c.system_type_id) LIKE ‘n%’ THEN CONVERT(NVARCHAR, c.max_length / 2) WHEN TYPE_NAME(c.system_type_id) LIKE ‘%var%’ AND c.max_length = -1 THEN ‘MAX’ WHEN TYPE_NAME(c.system_type_id) LIKE ‘%char%’ THEN CONVERT(NVARCHAR, c.max_length) WHEN TYPE_NAME(c.system_type_id) IN (‘decimal’, ‘numeric’) THEN N’全桁数: ‘ + CONVERT(NVARCHAR(5), c.precision) + N’ :小数点以下 ‘ + CONVERT(NVARCHAR(5), c.scale) ELSE ” END AS 桁数 FROM sys.tables t INNER JOIN sys.columns c ON t.object_id = c.object_id WHERE t.name = @TblNm; |
・対象のカラムを持つテーブル一覧を取得
あるカラムを使っているテーブルを調べる、結構あります。
| — 対象のカラムを持つテーブル一覧 DECLARE @ColNm VARCHAR(50) = ‘CountryRegionCode’; SELECT t.name AS テーブル名 ,c.name AS カラム名 ,TYPE_NAME(c.system_type_id) AS 型 ,CASE WHEN TYPE_NAME(c.system_type_id) LIKE ‘n%’ THEN CONVERT(NVARCHAR, c.max_length / 2) WHEN TYPE_NAME(c.system_type_id) LIKE ‘%var%’ AND c.max_length = -1 THEN ‘MAX’ WHEN TYPE_NAME(c.system_type_id) LIKE ‘%char%’ THEN CONVERT(NVARCHAR, c.max_length) WHEN TYPE_NAME(c.system_type_id) IN (‘decimal’, ‘numeric’) THEN N’全桁数: ‘ + CONVERT(NVARCHAR(5), c.precision) + N’ :小数点以下 ‘ + CONVERT(NVARCHAR(5), c.scale) ELSE ” END AS 桁数 FROM sys.tables AS t INNER JOIN sys.columns AS c ON t.object_id = c.object_id WHERE c.name LIKE @ColNm ORDER BY t.name, c.name; |
・対象の文字列を含むオブジェクト一覧を取得
私が今回紹介しているSQLの中で、一番利用するSQLです。
ビューやストアド内であるカラムを利用しているオブジェクトを探す時に利用します。
コメントも検索できます(されてしまいます)ので、ルールを決めてコメントを入れておくと、後々活用できます。
| — 対象の文字列を利用しているオブジェクトの一覧 DECLARE @SrhStr VARCHAR(50) = N’Flag’; SELECT DISTINCT o.name AS オブジェクト名 ,CASE o.xtype WHEN ‘V’ THEN N’ビュー’ WHEN ‘P’ THEN N’ストアドプロシージャ’ WHEN ‘TF’ THEN N’テーブル値関数’ WHEN ‘FN’ THEN N’スカラー値関数’ WHEN ‘TR’ THEN N’トリガー’ ELSE o.xtype END AS オブジェクト型 FROM sys.syscomments c, sys.sysobjects o WHERE o.id = c.id AND c.text LIKE ‘%’ + @SrhStr + ‘%’; |
・SQLの実行時間を取得
処理に時間がかかっているSQLを調査する時に利用します。
| — SQLの実行時間を取得 DECLARE @SrhTime VARCHAR(50) = N’2021/07/19 20:30:00′; SELECT SUBSTRING(s.text, (q.statement_start_offset / 2) + 1, ((CASE WHEN q.statement_end_offset = -1 THEN DATALENGTH(s.text) ELSE q.statement_end_offset END – q.statement_start_offset) / 2) + 1) AS 実行SQL ,CASE WHEN q.total_worker_time IS NULL OR q.execution_count IS NULL THEN ” ELSE CONVERT(VARCHAR, q.total_worker_time / q.execution_count) END AS [実行時間(マイクロ秒)] ,q.last_execution_time AS 実行日時 FROM sys.dm_exec_query_stats q CROSS APPLY sys.dm_exec_sql_text(q.sql_handle) s WHERE q.last_execution_time >= @SrhTime ORDER BY q.last_execution_time DESC; |
SSMSで表示できる内容も多いのですが、SQLを活用するとより早く確認できますし、結果もコピーができます。
まだまだ便利なシステムオブジェクトがありますので、是非活用してみてください。
サンプルデータベース：Mirrosoftの「AdventureWorks2019」
参考文献：オブジェクトカタログビュー システム情報スキーマビュー システム動的管理ビュー