验证在查询中的任何地方都进行了特定的连接 [英] Verify that specific joins are made everywhere in a query
问题描述
我必须以存储过程的形式进行数百次查询，并验证每个连接的结果:
- 一个特定的列连接总是在连接中进行
- 连接不以硬编码格式存在于列的前一个唯一值(即它需要像 a.requiredJoinColumn = b.requiredJoinColumn 而不是 a.requiredJoinColumn = 'onlyValue'
例如，如果所需的列名为reqCol"，我想将其视为一个问题:
SELECT a.*从表AJOIN table b ON a.OtherColumn = b.OtherColumn还有，
SELECT a.*从表AJOIN 表 b ON a.reqCol = b.reqColJOIN table c ON a.OtherColumn = c.otherColumn另外，我希望这不会成为问题
SELECT a.*从表AJOIN table b ON a.reqCol = b.correctColButDifferentName我还需要它处理明确说明的内部和外部连接，以及在通过逗号完成连接的情况下(即从 tableA、tableB 中选择 *，其中 a.OtherColumn = b.OtherColumn)
现在我正在手动解决这个问题，它需要永远，所以我希望可能有一个我可以使用的工具.也许我可以将一些验证逻辑写入或使用并在我的 SQL Server 数据库中的一系列存储过程中执行.
您可以导出" sql_modules 并将它们提供给 sql 解析器，或者您可以在 sql server 中引入解析器并在内部处理它们(有点非正统，但是创意"同时进行).
有一个 smo 解析器在 .Net 中，它可以用于 clr 模块(例如标量函数)
//r: Microsoft.SqlServer.Management.SqlParser.dll使用系统；使用 Microsoft.SqlServer.Server;使用 Microsoft.SqlServer.Management.SqlParser.Parser；使用 System.Reflection;命名空间 sqlns{公共部分类 SQLParser{[SqlFunction(DataAccess = DataAccessKind.None)][返回:SqlFacet(MaxSize = -1)]公共静态字符串 SQLParseToXml(string sqlquery){if (string.IsNullOrEmpty(sqlquery)){返回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();返回 xmlstr;}}}构建 dll 并将以下两个 dll 复制到构建位置:
<块引用>Microsoft.SqlServer.Management.SqlParser.dll
Microsoft.SqlServer.Diagnostics.STrace.dll
创建程序集 &clr 函数:
从'C:\path to the project\bin\Debug\xyz.dll'创建程序集sqlparse使用 permission_set = 不安全；走创建函数 dbo.parseSqlToXml(@sql nvarchar(max))返回 nvarchar(max)以执行为调用者，在空输入时返回空作为外部名称 [sqlparse].[sqlns.SQLParser].SQLParseToXml;您将不得不通过解析器的 xml 结构并找到一种方法来获取您需要的内容.
启动(和灵感):
选择 src.modulename,t.col.value('../comment()[1]', 'nvarchar(500)') 作为连接条件，replace(left(t.col.value('(..//*/@Location)[1]', 'varchar(20)'), charindex(',', t.col.value('(..//*/@Location)[1]', 'varchar(20)'))), '(', '') 作为行号，t.col.value('./comment()[1]', 'nvarchar(500)') 作为列条件，t.col.value('(./SqlScalarRefExpression[1]/@ColumnOrPropertyName)[1]', 'nvarchar(200)') 作为 leftcol，t.col.value('(./SqlScalarRefExpression[2]/@ColumnOrPropertyName)[1]', 'nvarchar(200)') 作为 rightcol，t.col.value('(./SqlLiteralExpression[1]/@Value)[1]', 'nvarchar(200)') 作为文字从(选择 object_name(object_id) 作为 modulename， cast(dbo.parseSqlToXml(definition) as xml) 作为 definitionxml来自 sys.sql_modules) 作为源交叉应用 src.definitionxml.nodes('//SqlQualifiedJoinTableExpression/SqlConditionClause//SqlComparisonBooleanExpression') as t(col);以上，将产生类似于以下的结果集(摘自msdb中的模块)
<代码>|模块名称 |加入条件 |行号 |列条件 |左上角 |右列 |文字 ||------------------------------------------------|---------------------------------------------------------------------------|------------|-----------------------------------------|----------------------|------------------------------|------------------------------||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' |身份证 |空 |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 |空 ||sp_syscollector_delete_execution_log_tree |ON (l.package_execution_id = s.executionid) |34, |(l.package_execution_id = s.executionid) |package_execution_id |执行编号 |空 ||sp_syscollector_delete_execution_log_tree |ON i.log_id = l.log_id |35, |i.log_id = l.log_id |log_id |log_id |空 ||sp_syscollector_delete_execution_log_tree |ON i.log_id = l.log_id |40, |i.log_id = l.log_id |log_id |log_id |空 ||sp_syscollector_delete_collection_set_internal |ON (cs.schedule_uid = sv.schedule_uid) |29, |(cs.schedule_uid = sv.schedule_uid) |schedule_uid |schedule_uid |空 ||sysutility_mi_configuration |开 1=1 |11, |1=1 |空 |空 |1 |
I have to go through hundreds of queries in the form of stored procedures and verify that for every join being made that:
- a specific column join is always made in the join
- that the join doesn't exist in a hardcoded format to the previous only value of the column (i.e it needs to be like a.requiredJoinColumn = b.requiredJoinColumn and not a.requiredJoinColumn = 'onlyValue'
For example if the required column was named 'reqCol' I'd want to find this as a problem:
SELECT a.*
FROM tableA a
JOIN table b ON a.OtherColumn = b.OtherColumn
also,
SELECT a.*
FROM tableA a
JOIN table b ON a.reqCol = b.reqCol
JOIN table c ON a.OtherColumn = c.otherColumn
also, I'd want this to not show up as a problem
SELECT a.*
FROM tableA a
JOIN table b ON a.reqCol = b.correctColButDifferentName
I'd also need it to work on explicitly stated inner and outer joins as well and also in cases where the joins are done via commas (i.e select * from tableA, tableB where a.OtherColumn = b.OtherColumn)
Right now I'm going about this manually and it's taking forever so I was hoping there might be a tool that I could use. Maybe something I could write some validation logic into or with and execute across a series of stored procedures in my SQL Server database.
You could "export" the sql_modules and feed them to a sql parser or you could bring a parser in sql server and handle them internally (a bit unorthodox but "creative" at the same time).
There is an smo parser in .Net and it could be used in a clr module (eg. a scalar function)
//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;
}
}
}
Build the dll and copy to the build location the following two dlls:
Microsoft.SqlServer.Management.SqlParser.dll
Microsoft.SqlServer.Diagnostics.STrace.dll
create the assembly & the clr function:
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;
You will have to go through the parser's xml structure and find a way through it to get what you need.
For a kick-start (and inspiration):
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);
The above, will produce a result set similar to the following (excerpt for modules in 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 |
这篇关于验证在查询中的任何地方都进行了特定的连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
