xp_stored_proceduresシステムプロシージャを使ってストアドプロシージャを一覧表示する

2024-04-05

SQL Serverでストアドプロシージャを一覧表示するクエリ

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

sys.objects テーブルには、データベース内のすべてのオブジェクトに関する情報が格納されています。以下のクエリを実行することで、ストアドプロシージャの名前、作成者、作成日時、変更日時などを一覧表示できます。

SELECT 
    name AS 'ストアドプロシージャ名',
    [schema_name] AS 'スキーマ名',
    [type_desc] AS 'オブジェクト種別',
    [create_date] AS '作成日時',
    [modify_date] AS '変更日時'
FROM sys.objects
WHERE type IN ('P', 'PC')
ORDER BY name;

type IN ('P', 'PC') は、ストアドプロシージャ (P) と拡張ストアドプロシージャ (PC) を表示するための条件です。

sys.procedures テーブルには、ストアドプロシージャに関する詳細情報が格納されています。以下のクエリを実行することで、sys.objects テーブルよりも詳細な情報を取得できます。

SELECT 
    name AS 'ストアドプロシージャ名',
    [schema_name] AS 'スキーマ名',
    [is_ms_shipped] AS 'システムオブジェクト',
    [create_date] AS '作成日時',
    [modify_date] AS '変更日時',
    [definition] AS 'ストアドプロシージャ定義'
FROM sys.procedures
ORDER BY name;

[is_ms_shipped] 列は、システムオブジェクトかどうかを示します。
[definition] 列は、ストアドプロシージャの定義テキストを取得できます。

INFORMATION_SCHEMA ビューには、データベース内のすべてのオブジェクトに関する情報が格納されています。以下のクエリを実行することで、sys.objects テーブルと同様の情報を得ることができます。

SELECT 
    ROUTINE_NAME AS 'ストアドプロシージャ名',
    ROUTINE_SCHEMA AS 'スキーマ名',
    ROUTINE_TYPE AS 'オブジェクト種別',
    CREATED AS '作成日時',
    LAST_ALTERED AS '変更日時'
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'
ORDER BY ROUTINE_NAME;

その他の方法

上記以外にも、以下の方法でストアドプロシージャを一覧表示できます。

SSMS (SQL Server Management Studio) を使用する
データベースツールを使用する

SQL Serverでストアドプロシージャを一覧表示するには、さまざまな方法があります。それぞれの方法には利点と欠点があるため、目的に合った方法を選択する必要があります。

補足

上記のクエリは、SQL Server 2016 以降で使用できます。
クエリを実行する前に、必要な権限を持っていることを確認してください。

sys.objects テーブルを使用する

SELECT 
    name AS 'ストアドプロシージャ名',
    [schema_name] AS 'スキーマ名',
    [type_desc] AS 'オブジェクト種別',
    [create_date] AS '作成日時',
    [modify_date] AS '変更日時'
FROM sys.objects
WHERE type IN ('P', 'PC')
ORDER BY name;

sys.procedures テーブルを使用する

SELECT 
    name AS 'ストアドプロシージャ名',
    [schema_name] AS 'スキーマ名',
    [is_ms_shipped] AS 'システムオブジェクト',
    [create_date] AS '作成日時',
    [modify_date] AS '変更日時',
    [definition] AS 'ストアドプロシージャ定義'
FROM sys.procedures
ORDER BY name;

INFORMATION_SCHEMA ビューを使用する

SELECT 
    ROUTINE_NAME AS 'ストアドプロシージャ名',
    ROUTINE_SCHEMA AS 'スキーマ名',
    ROUTINE_TYPE AS 'オブジェクト種別',
    CREATED AS '作成日時',
    LAST_ALTERED AS '変更日時'
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'
ORDER BY ROUTINE_NAME;

SSMS を使用する

SSMS を起動し、データベースに接続します。
オブジェクトエクスプローラーで、データベース > プログラム可能性 > ストアドプロシージャ を展開します。
ストアドプロシージャの一覧が表示されます。

データベースツールを使用する

SQL Serverでストアドプロシージャを一覧表示する他の方法

xp_stored_procedures システムプロシージャを使用すると、ストアドプロシージャに関する詳細情報を取得できます。

EXEC xp_stored_procedures

このプロシージャは、出力パラメータを使用して、ストアドプロシージャの名前、作成者、作成日時、変更日時、パラメータ情報などを取得します。

EXEC sp_helptext 'ストアドプロシージャ名'

Dynamic SQLを使用すると、ストアドプロシージャの一覧を取得するクエリを動的に生成できます。

DECLARE @sql NVARCHAR(MAX)

SET @sql = N'
SELECT 
    name AS ''ストアドプロシージャ名'',
    [schema_name] AS ''スキーマ名'',
    [type_desc] AS ''オブジェクト種別'',
    [create_date] AS ''作成日時'',
    [modify_date] AS ''変更日時''
FROM sys.objects
WHERE type IN (''P'', ''PC'')
ORDER BY name;'

EXEC (@sql)

この方法を使用すると、さまざまな条件に基づいてストアドプロシージャを一覧表示できます。

PowerShellを使用すると、SQL Server Management Objects (SMO) を使用してストアドプロシージャを一覧表示できます。

$server = New-Object Microsoft.SqlServer.Management.Smo.Server('localhost')
$database = $server.Databases['AdventureWorks2019']

$storedProcedures = $database.StoredProcedures

foreach ($storedProcedure in $storedProcedures) {
    Write-Host $storedProcedure.Name
}