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

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

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

INFORMATION_SCHEMA.ROUTINES

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

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

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

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は次のようになります。

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

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

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

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

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

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

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

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