Search for a stored procedure

最新推荐文章于 2024-04-18 23:32:04 发布

原创最新推荐文章于 2024-04-18 23:32:04 发布 · 149 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#SQL Server

sql server 专栏收录该内容

35 篇文章

订阅专栏

本文介绍了如何在SQL Server 2005中查找特定表被使用的存储过程，搜索存储过程的方法，以及在不使用sp_depends的情况下进行对象依赖检查的技巧。

September 3, 2007 by Pinal Dave
SQL SERVER – 2005 – Search Stored Procedure Code – Search Stored Procedure Text

I receive following question many times by my team members.

How can I find if particular table is being used in the stored procedure?
How to search in stored procedures?
How can I do dependency check for objects in stored procedure without using sp_depends?

I have previously wrote article about this SQL SERVER – Find Stored Procedure Related to Table in Database – Search in All Stored procedure.

The same feature can be implemented using following script in SQL Server 2005.
USE AdventureWorks
GO
--Searching for Empoloyee table
SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%Employee%'
GO
--Searching for Empoloyee table and RateChangeDate column together
SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%Employee%'
AND OBJECT_DEFINITION(OBJECT_ID) LIKE '%RateChangeDate%'
GO
ResultSet:
Name
———————————–
uspGetEmployeeManagers
uspGetManagerEmployees
uspUpdateEmployeeHireInfo
uspUpdateEmployeeLogin
uspUpdateEmployeePersonalInfo

Name
———————————–
uspUpdateEmployeeHireInfo

Reference : Pinal Dave (http://blog.SQLAuthority.com)