解析 TSQL 脚本并返回表和列用法

我们目前正在参与一个数据迁移项目，为此我们必须分析数据使用情况，这意味着我们需要弄清楚哪些表和列被使用并需要迁移，哪些数据已经过时。我们不仅有许多基于存储过程的 SSRS 报告，还有数百甚至数千个用于即席分析的 TSQL 脚本。手动逐个分析它们几乎是不可能的。

我寻找一种方法来解析这些脚本并返回脚本使用的数据。我设法在 EditPad 中编写了一个从脚本中提取表的宏，但我未能对列执行相同的操作。这里的主要问题是别名、CTE，甚至将脚本中的列名与其他命令区分开来。

与基于正则表达式的简单宏相比，SQL Server 必须清楚地了解脚本 -> 执行计划使用哪些列。有什么方法可以将此功能用于我们的目的吗？甚至更好：是否有任何第三方工具可以满足我们的需求？到目前为止，我什么也找不到。

非常感谢任何建议！

部分答案：

基于本文，可以使用 PowerShell(或编译的 .Net 语言)来使用该Microsoft.SqlServer.Management.SqlParser生成 SQL 语句的分析树，如Microsoft工具所用。

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.SqlParser") | Out-Null
$ParseOptions = New-Object Microsoft.SqlServer.Management.SqlParser.Parser.ParseOptions
$ParseOptions.BatchSeparator = 'GO' 
$ParseOptions.CompatibilityLevel = [Microsoft.SqlServer.Management.SqlParser.Common.DatabaseCompatibilityLevel]::Current
$ParseOptions.IsQuotedIdentifierSet = $true
$ParseOptions.TransactSqlVersion= [Microsoft.SqlServer.Management.SqlParser.Common.TransactSqlVersion]::Current
set-psdebug -strict
#$SQL=get-content $Args[0] |foreach-object{"$_`n"} 
$SQL = "SELECT  c.COLUMN_NAME,
c.TABLE_NAME, 
t.TABLE_SCHEMA,
t.TABLE_TYPE,
t.TABLE_NAME AS t2
FROM INFORMATION_SCHEMA.TABLES AS t
JOIN INFORMATION_SCHEMA.COLUMNS AS c
ON c.TABLE_NAME = t.TABLE_NAME
AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
AND c.TABLE_CATALOG = t.TABLE_CATALOG
"
$Script=[Microsoft.SqlServer.Management.SqlParser.Parser.Parser]::Parse($SQL, $ParseOptions)
$flags = [System.Reflection.BindingFlags]::Instance -bor [System.Reflection.BindingFlags]::NonPublic
$Script.GetType().GetProperty("Script", $flags).GetValue($Script).Xml

我安装了SSMS 2016和SQL Server 2014的本地版本，并且无需安装其他任何东西即可工作。

在解释树方面，这仍然有很多工作要做，但你可以利用它。

从执行计划中解析表和列可能是可能的，但做起来并不简单。 (我会看这篇文章，以防有人详细说明。第三方工具可能会这样做，这是另一件需要注意的事情。根据我的经验，我认为这可能不可行，没有达到您需要的全部程度。

横向方法：您能否安排对数据库"运行所有内容"，以便您知道所有可能的访问数据的尝试都受到了攻击？㞖：

创建数据库的副本。
仅包含查询工作所需的尽可能少的数据。
(因此，要么备份/还原，要么使用 SSMS "脚本输出"数据库)
配置安全性，以便使用的登录名无法访问任何内容
运行第一个查询。它将失败。确定需要访问哪些表和列。仅授予对这些表和列的访问权限。
运行下一个查询。重复。
很有可能可以批量运行查询并批量添加表/列
通过首先启用已知/明显的表来节省时间
一旦所有查询都可以成功运行，因为启用了所有相关的表和列，你就有了最小设置。

问题是...写完这篇文章后，我看到了很多潜在的警告、边缘案例和陷阱(这完全取决于你必须处理什么——观点呢？触发器？同义词？我不得不质疑这是否值得付出努力。如果您确定要删除一半的数据库，那就去做吧，但对于 10% 的减少，它可能不值得付出努力。(对于 10% 的减少，请尝试重命名最可疑的表，看看会发生什么。

我的一位同事有一个聪明的想法，即使用 XML 查询解析执行计划：

执行计划必须另存为 XML，然后在网站上进行过滤，以减少级别深度/级别数，级别数不得超过 128：

http://xmltoolbox.appspot.com/

1. Paste the XML
2. Add Column Reference as a filter
3. Format xml
4. Save it again as flatfile

过滤后的 XML 可以在 SQL 中读取和处理：

DECLARE @xml xml = (
       SELECT CAST(BulkColumn AS XML) FROM OPENROWSET(  
       BULK 'c:tempHerkunftsselect_filtered.xml',  
       SINGLE_BLOB) AS ExecPlan 
   );
WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS SP)
SELECT DISTINCT
       'Database' = n.xml.value('./@Database','nvarchar(100)'),
       'Schema' =          n.xml.value('./@Schema','nvarchar(100)'),
       'Tabelle' =         n.xml.value('./@Table','nvarchar(100)'),
       'Alias' =           n.xml.value('./@Alias','nvarchar(100)'),
       'Column' =          n.xml.value('./@Column','nvarchar(100)')
FROM @xml.nodes('/Root/SP:ColumnReference') n(xml)
WHERE n.xml.value('./@Column','nvarchar(100)') NOT LIKE 'Expr%' 
  AND n.xml.value('./@Column','nvarchar(100)') NOT LIKE 'Chk%'
  AND n.xml.value('./@Column','nvarchar(100)') NOT LIKE 'Bitmap%'
  AND n.xml.value('./@Column','nvarchar(100)') NOT LIKE 'IsBaseRow%'
  AND n.xml.value('./@Column','nvarchar(100)') NOT LIKE 'Union%'
  AND n.xml.value('./@Column','nvarchar(100)') NOT LIKE 'Segment%'
ORDER BY 1,2,3,4,5

现在，唯一缺少的部分是如何完全自动化循环脚本文件、生成执行计划、过滤 xml 和运行查询的过程。我的同事考虑的一种方式可能是将所有脚本文件合并到一个大文件中(循环访问文件并附加它们)，这样手动过程只需要执行一次。

相关内容

最新更新

热门标签：