如何查找数据库中使用 UNION 的所有对象

发布于 2024-12-09 14:09:45 字数 79 浏览 1 评论 0原文

如何找到包含 UNION 关键字的所有视图和 SP？

（另一个问题：SP和视图的文本存储在哪个系统表中）

谢谢

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

凤舞天涯 2024-12-16 14:09:45

这是一个有点幼稚但可行的示例：（在 SQL Server 2005 和 2008 R2 上测试）

use msdb;

SElECT * FROM sys.all_sql_modules
WHERE  
(definition LIKE '%CREATE VIEW%' OR 
 definition LIKE '%CREATE PROCEDURE%')
AND definition LIKE '%UNION%'

要巩固它，只需通过 object_id 链接进行连接，并按对象类型过滤视图和存储过程，而不是使用 LIKE。

更简单：

SElECT * FROM sys.all_sql_modules
WHERE definition LIKE '%UNION%'

警告：由于我们正在对每个对象的创建脚本执行 LIKE 比较，因此如果/当它出现在注释中时，我们还将选择 UNION 一词。这对您来说可能没问题，但如果您需要结果更具确定性并避免这种情况，您的 WHERE 子句将需要更复杂，可能使用正则表达式来优化结果。

Here is a somewhat naive but workable example: (tested on SQL Server 2005 & 2008 R2)

use msdb;

SElECT * FROM sys.all_sql_modules
WHERE  
(definition LIKE '%CREATE VIEW%' OR 
 definition LIKE '%CREATE PROCEDURE%')
AND definition LIKE '%UNION%'

To solidify it just join via the object_id link and filter for views and stored procs by object type rather than using LIKE.

Simpler:

SElECT * FROM sys.all_sql_modules
WHERE definition LIKE '%UNION%'

Caveat: Since we're performing a LIKE comparison against the create script of each object we will also pick up on the word UNION if/when it appears in comments. That might be OK for you but if you need to results to be more deterministic and avoid such cases your WHERE clause will need to be more complex, possibly using regex to refine the results.

回复收藏 0 原文

话少情深 2024-12-16 14:09:45

就我个人而言，我喜欢使用 INFORMATION_SCHEMA 视图：

对于存储过程：

SELECT 
    * 
FROM 
    INFORMATION_SCHEMA.ROUTINES 
WHERE 
    ROUTINE_TYPE = 'PROCEDURE' 
    AND 
    --ROUTINE_DEFINITION LIKE '%union%' <-- 4,000 character limitation
    OBJECT_DEFINITION(OBJECT_ID(SPECIFIC_SCHEMA + '.' + SPECIFIC_NAME)) 
        LIKE '%union%'

对于视图：

SELECT 
    * 
FROM 
    INFORMATION_SCHEMA.VIEWS 
WHERE 
    --VIEW_DEFINITION LIKE '%union%' <-- 4,000 character limitation
    OBJECT_DEFINITION(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME)) LIKE '%union%'

这些视图仅限于您当前使用的数据库中的定义。

或者，xSQL 软件中的一个出色工具可为您提供强大的 Microsoft SQL 搜索功能Server 2005 和 2008。

更新：正如 @Bogdan 指出的，INFORMATION_SCHEMA 定义仅限于前 4000 个人物。我已经更新了我的答案以包含解决方法。此时，跟踪 sys 视图可能会更容易。

Personally I like using the INFORMATION_SCHEMA views:

For stored procedures:

SELECT 
    * 
FROM 
    INFORMATION_SCHEMA.ROUTINES 
WHERE 
    ROUTINE_TYPE = 'PROCEDURE' 
    AND 
    --ROUTINE_DEFINITION LIKE '%union%' <-- 4,000 character limitation
    OBJECT_DEFINITION(OBJECT_ID(SPECIFIC_SCHEMA + '.' + SPECIFIC_NAME)) 
        LIKE '%union%'

And for views:

SELECT 
    * 
FROM 
    INFORMATION_SCHEMA.VIEWS 
WHERE 
    --VIEW_DEFINITION LIKE '%union%' <-- 4,000 character limitation
    OBJECT_DEFINITION(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME)) LIKE '%union%'

These views are limited to definitions in whichever database you're currently USEing.

Alternatively, there is a wonderful tool from xSQL software that gives you powerful searching capabilities for Microsoft SQL Server 2005 and 2008.

UPDATE: As @Bogdan pointed out, the INFORMATION_SCHEMA definitions are limited to the first 4000 characters. I've updated my answer to include a workaround. At this point it may just be easier to go after the sys views.

回复收藏 0 原文