如何使用SQL Server 2008根据表名搜索，查找存储过程名称？

Question

我想要查找所有使用特定表的存储过程。由于数据库中有很多存储过程，因此逐个检查不可行。

是否有办法使用搜索查询来查找这些存储过程？

我已经尝试了以下代码：

SELECT distinct so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '% RejectionReason %'

我的表名是RejectionReason，但它显示了所有使用RejectionReason作为列名的存储过程，所以这并不起作用。

- Microsoft Developer

---

我写过一些关于这方面的博客文章，可能会有所帮助：https://sqlblog.org/blogs/aaron_bertrand/archive/2009/11/09/finding-stored-procedures-containing-string.aspx https://sqlblog.org/blogs/aaron_bertrand/archive/2008/09/09/keeping-sysdepends-up-to-date-in-sql-server-2008.aspx - Aaron Bertrand

Answer 1

SELECT o.name, o.type_desc, p.name, p.type_desc
FROM sys.sql_dependencies d
INNER JOIN sys.objects o
    ON d.object_id = o.object_id
INNER JOIN sys.objects p
    ON d.referenced_major_id = p.object_id
    AND o.name = 'RejectionReason'

或者

SELECT o.name, t.TABLE_NAME, c.text 
  FROM syscomments c 
  JOIN sysobjects o 
    ON c.id = o.id
  JOIN INFORMATION_SCHEMA.Tables t
    ON  c.text LIKE '%RejectionReason%' 

或者

EXEC sp_depends @objname = N'RejectionReason';

如果以上方法都不能帮助您，可以查看这篇博客：http://blog.sqlauthority.com/2010/02/04/sql-server-get-the-list-of-object-dependencies-sp_depends-and-information_schema-routines-and-sys-dm_sql_referencing_entities/

- Carlos Quintanilla

---

-1。不正确。必须将@objectname更改为@objname。在更正后将其转换为+1。 - anar khalilov

Answer 2

建议使用 RedGate 公司的免费工具 SQL Search。

- Alex_L

Answer 3

这里有一段代码，希望它能够运行。只需要更改表名，它就可以根据你的代码工作。

SELECT DISTINCT so.name 
FROM syscomments sc INNER JOIN sysobjects so on sc.id=so.id 
WHERE sc.text LIKE '%tablename%'

e.g.:

SELECT DISTINCT so.name 
FROM syscomments sc INNER JOIN sysobjects so on sc.id=so.id 
WHERE sc.text LIKE '%users%'

您将获得存储过程列表和表关系。

- Praveen04

---

非常好用，我也在 SQL Server 2014 上测试过了！谢谢。 - Aimal Khan

Answer 4

根据MSDN，sp_depends将在未来的版本中被移除。如果您正在使用它，可以改用以下查询语句：

SELECT referencing_schema_name, referencing_entity_name, referencing_id, referencing_class_desc, is_caller_dependent
FROM sys.dm_sql_referencing_entities ('dbo.TableName', 'OBJECT');

- S2S2

Answer 5

我知道有两种可能性。

首先，SQL Management Studio有一个选项可以显示依赖项。右键单击表格，选择查看依赖项。但是，这不会突出显示嵌入动态SQL中的表名。

第二个选项是右键单击数据库，选择生成脚本。按照向导操作并将所有usps脚本到新的查询窗口，然后在其中搜索您的表名。这更费力，但会找到所有用途。

- BonyT

Answer 6

我猜这个脚本显示了表的所有依赖对象，包括存储过程。

USE MYDatabase
GO

DECLARE @TableName varchar(100)
SET @TableName = 'mytable'

SELECT
 SourceSchema                  = OBJECT_SCHEMA_NAME(sed.referencing_id)
 ,SourceObject                 = OBJECT_NAME(sed.referencing_id)
 ,ReferencedDB                 = ISNULL(sre.referenced_database_name, DB_NAME())
 ,ReferencedSchema             = ISNULL(sre.referenced_schema_name,
OBJECT_SCHEMA_NAME(sed.referencing_id))
 ,ReferencedObject             = sre.referenced_entity_name
 ,ReferencedColumnID   = sre.referenced_minor_id
 ,ReferencedColumn             = sre.referenced_minor_name
FROM sys.sql_expression_dependencies sed
CROSS APPLY sys.dm_sql_referenced_entities(OBJECT_SCHEMA_NAME(sed.referencing_id)
+ '.' + OBJECT_NAME(sed.referencing_id), 'OBJECT') sre
WHERE sed.referenced_entity_name = @TableName
AND sre.referenced_entity_name = @TableName

如需更多详细信息，请查看以下链接： http://sqlserverplanet.com/sql-server-2008/find-dependent-objects/

- Sumit

Answer 7

SysObjects 存储有关数据库中所有对象的基本信息。这对您很有用，因为它告诉我们每个对象的名称和类型。

SysComments 存储存储过程和函数的实际文本（代码）。它包含一个 ID 字段，该字段映射回 SysObjects 中的 id 字段。

select so.name, text
from sysobjects so, syscomments sc
where so.id = sc.id
and text like '%RejectionReason%'

- Karthi Rathinavelu

Answer 8

这将返回存储过程和视图。

SELECT DISTINCT o.name AS Object_Name,o.type_desc
FROM sys.sql_modules m 
INNER JOIN sys.objects o 
    ON m.object_id=o.object_id
WHERE m.definition Like '%TableName%'

- Nalan Madheswaran

Answer 9

我正在使用以下SQL脚本来搜索数据库中所有存储过程中的列名和文本。您也可以使用它来查找存储过程中的表。

在变量@SearchFor中指定搜索条件（就像在LIKE表达式中使用它一样，例如'%LastName%'以查找包含LastName的列和存储过程）。

它将在表中查找列名以及存储过程内部的文本。如果将@SPNameOnly设置为0，则该脚本甚至可以显示SP源代码。

--
-- Purpose: Search field names in all tables, views stored procedures
--

DECLARE @SearchFor nvarchar(max)='%Search_SP_Or_Table_Or_View%' -- search for this string
DECLARE @SearchSP bit = 1 -- 1=search in SPs as well
DECLARE @DisplaySPSource bit = 1 -- 1=display SP source code

-- tables
if (@SearchSP=1) begin  
  (
  select '['+c.table_Schema+'].['+c.table_Name+'].['+c.column_name+']' [schema_object], 
            t.table_type 
  from information_schema.columns c
  left join information_schema.Tables t on c.table_name=t.table_name
  where column_name like @SearchFor or t.table_name like @SearchFor 
  UNION
  select '['+routine_Schema+'].['+routine_Name+']' [schema_object], 
         'PROCEDURE' as table_type from information_schema.routines
  where routine_definition like @SearchFor or routine_name like @SearchFor 
        and routine_type='procedure'
  )
  order by table_type, schema_object
end else begin
  select '['+c.table_Schema+'].['+c.table_Name+'].['+c.column_name+']' [schema_object], 
         t.table_type 
  from information_schema.columns c
  left join information_schema.Tables t on c.table_name=t.table_name
  where column_name like @SearchFor or t.table_name like @SearchFor 
  order by c.table_Name, c.column_name
end     
-- stored procedure (source listing)
if (@SearchSP=1) begin      
    if (@DisplaySPSource=1) begin
      select '['+routine_Schema+'].['+routine_Name+']' [schema.sp], routine_definition 
      from information_schema.routines
      where routine_definition like @SearchFor  or routine_name like @SearchFor 
      and routine_type='procedure'
      order by routine_name
    end
end

- Matt