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://www.SQLAuthority.com)
pinaldave
This is excellent, thanks. I was used to searching sysmessages in previous versions but hadn’t figured this out in 2005 yet.
[...] : SQL SERVER - 2005 - Search Stored Procedure Code - Search Stored Procedure Text, SQL SERVER - Find Stored Procedure Related to Table in Database - Search in All Stored [...]
Hi Pinal,
When i executed the below uqery provided by u:–
GO
SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE ‘%tblseason%’
GO
I got belwo error:–
Server: Msg 195, Level 15, State 10, Line 3
‘OBJECT_DEFINITION’ is not a recognized function name.
Please tell me teh resolution.
Meanwhiel i am using sp_depends for teh same purpose
Thanks,
Bobby
Hi.
How can I get the internal text of stored procedure with the known name of that stored procedure?
@Eugene:
sp_helptext ‘procedure_name’ gives you internal SP text…
HI,
I need to return 2 values from a function so that i can populate in select statement
[...] SQL SERVER - 2005 - Search Stored Procedure Code - Search Stored Procedure Text [...]
[...] SQL SERVER - 2005 - Search Stored Procedure Code - Search Stored Procedure Text [...]
how can i view the sp (already written)
Here is a query to look for a string in all the database’s procedures:
——————————————————-
USE DataBaseName
GO
–
– create temporary Result table to gather names and text
– of the procedures in the DataBaseName database :
CREATE TABLE #Result
(TextField varchar(max), ProcName varchar(100))
– create temporary ProcName table with the names of
– all the procedures in the database [DataBaseName]:
CREATE TABLE #ProcList
(ID int IDENTITY, ProcName varchar(100))
– populate the ProcName table with the procedure names:
INSERT #ProcList SELECT [name] from sys.procedures
– get the number of procedures (to be used in the loop below):
DECLARE @NumberOfProcs int
SELECT @NumberOfProcs = COUNT(*) FROM sys.procedures
– loop to populate the Result table:
DECLARE @i INT
SET @i = 1
DECLARE @ProcName varchar(100)
DECLARE @SQL varchar(2000)
WHILE @i <= @NumberOfProcs
BEGIN
SELECT @ProcName = ProcName FROM #ProcList WHERE ID = @i
SET @SQL = ‘INSERT INTO #Result (TextField) EXEC sp_helptext ‘ + @ProcName
EXEC (@SQL)
UPDATE #Result SET ProcName = @ProcName WHERE ProcName IS NULL
SET @i = @i + 1
END
– look for a string you need [your string] in the Result table
SELECT * FROM #Result WHERE TextField LIKE ‘%your string%’
– clean up
DROP TABLE #Result
DROP TABLE #ProcList
how to get the structure of sp of unkown stored procedure name.
I found one issue with the above given code.
suppose say my table name existing as a alias name in any stored procedure, then even that stored procedure is getting displayed as dependent on the stored procedure, which is incorrect.
E.g.:
SELECT Name FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) like ‘%books%’
then suppose say, I have created a SP as follows,
create procedure employeekidding
as
select empno,managerno*deptno as books from employee
Issue:
the above created procedure is getting displayed as if it is dependent on books table, which is not.
Thanks a lot
You will get simple code to search SP by text Visit this link
http://www.tctcworld.com/dotnet/viewtopic.php?f=4&t=43
Thanks
This is Siddhesh
I need a search algorithm which when ran on any server can reveal names of all the stored procedures stored on it .
Does ant 1 has any idea?
Where is the code for the stored procedure stored within SQL Server?