SQL Server 2008 R2 で sp_helptext プロシージャを使用してユーザー定義関数をリストする

SQL Server 2008 R2 でデータベース内のすべてのユーザー定義関数をリストする

方法 1: システム ビューを使用する

SQL Server 2008 R2 には、INFORMATION_SCHEMA.TABLES ビューと INFORMATION_SCHEMA.COLUMNS ビューを使用して、すべてのユーザー定義関数をリストするシステム ビューが用意されています。

以下のクエリを実行すると、現在のデータベース内のすべてのユーザー定義関数がリストされます。

SELECT
    f.name AS 関数名,
    f.definition AS 定義,
    s.name AS スキーマ名
FROM
    INFORMATION_SCHEMA.TABLES f
JOIN
    INFORMATION_SCHEMA.COLUMNS c
ON
    f.object_id = c.object_id
AND
    c.type = 'FUNCTION'
ORDER BY
    f.name;

方法 2: sp_helptext プロシージャを使用する

sp_helptext プロシージャを使用して、個々のユーザー定義関数の定義を表示することもできます。 すべてのユーザー定義関数のリストを取得するには、以下のクエリを実行できます。

DECLARE @function_name NVARCHAR(128);

EXEC sp_helptext @objname = N'dbo.FunctionName';

SELECT @function_name AS 関数名,
       @definition AS 定義,
       'dbo' AS スキーマ名
FROM
    sys.sp_helptext;

このクエリを実行するには、まず @function_name 変数にユーザー定義関数の名前を指定する必要があります。 次に、sp_helptext プロシージャを実行して、関数の定義を取得します。 最後に、@function_name、@definition、および 'dbo' を結果セットに追加して、すべてのユーザー定義関数のリストを作成します。

• システム ビューと sp_helptext プロシージャのどちらを使用するかは、個人の好みと要件によって異なります。 システム ビューは、すべてのユーザー定義関数のリストをすばやく簡単に取得する場合に役立ちます。 sp_helptext プロシージャは、個々のユーザー定義関数の定義を表示する場合に役立ちます。
• 上記のクエリは、現在のデータベース内のユーザー定義関数のみをリストします。 他のデータベースのユーザー定義関数をリストするには、そのデータベース名をクエリに指定する必要があります。

SELECT
    f.name AS 関数名,
    f.definition AS 定義,
    s.name AS スキーマ名
FROM
    INFORMATION_SCHEMA.TABLES f
JOIN
    INFORMATION_SCHEMA.COLUMNS c
ON
    f.object_id = c.object_id
AND
    c.type = 'FUNCTION'
ORDER BY
    f.name;

このクエリを実行するには、SQL Server Management Studio などのツールを使用して SQL Server に接続する必要があります。 接続したら、上記のクエリをクエリ エディタに貼り付けて実行します。 クエリが正常に実行されると、現在のデータベース内のすべてのユーザー定義関数が結果セットに表示されます。

結果セット

説明

• 結果セットは、関数名 列で昇順にソートされます。
• s.name 列には、関数が属するスキーマ名が表示されます。
• f.definition 列には、関数の定義が表示されます。
• このクエリは、INFORMATION_SCHEMA.TABLES ビューと INFORMATION_SCHEMA.COLUMNS ビューを結合して、すべてのユーザー定義関数を取得します。

sys.dm_sql_objects ビューを使用して、データベース内のすべての SQL オブジェクトのリストを取得できます。 このビューには、ユーザー定義関数も含まれます。 以下のクエリを実行すると、現在のデータベース内のすべてのユーザー定義関数がリストされます。

SELECT
    o.name AS 関数名,
    o.definition AS 定義,
    s.name AS スキーマ名
FROM
    sys.dm_sql_objects o
JOIN
    sys.schemas s
ON
    o.schema_id = s.schema_id
WHERE
    o.type = 'FUNCTION'
ORDER BY
    o.name;

方法 4: PowerShellを使用する

PowerShell を使用して、SQL Server に接続し、すべてのユーザー定義関数をリストすることもできます。 以下の PowerShell スクリプトは、現在のデータベース内のすべてのユーザー定義関数をリストします。

$connection = New-Object System.Data.SqlClient.SqlConnection("server=localhost;database=master;integrated security=true")
$connection.Open()

$command = New-Object System.Data.SqlClient.SqlCommand("SELECT name, definition FROM sys.procedures WHERE type = 'FUNCTION'", $connection)
$reader = $command.ExecuteReader()

while ($reader.Read()) {
    Write-Output ("関数名: " + $reader["name"])
    Write-Output ("定義: " + $reader["definition"])
    Write-Output ("----")
}

$reader.Close()
$connection.Close()

方法 5: タスク スケジューラを使用する

タスク スケジューラを使用して、定期的にクエリを実行し、ユーザー定義関数のリストをファイルにエクスポートすることもできます。 これは、データベース内のユーザー定義関数を追跡する必要がある場合に役立ちます。