您可以“导出”sql_modules 并将它们提供给 sql 解析器,或者您可以在 sql server 中引入解析器并在内部处理它们(有点非正统,但同时“有创意”)。
有一个.Net 中的 smo 解析器 https://learn.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.management.sqlparser.parser.parser它可以在 clr 模块中使用(例如标量函数)
//r: Microsoft.SqlServer.Management.SqlParser.dll
using System;
using Microsoft.SqlServer.Server;
using Microsoft.SqlServer.Management.SqlParser.Parser;
using System.Reflection;
namespace sqlns
{
public partial class SQLParser
{
[SqlFunction(DataAccess = DataAccessKind.None)]
[return: SqlFacet(MaxSize = -1)]
public static string SQLParseToXml(string sqlquery)
{
if (string.IsNullOrEmpty(sqlquery))
{
return sqlquery;
}
ParseResult pres = Parser.Parse(sqlquery);
Object script = pres.GetType().GetProperty("Script", BindingFlags.NonPublic | BindingFlags.Instance).GetValue(pres, null);
String xmlstr = script.GetType().BaseType.GetProperty("Xml").GetValue(script, null).ToString();
return xmlstr;
}
}
}
构建 dll 并将以下两个 dll 复制到构建位置:
Microsoft.SqlServer.Management.SqlParser.dll
Microsoft.SqlServer.Diagnostics.STrace.dll
创建程序集和 clr 函数:
create assembly sqlparse from 'C:\path to the project\bin\Debug\xyz.dll'
with permission_set = unsafe;
go
create function dbo.parseSqlToXml(@sql nvarchar(max))
returns nvarchar(max)
with execute as caller, returns null on null input
as
external name [sqlparse].[sqlns.SQLParser].SQLParseToXml;
您必须仔细检查解析器的 xml 结构并找到一种方法来获取您需要的内容。
对于启动(和灵感):
select src.modulename,
t.col.value('../comment()[1]', 'nvarchar(500)') as joincondition,
replace(left(t.col.value('(..//*/@Location)[1]', 'varchar(20)'), charindex(',', t.col.value('(..//*/@Location)[1]', 'varchar(20)'))), '(', '') as linenumber,
t.col.value('./comment()[1]', 'nvarchar(500)') as columncondition,
t.col.value('(./SqlScalarRefExpression[1]/@ColumnOrPropertyName)[1]', 'nvarchar(200)') as leftcol,
t.col.value('(./SqlScalarRefExpression[2]/@ColumnOrPropertyName)[1]', 'nvarchar(200)') as rightcol,
t.col.value('(./SqlLiteralExpression[1]/@Value)[1]', 'nvarchar(200)') as literal
from
(
select object_name(object_id) as modulename, cast(dbo.parseSqlToXml(definition) as xml) as definitionxml
from sys.sql_modules
) as src
cross apply src.definitionxml.nodes('//SqlQualifiedJoinTableExpression/SqlConditionClause//SqlComparisonBooleanExpression') as t(col);
以上,将产生类似于以下的结果集(摘录于 msdb 中的模块)
| modulename | joincondition | linenumber | columncondition | leftcol | rightcol | literal |
|------------------------------------------------|---------------------------------------------------------------------------|------------|-------------------------------------------------|----------------------|---------------|--------------------------------------|
| syscollector_execution_log_full | (p.id = t.package_id AND p.id != N'84CEC861-D619-433D-86FB-0BB851AF454A') | 25, | p.id != N'84CEC861-D619-433D-86FB-0BB851AF454A' | id | NULL | 84CEC861-D619-433D-86FB-0BB851AF454A |
| sp_syscollector_delete_execution_log_tree | ON (node.log_id = leaf.parent_log_id) | 25, | (node.log_id = leaf.parent_log_id) | log_id | parent_log_id | NULL |
| sp_syscollector_delete_execution_log_tree | ON (l.package_execution_id = s.executionid) | 34, | (l.package_execution_id = s.executionid) | package_execution_id | executionid | NULL |
| sp_syscollector_delete_execution_log_tree | ON i.log_id = l.log_id | 35, | i.log_id = l.log_id | log_id | log_id | NULL |
| sp_syscollector_delete_execution_log_tree | ON i.log_id = l.log_id | 40, | i.log_id = l.log_id | log_id | log_id | NULL |
| sp_syscollector_delete_collection_set_internal | ON (cs.schedule_uid = sv.schedule_uid) | 29, | (cs.schedule_uid = sv.schedule_uid) | schedule_uid | schedule_uid | NULL |
| sysutility_mi_configuration | ON 1=1 | 11, | 1=1 | NULL | NULL | 1 |