求一CLR函数，格式化Trace跟踪出来SQL语句

[jia_guijun]

例如以下两句：

select * from tb where id=5
select * from tb where id=10

格式化类似为：
select * from tb where id=@id

记得在一本书上看到过，网上也有，但找不到了，应该是CLR扩展函数

[csdyyr]

SQL SERVER 2005 技术内幕上有。

[jia_guijun]

[Quote=引用 1 楼 csdyyr 的回复:]
SQL SERVER 2005 技术内幕上有。
[/Quote]

对，好像是，有脚本没？当时自己调试好像没调通。

[csdyyr]

using System.Text;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.Text.RegularExpressions;

public partial class SQLSignature
{
    // fn_SQLSigCLR
    [SqlFunction(IsDeterministic = true, DataAccess = DataAccessKind.None)]
    public static SqlString fn_SQLSigCLR(SqlString querystring)
    {
        return (SqlString)Regex.Replace(
            querystring.Value,
            @"([\s,(=<>!](?![^\]]+[\]]))(?:(?:(?:(?#    expression coming
             )(?:([N])?(')(?:[^']|'')*('))(?#           character
             )|(?:0x[\da-fA-F]*)(?#                     binary
             )|(?:[-+]?(?:(?:[\d]*\.[\d]*|[\d]+)(?#     precise number
             )(?:[eE]?[\d]*)))(?#                       imprecise number
             )|(?:[~]?[-+]?(?:[\d]+))(?#                integer
             ))(?:[\s]?[\+\-\*\/\%\&\|\^][\s]?)?)+(?#   operators
             ))",
            @"$1$2$3#$4");
    }
    // fn_RegexReplace - for generic use of RegEx-based replace
    [SqlFunction(IsDeterministic = true, DataAccess = DataAccessKind.None)]
    public static SqlString fn_RegexReplace(
        SqlString input, SqlString pattern, SqlString replacement)
    {
        return (SqlString)Regex.Replace(
            input.Value, pattern.Value, replacement.Value);
    }
}

[--小F--]

这个是什么 看不懂

[csdyyr]

--来自SQL SERVER 2005 技术内幕
--SQL Function:
IF OBJECT_ID('dbo.fn_SQLSigTSQL') IS NOT NULL
  DROP FUNCTION dbo.fn_SQLSigTSQL;
GO

CREATE FUNCTION dbo.fn_SQLSigTSQL
  (@p1 NTEXT, @parselength INT = 4000)
RETURNS NVARCHAR(4000)

--
-- This function is provided "AS IS" with no warranties,
-- and confers no rights.
-- Use of included script samples are subject to the terms specified at
-- http://www.microsoft.com/info/cpyright.htm
--
-- Strips query strings
AS
BEGIN
  DECLARE @pos AS INT;
  DECLARE @mode AS CHAR(10);
  DECLARE @maxlength AS INT;
  DECLARE @p2 AS NCHAR(4000);
  DECLARE @currchar AS CHAR(1), @nextchar AS CHAR(1);
  DECLARE @p2len AS INT;

  SET @maxlength = LEN(RTRIM(SUBSTRING(@p1,1,4000)));
  SET @maxlength = CASE WHEN @maxlength > @parselength
                     THEN @parselength ELSE @maxlength END;
  SET @pos = 1;
  SET @p2 = '';
  SET @p2len = 0;
  SET @currchar = '';
  set @nextchar = '';
  SET @mode = 'command';

  WHILE (@pos <= @maxlength)
  BEGIN
    SET @currchar = SUBSTRING(@p1,@pos,1);
    SET @nextchar = SUBSTRING(@p1,@pos+1,1);
    IF @mode = 'command'
    BEGIN
      SET @p2 = LEFT(@p2,@p2len) + @currchar;
      SET @p2len = @p2len + 1 ;
      IF @currchar IN (',','(',' ','=','<','>','!')
        AND @nextchar BETWEEN '0' AND '9'
      BEGIN
        SET @mode = 'number';
        SET @p2 = LEFT(@p2,@p2len) + '#';
        SET @p2len = @p2len + 1;
      END
      IF @currchar = ''''
      BEGIN
        SET @mode = 'literal';
        SET @p2 = LEFT(@p2,@p2len) + '#''';
        SET @p2len = @p2len + 2;
      END
    END
    ELSE IF @mode = 'number' AND @nextchar IN (',',')',' ','=','<','>','!')
      SET @mode= 'command';
    ELSE IF @mode = 'literal' AND @currchar = ''''
      SET @mode= 'command';

    SET @pos = @pos + 1;
  END
  RETURN @p2;
END
GO

[nzperfect]

如果做性能分析，推荐一个工具：ClearTrace
http://www.scalesql.com/cleartrace/

[guguda2008]

收藏学习

[jia_guijun]

[Quote=引用 6 楼 perfectaction 的回复:]
如果做性能分析，推荐一个工具：ClearTrace
http://www.scalesql.com/cleartrace/
[/Quote]

性能分析我用RMLUtils。

格式化语句，我是要根据跟踪出来的错误信息进行分组统计，并发送邮件提醒。

两个都可以用，用CLR的明显比SQL的快很多，谢谢了。