I'm migrating a legacy SQLS2k to 2008R2, and it seems all data access was done through stored procs, and any custom queries use the legacy *= =* outer join syntax. There are upwards of a hundred procs so I don't want to open each one individually to see if it uses that syntax (most wouldn't), is there a way I can query the metadata for a list of procs/functions/views/triggers, then loop through searching for the *= or =* strings, printing out the name of the offending object?
我正在将旧版SQLS2k迁移到2008R2，似乎所有数据访问都是通过存储过程完成的，并且任何自定义查询都使用legacy * = = * outer join语法。有超过一百个触发因此我不想单独打开每一个以查看它是否使用该语法（大多数不会），是否有一种方法可以查询元数据以获取一个procs / functions / views列表/触发器，然后循环搜索* =或= *字符串，打印出违规对象的名称？
My background is oracle, I know how to find the metadata views there, but I'm a bit new to Sql Server. Downgrading the compatibility version is not an option.
我的背景是oracle，我知道如何在那里找到元数据视图，但我对Sql Server有点新意。降级兼容性版本不是一种选择。
thanks!
谢谢！
2 个解决方案
#1
29
免费Red Gate SQL搜索？
Or query sys.sql_modules
或者查询sys.sql_modules
SELECT OBJECT_NAME(object_id)
FROM sys.sql_modules
WHERE definition LIKE '%=*%' OR definition LIKE '%*=%'
Note: INFORMATION_SCHEMA views and syscomments truncate the definition so are unreliable.
注意：INFORMATION_SCHEMA视图和syscomments截断定义，因此不可靠。
#2
2
Problem with using queries is that these don’t work if stored procedure is encrypted unless you’re running DAC connection type.
使用查询的问题是，如果存储过程已加密，则这些操作无效，除非您正在运行DAC连接类型。
This is where third party tool come in handy because they help you do this without too much hassle. I’m using ApexSQL Search that’s free but I guess you can’t go wrong with Red Gate or any other tool out there.
这是第三方工具派上用场的地方，因为它们可以帮助您在没有太多麻烦的情况下实现这一目标。我正在使用免费的ApexSQL搜索，但我想你不能错过Red Gate或其他任何工具。
#1
29
免费Red Gate SQL搜索？
Or query sys.sql_modules
或者查询sys.sql_modules
SELECT OBJECT_NAME(object_id)
FROM sys.sql_modules
WHERE definition LIKE '%=*%' OR definition LIKE '%*=%'
Note: INFORMATION_SCHEMA views and syscomments truncate the definition so are unreliable.
注意：INFORMATION_SCHEMA视图和syscomments截断定义，因此不可靠。
#2
2
Problem with using queries is that these don’t work if stored procedure is encrypted unless you’re running DAC connection type.
使用查询的问题是，如果存储过程已加密，则这些操作无效，除非您正在运行DAC连接类型。
This is where third party tool come in handy because they help you do this without too much hassle. I’m using ApexSQL Search that’s free but I guess you can’t go wrong with Red Gate or any other tool out there.
这是第三方工具派上用场的地方，因为它们可以帮助您在没有太多麻烦的情况下实现这一目标。我正在使用免费的ApexSQL搜索，但我想你不能错过Red Gate或其他任何工具。