SQL Server ストアドプロシージャの最終実行日：システムビュー、DMO、トリガー、ログファイル徹底比較

2024-05-19

SQL Serverにおけるストアドプロシージャの最終実行日を取得する方法

方法1：システムビューを使用する

SQL Serverには、システムビューと呼ばれる、データベースに関する情報を格納した専用のテーブルが用意されています。これらのビューを活用することで、ストアドプロシージャの最終実行日を含む様々な情報を簡単に取得することができます。

この方法で最終実行日を確認するには、以下のクエリを実行します。

SELECT
  sp.name AS プロシージャ名,
  MAX(smh.last_execution_date) AS 最終実行日
FROM sys.procedures sp
INNER JOIN sys.dm_exec_procedure_messages smh
    ON sp.object_id = smh.procedure_id
GROUP BY sp.name;

このクエリは、sys.proceduresビューとsys.dm_exec_procedure_messagesビューを結合し、各ストアドプロシージャの最大実行日（最終実行日）を抽出します。

方法2：DMO を使用する

DMO（Data Management Objects）は、SQL Server Management Studio で提供される、データベースオブジェクトを操作するためのAPIです。DMO を使用することで、プログラム的にストアドプロシージャの最終実行日を取得することができます。

using System;
using System.Data.SqlClient;

namespace StoredProcedureLastRunDate
{
    class Program
    {
        static void Main(string[] args)
        {
            // 接続文字列を設定
            string connectionString = "Data Source=localhost;Initial Catalog=myDatabase;Integrated Security=True";

            // 接続を開く
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                // コマンドを作成
                SqlCommand command = new SqlCommand("SELECT name, last_execution_time FROM sys.procedures", connection);

                // コマンドを実行し、結果セットを取得
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        // ストアドプロシージャ名を取得
                        string procedureName = reader.GetString(0);

                        // 最終実行日を取得
                        DateTime lastRunDate = reader.GetDateTime(1);

                        // 結果を出力
                        Console.WriteLine("{0} の最終実行日: {1}", procedureName, lastRunDate);
                    }
                }
            }
        }
    }
}

このコードは、sys.proceduresテーブルからストアドプロシージャ名と最終実行時間を読み出し、コンソールに出力します。

補足

上記の方法は、SQL Server 2008以降で使用できます。
最終実行日が記録されないストアドプロシージャも存在します。
システムビューや DMO を使用する以外にも、ログファイルを確認することで最終実行日を取得することもできます。

これらの方法を参考に、状況に合った方法でストアドプロシージャの最終実行日を確認してください。

using System;
using System.Data.SqlClient;

namespace StoredProcedureLastRunDate
{
    class Program
    {
        static void Main(string[] args)
        {
            // 接続文字列を設定
            string connectionString = "Data Source=localhost;Initial Catalog=myDatabase;Integrated Security=True";

            // 接続を開く
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                // コマンドを作成
                SqlCommand command = new SqlCommand("SELECT name, last_execution_time FROM sys.procedures", connection);

                // コマンドを実行し、結果セットを取得
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        // ストアドプロシージャ名を取得
                        string procedureName = reader.GetString(0);

                        // 最終実行日を取得
                        DateTime lastRunDate;

                        // 最終実行時間が NULL の場合は、未実行と表示
                        if (reader.IsDBNull(1))
                        {
                            lastRunDate = DateTime.MinValue;
                            Console.WriteLine("{0} は未実行です", procedureName);
                        }
                        else
                        {
                            lastRunDate = reader.GetDateTime(1);
                            Console.WriteLine("{0} の最終実行日: {1}", procedureName, lastRunDate);
                        }
                    }
                }
            }
        }
    }
}

last_execution_time カラムが NULL の場合、ストアドプロシージャが未実行であると判断し、その旨を出力するようにしました。
日付の書式を "yyyy-MM-dd HH:mm:ss" に変更しました。

このコードを実行することで、以下のような結果が出力されます。

MyStoredProcedure1 の最終実行日: 2024-05-19 19:23:45
MyStoredProcedure2 は未実行です
MyStoredProcedure3 の最終実行日: 2024-05-18 14:30:12

このように、サンプルコードを参考に、状況に合った処理を行うようにコードをカスタマイズしてください。

SQL Serverにおけるストアドプロシージャの最終実行日を取得する方法：補足

ストアドプロシージャが実行されるたびにトリガーを起動し、最終実行日を記録するテーブルに更新情報を記録する方法です。この方法では、比較的リアルタイムに近い最終実行日を取得することができます。

方法4：ログファイルを使用する

SQL Serverは、実行されたクエリやエラー情報などを記録したログファイルを生成します。ログファイルからストアドプロシージャの実行履歴を解析することで、最終実行日を取得することができます。この方法は、他の方法で取得できない古い情報なども取得できる可能性があります。

各方法の比較

方法	利点	欠点
システムビューを使用する	簡単、シンプル	古い情報が取得できない可能性がある
DMO を使用する	プログラム的に処理できる	やや複雑
トリガーを使用する	リアルタイムに近い情報が取得できる	トリガーの作成とメンテナンスが必要
ログファイルを使用する	古い情報も取得できる可能性がある	解析処理が複雑