sql-server - 在现有程序中定位旧式连接

Question

我正在将二十多岁的数据库迁移和重构到 2019 年。它们都至少包含一些使用旧的非 ANSI 连接的存储过程。总共有超过 4,800 个程序，所以我不想手动通过它们来寻找旧式连接。作为一个额外的复杂因素，许多旧的连接都嵌入在动态 sql 中，而 SQL 升级顾问没有捕捉到这些。

我在想必须有一种方法来查询 sys.sql_modules 来识别它们，但想不出在定义列上为条件写什么。任何人都可以提供建议吗？？？下面，或提供不同的方法？

```
SELECT 
    objects.object_id
,   schemas.name AS [Schema]
,   objects.name AS Object_Name
,   objects.type_desc AS [Type]
,   sql_modules.definition AS Definition
FROM 
    sys.sql_modules sql_modules
INNER JOIN sys.objects objects 
    ON sql_modules.object_id = objects.object_id 
INNER JOIN sys.schemas schemas 
    ON objects.schema_id = schemas.schema_id 
WHERE 
    sql_modules.definition COLLATE SQL_Latin1_General_CP1_CI_AS LIKE '%??????????????????%' ESCAPE '\' 
AND objects.type_desc = 'SQL_STORED_PROCEDURE';
```

标签： sql-server

Answer 1

使用clr 解析器函数 dbo.parseSqlToXml()

--a dummy proc，用于测试

create procedure testxyz
as
begin

exec('
select *
from tableA as a, tableB as b
where a.id = b.columnid
')


exec('
select *
from X as x
join Y as y on x.col1 = y.col2
where x.id = y.columnid
')


exec('
select *
from (select * from X) as x
join (select * from Y) as y on x.col1 = y.col2
where x.id = y.columnid
')

exec('
select *
from (select * from a, b where a.foo = b.bar) as x
join (select * from Y) as y on x.col1 = y.col2
where x.id = y.columnid
');

select *
from (select * from a, b where a.foo = b.bar) as x
join (select * from Y) as y on x.col1 = y.col2
where x.id = y.columnid;

exec sp_executesql N'select * from k, l where k.foo = l.bar '

declare @sql nvarchar(max) = N'select * from o,p where o.id = p.id';
exec sp_executesql @sql


declare @sql1 nvarchar(max) = N'select * from r,s where r.id = s.id';
exec(@sql1)



--constructed dynamic sql ....
declare @sql2 nvarchar(max) = N'select * from ';
select @sql2 = @sql2 + 'pr,qr where pr.id = qr.id';
exec(@sql2)


update tableX
set col1 = 123
from tableX, TableY
where TableX.id = TableY.id

delete a
from tableAB as a, tableCD as b
where a.id = b.id

insert into #temp(x, y, z)
select a, b, c
from tableX, tableY
where idX=idY


end

....以下...

--slow...better execute this per proc, or for iterate through batches of procs/modules
select top (50)
    object_name(m.object_id) as modulename,
    sfc.f.value('data((./../../*[local-name() = ("SqlQuerySpecification", "SqlUpdateSpecification", "SqlDeleteSpecification")]/comment())[1])', 'nvarchar(max)') as thequery,
    sfc.f.query('data(comment()[1])') as fromclause,
    stmt.isdynamicsql
from sys.all_sql_modules as m
cross apply (select cast(dbo.parseSqlToXml(m.definition) as xml) as sqlparse) as prs
cross apply
(
    --normal sql
    select prs.sqlparse as thesql, 0 as isdynamicsql
    union all
    select 
    --dynamic (exec/sp_executesql) sql in token_string
    cast(dbo.parseSqlToXml(stuff(reverse(stuff(reverse(replace(replace('xN'+rtrim(ltrim(replace(replace(t.s.value('.', 'nvarchar(max)'), '\r\n', ' '), '\t', ' '))), 'xNN', 'xN'), 'XN', '')), 1, 1, '')), 1, 1, '')) as xml) as thesql, 1 as isdynamicsql
    from prs.sqlparse.nodes('SqlScript/SqlBatch/Tokens/Token[@type="TOKEN_STRING"][contains(lower-case(.), "from")]') as t(s)
) as stmt
--..each fromclause in thesql
cross apply stmt.thesql.nodes('//SqlFromClause[count(SqlDerivedTableExpression) + count(SqlTableRefExpression) > 1]') as sfc(f)
order by m.object_id desc;

...返回：

+--------------------------+-----------------------------------+------------------------------------------+--------------+
| modulename               | thequery                          | fromclause                               | isdynamicsql |
+--------------------------+-----------------------------------+------------------------------------------+--------------+
| testxyz                  | select * from a, b where a.foo... | from a, b                                | 0            |
| testxyz                  | update tableX set col1 = 123 f... | from tableX, TableY                      | 0            |
| testxyz                  | delete a from tableAB as a, ta... | from tableAB as a, tableCD as b          | 0            |
| testxyz                  | select a, b, c from tableX, ta... | from tableX, tableY                      | 0            |
| testxyz                  | select * from tableA as a, tab... | from tableA as a, tableB as b            | 1            |
| testxyz                  | select * from a, b where a.foo... | from a, b                                | 1            |
| testxyz                  | select * from k, l where k.foo... | from k, l                                | 1            |
| testxyz                  | select * from o,p where o.id =... | from o,p                                 | 1            |
| testxyz                  | select * from r,s where r.id =... | from r,s                                 | 1            |
| sp_MShelpfulltextscript  | select @catname = f.name from ... | from dbo.sysfulltextcatalogs f, dbo.syso | 0            |
| sp_MSgetalternaterecgens | select distinct p.pubid as alt... | from dbo.sysmergesubscriptions s, dbo.sy | 0            |
| sp_check_sync_trigger    | select @trigid = so1.object_id... | from sys.objects so1, sys.objects so2    | 0            |
+--------------------------+-----------------------------------+------------------------------------------+--------------+

并非所有构造的 sql 都被正确“解析”。

或者，扩展事件可以捕获已弃用的功能。