SQLServerのストアドプロシージャ、関数のSQLを取得する

SQL Server Management Studioのクエリエディターを使用してストアドプロシージャと関数のSQLを取得する方法を紹介します。

本記事ではデータを取得するデータベースとして、Microsoftが公開しているデータベースの「AdventureWorks2017」を使用します。

INFORMATION_SCHEMA.ROUTINES

SQLServerのシステムビューにあるINFORMATION_SCHEMA.ROUTINESビューを使うとストアドプロシージャと関数の様々な情報を取得することができます。

INFORMATION_SCHEMA.ROUTINESビューで取得できるデータの一覧はMicrosoftのドキュメントページのROUTINES (Transact-SQL)で確認することができます。

ストアドプロシージャと関数の一覧を取得

INFORMATION_SCHEMA.ROUTINESビューからストアドプロシージャと関数の一覧を取得します。

実行結果

INFORMATION_SCHEMA.ROUTINESからストアドプロシージャと関数の一覧を取得

上記のSQLを実行することで、ストアドプロシージャと関数の一覧を取得できることが確認できます。

INFORMATION_SCHEMA.ROUTINESビューの主な情報

ここではINFORMATION_SCHEMA.ROUTINESビューで取得できるデータのうち、主な情報(列)を抜粋して記載します。

列名 説明
SPECIFIC_CATALOG
ROUTINE_CATALOG
カタログ名(データベース名)。
SPECIFIC_SCHEMA
ROUTINE_SCHEMA
スキーマ名。
SPECIFIC_NAME
ROUTINE_NAME
ストアドプロシージャ名または関数名。
ROUTINE_TYPE ストアドプロシージャまたは関数を表すタイプ名。
DATA_TYPE 戻り値のデータ型。
CHARACTER_MAXIMUM_LENGTH 戻り値のデータ型が文字型の場合の最大文字数。
CHARACTER_OCTET_LENGTH 戻り値の型が文字型の場合はバイト単位の最大長。
COLLATION_NAME 戻り値の照合順序名。
NUMERIC_PRECISION 戻り値が数値の場合の数値有効桁数。
NUMERIC_PRECISION_RADIX 戻り値が数値の場合の数値の有効桁数の基数。
NUMERIC_SCALE 戻り値が数値の場合の小数点以下の桁数。
DATETIME_PRECISION 戻り値が日時型の場合の秒の小数部の有効桁数。
ROUTINE_DEFINITION ストアドプロシージャまたは関数の定義テキスト。(ソースSQL。)

ストアドプロシージャと関数のSQLは「ROUTINE_DEFINITION」列から取得できます。

ストアドプロシージャまたは関数の一覧の取得

ストアドプロシージャの一覧を取得するSQLは次のようになります。

実行結果

INFORMATION_SCHEMA.ROUTINESからストアドプロシージャの一覧を取得

関数の一覧を取得するSQLは次のようになります。

実行結果

INFORMATION_SCHEMA.ROUTINESから関数の一覧を取得

INFORMATION_SCHEMA.ROUTINESビューのROUTINE_TYPE列に’PROCEDURE’を指定することでストアドプロシージャの一覧を取得することができ、ROUTINE_TYPE列に’FUNCTION’を指定することで関数の一覧を取得することができます。

特定のストアドプロシージャまたは関数の取得

スキーマ名(SPECIFIC_SCHEMA)とストアドプロシージャ名または関数名を指定することで、特定のストアドプロシージャ名または関数名の情報のみ取得することができます。

特定のストアドプロシージャの情報を取得するSQLは次のようになります。

実行結果

INFORMATION_SCHEMA.ROUTINESから特定のストアドプロシージャを取得

特定の関数の情報を取得するSQLは次のようになります。

実行結果

INFORMATION_SCHEMA.ROUTINESから特定の関数を取得

INFORMATION_SCHEMA.ROUTINESビューのSPECIFIC_SCHEMA列とSPECIFIC_NAME列に、スキーマ名とストアドプロシージャ名または関数名を指定して目的のSQLを取得することができます。

補足

使用するデータベースを選択せずにmasterデータベース等が選択された状態で、特定のストアドプロシージャまたは関数のSQLを取得する場合は、WHERE句の条件にSPECIFIC_CATALOG列を追加して対象のデータベース名を指定してください。