はじめに

そんなの記事にするまでもなく、 sys.parameters.default_value を見れば一発でしょ？？

そう思いきや実際にやってみると NULL しかとれなかったりします。（もちろんとれる場合もあるけど）

以下 MSDN より。

既定値は、CLR プロシージャの場合のみ、sys.parameters.default 列に記録されます。
この列は、Transact-SQL プロシージャパラメーターでは NULL になります。

ということで、この記事では簡単にとれそうでとれない ストアドプロシージャのパラメータに指定された既定値 を知る方法について書きたいと思います。

前提条件

・Transact-SQL (SQL Server) に限定します。
・知りたいのは MSDN に載っている以下の構文の default 値です。

--SQL Server Stored Procedure Syntax
CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ] 
    [ { @parameter [ type_schema_name. ] data_type }
        [ VARYING ] [ = default ] [ OUT | OUTPUT | [READONLY]
    ] [ ,...n ] 
[ WITH <procedure_option> [ ,...n ] ]
[ FOR REPLICATION ] 
AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
[;]

<procedure_option> ::= 
    [ ENCRYPTION ]
    [ RECOMPILE ]
    [ EXECUTE AS Clause ]

文字列から既定値を探るアプローチ

というわけでシステムテーブルには情報がないので、sp_helptext を使ってプロシージャを文字列として取得、そこから構文解析により既定値を探りたいと思います。

Transact-SQL で頑張って構文解析する記事もネット上にあるようですが、今回私はそれに縛られる必要がなかったのですぐさま .Net Framework に頼りました。

複雑なコメントが混じっている場合などにも楽に対応できるので、基本的には Microsoft.SqlServer.TransactSql.ScriptDom を使う方法が良いのかなと思います。

今回は参照 dll を増やしたくないなど諸々の事情があって System.Text.RegularExpressions.Regex を使ってやります。

正規表現を使って既定値を探る

やっと本題です。

まず文字列にコメントが混ざっている場合はノイズになるので取り除いてください。
/* */ のネストが2回以上発生していると正規表現一発ではできないようです。
1回までのネストは stack overflow に書いてらっしゃる方がいました。

コメント部分を表す正規表現

new Regex(@"/\*(?>(?:(?!\*/|/\*).)*)(?>(?:/\*(?>(?:(?!\*/|/\*).)*)\*/(?>(?:(?!\*/|/\*).)*))*).*?\*/|--.*?\r?[\n]", RegexOptions.Singleline)

次に、ストアドプロシージャを構成する文字列全体から、パラメータ定義部分を取得します。

CREATE～ASの部分を表す正規表現

new Regex(@"CREATE\s+(PROC(EDURE)?)\s+([\[]?(?<schema_name>[^\s\[\]]+)[\]]?\.)?\s*[\[]?(?<procedure_name>[^\s\[\]]+)[\]]?\s*\(?\s*(?<parameters>@.*?)?\s*(WITH\s+(?<procedure_options>(\s*,?\s*ENCRYPTION|\s*,?\s*RECOMPILE|\s*,?\s*EXECUTE\s+AS\s+[^\s\[\]]+?)+?))?\s*(?<for_replication>FOR\sREPLICATION)?\s*(?<=[\s|\]])(\)?\s*AS)\s+", RegexOptions.Singleline | RegexOptions.IgnoreCase)

列名やプロシージャ名に使える文字は MSDN のデータベース識別子のページに載っています。

上記で parameters と名前を付けたグループがパラメータ定義部分にあたります。
さらに次の正規表現で一つ一つのパラメーターに分解します。

一つ一つのパラメーターに分解する正規表現

new Regex(@"(?<parameter_name>@[^\s\[\]]+)\s*([\[]?(?<schema_name>[^\s\[\],]+)[\]]?\.)?\s*(?<data_type>[\[]?[^\s\[\],\(\)]+[\]]?(\s*\(\s*\d*\s*,?\s*\d*\s*\))?)\s*(?<varying>VARYING)?(=\s*(?<default>([^\s\[\],]|'|%)+(\s*\(\s*\d*\s*,?\s*\d*\s*\))?))?\s*(?<output>(OUT(PUT)?)?)\s*(?<readonly>READONLY)?", RegexOptions.Singleline | RegexOptions.IgnoreCase)

default と名前をつけたグループが既定値です。

まとめ

ある程度動くのは確認しましたが、うまく動かないところもまだありそうです。
というのもスペースの有無や特殊な文字など、テストパターンを作り切れていません。
使ったことないオプションとかあるし。。。
（テストはもう少しちゃんと書けたら GitHub あたりに公開します、たぶん）

また、上でさらっと書いて流しましたが余計なコメントを取り除いておかないと正しく動きません。
真面目にやると結構面倒そう。。

最後に、確認用のコードを載せて終わりにしようと思います。

Test.cs

        [TestMethod]
        public void Qiita()
        {
            var text = @"
CREATE PROCEDURE UserProcedure (
   @param0 varchar(5) = 'あ',
   @param1 decimal(15,2) = 123.45
) AS 
BEGIN
   SELECT 1
END";
            var regexCreateAs = new Regex(@"CREATE\s+(PROC(EDURE)?)\s+([\[]?(?<schema_name>[^\s\[\]]+)[\]]?\.)?\s*[\[]?(?<procedure_name>[^\s\[\]]+)[\]]?\s*\(?\s*(?<parameters>@.*?)?\s*(WITH\s+(?<procedure_options>(\s*,?\s*ENCRYPTION|\s*,?\s*RECOMPILE|\s*,?\s*EXECUTE\s+AS\s+[^\s\[\]]+?)+?))?\s*(?<for_replication>FOR\sREPLICATION)?\s*(?<=[\s|\]])(\)?\s*AS)\s+", RegexOptions.Singleline | RegexOptions.IgnoreCase);
            var regexParameter = new Regex(@"(?<parameter_name>@[^\s\[\]]+)\s*([\[]?(?<schema_name>[^\s\[\],]+)[\]]?\.)?\s*(?<data_type>[\[]?[^\s\[\],\(\)]+[\]]?(\s*\(\s*\d*\s*,?\s*\d*\s*\))?)\s*(?<varying>VARYING)?(=\s*(?<default>([^\s\[\],]|'|%)+(\s*\(\s*\d*\s*,?\s*\d*\s*\))?))?\s*(?<output>(OUT(PUT)?)?)\s*(?<readonly>READONLY)?", RegexOptions.Singleline | RegexOptions.IgnoreCase);

            var parameters = regexParameter.Matches(regexCreateAs.Match(text).Groups["parameters"].Value);

            Assert.AreEqual("'あ'", parameters[0].Groups["default"].Value);
            Assert.AreEqual("123.45", parameters[1].Groups["default"].Value);
        }

最後まで読んでいただきありがとうございました。
間違っている部分など、コメントいただけたら幸いです。

Transact-SQL ストアドプロシージャのパラメータに指定された既定値を知る方法

はじめに

前提条件

文字列から既定値を探るアプローチ

正規表現を使って既定値を探る

まとめ