34,593
社区成员
发帖
与我相关
我的任务
分享
CREATE ASSEMBLY [abc]
AUTHORIZATION [dbo]
FROM 'C:\...\abc.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
CREATE ASSEMBLY [CasterFeg3Kakuduke]
AUTHORIZATION [dbo]
FROM 0x4D5A9000030000...00000
WITH PERMISSION_SET = EXTERNAL_ACCESS
CLR方式
a. 建立C#版的Classs Libary,函数如下:
using System;
using System.Collections.Generic;
using System.Linq;
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);
}
}
b. 加载.dll中间语言代码到DB
USE master;
CREATE ASSEMBLY SQLSignature
FROM 'C:\SQLSignature\SQLSignature\bin\Debug\SQLSignature.dll';
c. 注册函数fn_SQLSigCLR和fn_RegexReplace
CREATE FUNCTION dbo.fn_SQLSigCLR(@querystring AS NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
WITH RETURNS NULL ON NULL INPUT
EXTERNAL NAME SQLSignature.SQLSignature.fn_SQLSigCLR;
GO
CREATE FUNCTION dbo.fn_RegexReplace(
@input AS NVARCHAR(MAX),
@pattern AS NVARCHAR(MAX),
@replacement AS NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
WITH RETURNS NULL ON NULL INPUT
EXTERNAL NAME SQLSignature.SQLSignature.fn_RegexReplace;
GO
d. 注册完成之后,用下面代码测试:
SELECT
dbo.fn_SQLSigCLR(tsql_code) AS sig_sql,
duration
FROM dbo.Workload;