有没有办法在 SQL Server 存储过程中查询注释

Question

我希望显示我的存储过程的文档步骤。开始此操作的最佳方法是将所有注释从我的存储过程中提取出来。有没有办法查询并仅返回我的存储过程中的注释？

Answer 1

对于以"--"开头的注释，你可以像这样使用 Ngrams8K：

DECLARE @storedproc varchar(8000) =
'-- Some Comments
SET NOCOUNT ON;
-- Some Comments
SELECT FirstName -- we only need the first name
FROM dbo.Users WHERE Id = @Id;';
SELECT *
FROM 
(
  SELECT
    lineNumber = row_number() over (ORDER BY d.p),
    wholeLine  = SUBSTRING
               (
                 @storedproc, 
                 d.p+l.d, -- delimiter position + delimiter length
                  isnull(nullif(charindex(char(10), @storedproc, d.p+l.d),0) - (d.p+l.d), l.s+l.d)
               )
  FROM (values (len(@storedproc), 1)) as l(s,d) -- length of the string and delimiter as "inline variables"
  CROSS APPLY
  (
    SELECT -(l.d) UNION ALL -- 0 would be fine too; I'm using -(l.d) to keep the formula uniform
    SELECT ng.position
    FROM dbo.NGrams8K(@storedproc, l.d) as ng
    WHERE token = char(10)
  ) as d(p) -- delimiter.position
) split
CROSS APPLY (VALUES (SUBSTRING(wholeLine, charindex('--', wholeLine), 8000))) txt(comment)
WHERE txt.comment LIKE '%--%';

返回：

lineNumber  wholeLine                                         comment
----------  ----------------------------------------------    -----------------------
1           -- Some Comments                                  -- Some Comments
4           -- Some Comments                                  -- Some Comments
6           SELECT FirstName  -- we only need the first name  -- we only need the first name

对于使用 NGrams2B 的长度超过 8,000 个字符的过程，并将我的代码中 8000 的每个实例更改为输入字符串的长度。