データベース内の全ユーザー定義関数を取得する

データベース内の全ユーザー定義関数を取得するには？

今回はデータベース内の全ユーザー定義関数の一覧を取得したい時使えるスクリプトをご紹介します。

方法は何通りかありますが、ここではシンプルにユーザー定義関数の名前のリストを取得したいので sys.objects と INFORMATION_SCHEMA.ROUTINES を使うクエリーを載せておきます。

sys.objects を使って全ユーザー定義関数を取得する

まず、sys.objects　というスキーマスコープのユーザー定義オブジェクトを返すシステムカタログビューを使った方法です。

sys.objects を使って全ユーザー定義関数を取得するクエリーは次の通りです。

SELECT	SCHEMA_NAME(schema_id) AS SchemaName,
	name AS FunctionName,
	type AS FunctionType,
	type_desc AS FunctionTypeDesc
FROM	sys.objects
WHERE	type IN ('AF','FN','FS','FT','IF','TF')
ORDER BY SchemaName,
	 FunctionName;

sys.objects はユーザー定義関数以外のオブジェクトも含むので、type を指定してフィルターしています。

指定しているユーザー定義関数の type は以下のものです。

AF: CLR 集計関数
FN: SQL スカラー関数
FS: CLR スカラー関数
FT: CLR テーブル値関数
IF: SQL インラインテーブル値関数
TF: SQL テーブル値関数

INFORMATION_SCHEMA.ROUTINES を使って全ユーザー定義関数を取得する

次は、システムインフォメーションスキーマビューの ROUTINES を使って全ユーザー定義関数を取得する方法です。

INFORMATION_SCHEMA.ROUTINES は現在のデータベースで現在のユーザーがアクセスできるストアードプロシージャとファンクションを返してくれるビューです。

INFORMATION_SCHEMA.ROUTINES を使って全ユーザー定義関数を取得するクエリーは次の通りです。

SELECT	ROUTINE_SCHEMA,
	ROUTINE_NAME
FROM	INFORMATION_SCHEMA.ROUTINES
WHERE	ROUTINE_TYPE = 'FUNCTION'
ORDER BY ROUTINE_SCHEMA,
	 ROUTINE_NAME;

ROUTINE_TYPE はストアードプロシージャは PROCEDURE、ファンクションは FUNCTION なので、FUNCTION でフィルターしています。

理由はわかりませんが、マイクロソフトのサイトに INFORMATION_SCHEMA.ROUTINES ビューの ROUTINE_SCHEMA カラムから取得される dbo などのスキーマ名をあてにしないように注意書きがされています。信用できるスキーマ名を取得したい時は sys.objects のほうを使ってください。

ユーザー定義関数の定義を取得する

おまけですが、ユーザー定義関数の定義の内容も取得したい時は、CLR 以外は以下のクエリーで取得できます。

SELECT	SCHEMA_NAME(O.schema_id) AS SchemaName,
	O.name AS FunctionName,
	O.type AS FunctionType,
	O.type_desc AS FunctionTypeDesc,
	M.definition AS FunctionDefinition
FROM	sys.objects AS O
	   LEFT JOIN sys.sql_modules AS M
	      ON O.object_id = M.object_id
WHERE	O.type IN ('AF','FN','FS','FT','IF','TF')
ORDER BY SchemaName,
	 FunctionName;

CLR ユーザー定義関数の FunctionDefinition は NULL になります。

どれも、現在のユーザーがアクセスできるユーザー定義関数しか取得できませんので、権限のあるユーザーでログインして実行してくださいね！