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 (https://blog.sqlauthority.com)
89 Comments. Leave new
This is excellent, thanks. I was used to searching sysmessages in previous versions but hadn’t figured this out in 2005 yet.
MSSQL Server 2005 keeps track of the dependencies for you: no need to search the object definition for possibly misleading text chunks. This code returns all stored procedures that access the table Site, showing each column that is referenced:
DECLARE @tabName VARCHAR(60);
SET @tabName = ‘Site’;
SELECT DISTINCT
pr.object_id AS procid,
pr.name, tab.name, col.name,
dep.is_selected, dep.is_updated, dep.is_select_all
FROM sys.procedures pr
INNER JOIN sys.sql_dependencies dep ON pr.object_id = dep.object_id
INNER JOIN sys.columns col ON dep.referenced_major_id = col.object_id
INNER JOIN sys.tables tab ON tab.object_id = col.object_id
WHERE tab.name = @tabName
AND dep.class IN (0, 1)
AND dep.is_updated = 1
— AND col.name = ‘Source’
ORDER BY pr.name
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
Because you are using lower versions than SQL Server 2005
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
Use Table Valued Function
use this query
select * from Tablename where name, &id;
how can i view the sp (already written)
EXEC sp_helptext ‘procedure_name’
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
Thank You Tatyana, that was useful :)
Best answer ever!!!
Thank you very much :)
Itzhak from Israel
how to get the structure of sp of unkown stored procedure name.
Search here
SELECT * FROMinformation_schema.routines
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.
Hi Ravi kanth Ponnam,
Did you find any solution for this issue. If so please send me the solution to dev.srivani@gmail.com.
Appreciate your help.
Thanks
Thanks a lot
You will get simple code to search SP by text Visit this link
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?
Did you mean system procedures like sp_help, sp_helptext?
How can i access Linked server Stored Procedures ?
EXEC LinkedServerName.DBNAME.OwnerName.ProcedureName param1,para2,….
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
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.
just open your databasetable and there you have to have two step there is Programabilty is there just click and get them
just open your databasetable and there you have to take two steps down there is Programabilty is there just click and get them
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
Hi all,
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE ‘%Student%’
AND ROUTINE_TYPE=’PROCEDURE’
IF this views only search the SPs less than 4000 character length. If my SP is more than 4000 character means I can’t get the results.
Why System View INFORMATION_SCHEMA.ROUTINES ROUTINE_DEFINITION Column Size is NVARCHAR(4000)..?
You need to use Object_definition function or system view
Refer
Sir i am need of creating function inside stored procedure can you resolve my doubt since i am new to sql server2005 i’m unable to find solution
You cannot create userdefined function inside a stored procedure
Why do you want to do this?
Hi Pinal,
I have the code to search text in the SP. Is there a way to get LineNumbers for the text?. This helps me pinpoint exact places that need to change.
Regards,
Rohan
One method is
declare @t table(id int identity(1,1), texts varchar(8000))
insert into @t(texts)
EXEC sp_helptext ‘procedure_name’
SELECT * FROM @t