sql - 访问另一个数据库中的sys.sql_modules

Question

以下查询列出了数据库中的所有存储过程及其定义。它返回 12 行:

USE [HQ-211-STOCK-DBO]

SELECT OBJECT_NAME (object_id) AS sp_name, 
       [definition] AS sp_definition
FROM [HQ-211-STOCK-DBO].sys.sql_modules as st
WHERE OBJECTPROPERTY (object_id, 'IsProcedure') = 1

当我更改查询的第一行以使用不同的数据库时:

USE [HQ-193-STOCK-DBO]

SELECT OBJECT_NAME (object_id) AS sp_name, 
       [definition] AS sp_definition
FROM [HQ-211-STOCK-DBO].sys.sql_modules as st
WHERE OBJECTPROPERTY (object_id, 'IsProcedure') = 1

返回零行。

当我现在更改查询的第 4 行(FROM 行)时:

USE [HQ-193-STOCK-DBO]

SELECT OBJECT_NAME (object_id) AS sp_name, 
       [definition] AS sp_definition
FROM [HQ-193-STOCK-DBO].sys.sql_modules as st
WHERE OBJECTPROPERTY (object_id, 'IsProcedure') = 1

我返回一行。

似乎用数据库名称作为前缀 sys.sql_modules 并没有像我预期的那样工作。

有人可以指出我做错了什么吗？为什么引用 sys.sql_modules 时无法指定数据库名称？

Answer 1

OBJECT_NAME() 和 OBJECTPROPERTY() 查看当前数据库。停止使用它们。通常您不会得到匹配项(这使得您的 WHERE 子句在第二个查询中返回 0 行 - 您可以通过仅返回 object_id 来确认这一点，然后检查当前数据库的 sys.objects 对于任何这些值)，但有时如果您碰巧在两个对象中具有相同的 object_id ，您可能会得到错误匹配数据库。

( They also ignore isolation semantics, so can be completely blocked by other queries, even if you are using READ UNCOMMITTED / NOLOCK 。因此，即使在相同数据库中，我也强烈建议使用与其他目录 View 的联接，而不是辅助元数据函数。通常这不是问题，但他们会得到我保证，会在最不合时宜的时候被封锁。)

相反，你可以说:

SELECT o.name AS sp_name, st.[definition] AS sp_definition
FROM [HQ-211-STOCK-DBO].sys.sql_modules as st
INNER JOIN [HQ-211-STOCK-DBO].sys.objects AS o
ON st.[object_id] = o.[object_id]
WHERE o.type = 'P';