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)
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?
How can i access Linked server Stored Procedures ?
@Raja
Please look at this link,
http://blog.sqlauthority.com/2007/10/06/sql-server-executing-remote-stored-procedure-calling-stored-procedure-on-linked-server/
Regards,
IM.
Hi This is manohar,
I Need Search StoredProcedure.
i have 2 columns in Table one Column is ‘JobId’ and secoud Column is ‘Location’
select JobId,JObLocation From Jobs
i need search Procedure Two Columuns at a time
if one culumn left onethe column result should disply vise virsa
and if i am giving half of text to search for any of column it should give the result with that …….
please give Procedure for it
[...] SQL SERVER – 2005 – Search Stored Procedure Code – Search Stored Procedure Text [...]
hi pinel,
i’m new to this software field. actually i created one database table in db. could u tell me how to retrieve that table in stored procedure.
Hi Mahalaxmi,
Its very easy to get the details from table through stored procedure
If the table name is dbo.employee
SP Creation:
create procedure dbo.getemployeedetails
as
begin
select * from dbo.employee
end
execute this procedure in query window…
exec dbo.employeedetails
hi sir i want stored procedure quriees just like select,insert ,delete,updatecommands ple