sp_help_trigger システムプロシージャを使用して SQL Server データベースのすべてのトリガーを一覧表示する方法

2024-04-02

SQL Server データベースのすべてのトリガーをテーブル名とテーブルのスキーマで一覧表示する

このチュートリアルでは、SQL Server データベース内のすべてのトリガーを、関連するテーブル名とテーブルのスキーマと共に一覧表示する方法について説明します。

方法

INFORMATION_SCHEMA ビューは、データベースに関するメタデータ情報を提供します。以下のクエリを使用して、すべてのトリガーとその関連するテーブル名とテーブルのスキーマを取得できます。

SELECT 
    t.name AS テーブル名,
    s.name AS スキーマ名,
    tr.name AS トリガー名
FROM 
    sys.triggers AS tr
INNER JOIN 
    sys.tables AS t ON tr.object_id = t.object_id
INNER JOIN 
    sys.schemas AS s ON t.schema_id = s.schema_id
ORDER BY 
    s.name, t.name, tr.name;

SELECT 
    t.name AS テーブル名,
    s.name AS スキーマ名,
    tr.name AS トリガー名
FROM 
    sys.triggers AS tr
INNER JOIN 
    sys.objects AS t ON tr.parent_id = t.object_id
INNER JOIN 
    sys.schemas AS s ON t.schema_id = s.schema_id
WHERE 
    tr.type IN ('D', 'U', 'I', 'F', 'X')
ORDER BY 
    s.name, t.name, tr.name;

EXEC sp_help_trigger;

出力例

以下の例は、上記のクエリによって生成される出力例です。

テーブル名 | スキーマ名 | トリガー名
------- | -------- | --------
Customers | dbo | Customer_Insert
Employees | dbo | Employee_Update
Orders | dbo | Order_Delete
Products | dbo | Product_Validation

補足

上記のクエリは、すべての種類のトリガー (INSERT、UPDATE、DELETE、DDL、FOR EACH) を一覧表示します。特定の種類のトリガーのみを一覧表示するには、WHERE 句を追加で指定します。
上記のクエリは、テーブル名とテーブルのスキーマのみを表示します。その他のトリガー情報 (作成者、作成日時、トリガーの定義など) を表示するには、SELECT 句に additional columns を追加します。

INFORMATION_SCHEMA ビューを使用する

SELECT 
    t.name AS テーブル名,
    s.name AS スキーマ名,
    tr.name AS トリガー名
FROM 
    sys.triggers AS tr
INNER JOIN 
    sys.tables AS t ON tr.object_id = t.object_id
INNER JOIN 
    sys.schemas AS s ON t.schema_id = s.schema_id
ORDER BY 
    s.name, t.name, tr.name;

sys.triggers システムテーブルを使用する

SELECT 
    t.name AS テーブル名,
    s.name AS スキーマ名,
    tr.name AS トリガー名
FROM 
    sys.triggers AS tr
INNER JOIN 
    sys.objects AS t ON tr.parent_id = t.object_id
INNER JOIN 
    sys.schemas AS s ON t.schema_id = s.schema_id
WHERE 
    tr.type IN ('D', 'U', 'I', 'F', 'X')
ORDER BY 
    s.name, t.name, tr.name;

sp_help_trigger システムプロシージャを使用する

EXEC sp_help_trigger;

他の方法

PowerShell スクリプトを使用する

以下の PowerShell スクリプトは、データベース内のすべてのトリガーとその関連するテーブル名とテーブルのスキーマを取得します。

$server = 'localhost'
$database = 'MyDatabase'

$triggers = Get-Trigger -ServerInstance $server -Database $database

foreach ($trigger in $triggers) {
    $table = Get-Table -ServerInstance $server -Database $database -Name $trigger.ParentObject

    Write-Output "`nテーブル名: $($table.Name)"
    Write-Output "スキーマ名: $($table.Schema)"
    Write-Output "トリガー名: $($trigger.Name)"
}

C# プログラムを使用する

using System;
using System.Data.SqlClient;

namespace ListTriggers
{
    class Program
    {
        static void Main(string[] args)
        {
            string connectionString = "Server=localhost;Database=MyDatabase;Integrated Security=True;";

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                SqlCommand command = new SqlCommand("SELECT * FROM INFORMATION_SCHEMA.TRIGGERS", connection);

                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    string tableName = reader["TABLE_NAME"].ToString();
                    string schemaName = reader["SCHEMA_NAME"].ToString();
                    string triggerName = reader["TRIGGER_NAME"].ToString();

                    Console.WriteLine("テーブル名: {0}", tableName);
                    Console.WriteLine("スキーマ名: {0}", schemaName);
                    Console.WriteLine("トリガー名: {0}", triggerName);
                    Console.WriteLine();
                }

                reader.Close();
            }
        }
    }
}