I receive following question many times by my team members. They want to search stored procedure code for a table or column name.
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
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
89 Comments. Leave new
following error occur when i execute the procedure…although it parse sucessfully…
Msg 2714, Level 16, State 3, Procedure sp_GetInventory, Line 9
There is already an object named ‘sp_GetInventory’ in the database.
can any one tell me how to execute stored procedure????i am using SQL server 2005 first time..
Hi
I Solved my Problem by Ur Post
Thanks a lot …
how can I find out datbase name from store procedure which is run in job’s step.
Thanks,
can u tell me abt syntax of store procedure {control structure (Looping & if ) }
Read about them in sql server help file
Could anyone give pls give any information about how can i use user defined function in sore procedure using sql server 2005??
You can use function in the procedure like below
create procedure proc_name
as
.
.
select dbo.function_name(parameter)
Hi,
Please give me the solution for:
I have Stored Procedure name: USP_Get_Daily_Report_PriorityWise
ALTER PROC [dbo].[USP_Get_Daily_Report_PriorityWise]
AS
Begin
set nocount on
DECLARE @Loc_Table TABLE (Counts INT, Internal_Priority VARCHAR(20), Process_Area VARCHAR(1000))
INSERT INTO @Loc_Table
SELECT COUNT(*),Internal_Priority, Process_Area FROM AgedReportPivot (NOLOCK)
WHERE (Internal_Priority IN (‘1′,’2′,’3′,’4′,’Other’))
AND (Status NOT IN (‘CANCELLED’, ‘RESOLVED’, ‘CLOSED’))
GROUP BY Process_Area,Internal_Priority
DECLARE @loc_Result Table (Process_Area VARCHAr(1000),a INT,b INT,c INT,d INT,Other VARCHAr(10))
–DECLARE @loc_Result Table (Process_Area VARCHAr(1000),1 INT,2 INT,3 INT,4 INT,Other VARCHAr(10))
INSERT INTO @loc_Result
SELECT Process_Area,[1] as a,[2]As b,[3]AS c,
[4] AS d,[Other] AS Others
FROM(SELECT Counts,Internal_Priority,Process_Area
FROM @Loc_Table
) p
PIVOT
(SUM(Counts)
FOR Internal_Priority IN ([1],[2],[3],[4],[Other]))
AS pvt
————————PSI last 30 days data———————————
declare @PSIData int;
SELECT @PSIData = COUNT(*) FROM dbo.PSIAgedRequestAll (NOLOCK)
WHERE (Status NOT IN (‘CANCELLED’)) AND (Reported_Date >= GETDATE() – 30)
AND (Reported_Date = GETDATE() – 30)
AND (Reported_Date = GETDATE() – 30)
AND (Reported_Date <= GETDATE())
set nocount off
END
There are 8 columns:
LastDays is Last 30 days Requests inflow (Open, Closed, Resolved etc).
Priority1,Priority2, Priority3, Priority4,PriorityOthers, are all open request count.
Total=Priority1+Priority2+Priority3+Priority4+PriorityOthers
Result:
Priority1 Priority2 Priority3 Priority4 PriorityOthers Process_Area LASTDAYS Total
NULL NULL 1 NULL 7 Customer Masterdata 13 61.54
NULL 1 6 NULL 15 P2P 48 45.83
NULL NULL 3 NULL 1 CRM-Complaints 9 44.44
NULL 2 3 NULL 6 Material MasterData 25 44
NULL NULL 4 NULL NULL CRM Field Service 10 40
NULL 2 11 NULL 1 TheEdge 36 38.89
NULL 10 31 NULL 57 Finance 305 32.13
NULL 1 24 NULL NULL Non-ERP 92 27.17
NULL NULL 4 NULL 4 CRM Internet Sales 36 22.22
NULL 1 9 NULL 10 MFG 92 21.74
NULL 4 10 NULL NULL GEMS 65 21.54
NULL 10 33 NULL 132 PSI 395 19.25
NULL 3 3 1 20 EHS 163 16.56
NULL 3 9 NULL 42 OTC 364 14.84
NULL 4 11 NULL 1 APO 122 13.11
NULL 2 3 NULL 14 BI 281 6.76
NULL 1 3 NULL 6 ABAP 152 6.58
NULL 9 5 NULL 2 Basis 362 4.42
My Requirement in the same table:
Now I want to fibercate data according to Priority wise and in the same need to show out of last 30 days request count how many are Priority 1, Priority 2 etc
Ex:
If Priority 3 request count for Process_Area P2P are 6. Out of last 30 days 48 request count for P2P,how many are Priority 3?
Priority 3
10 6
Here 10 are Inflow Priority 3 out of 48 requests and 6 is Open for Priority 3.
You are a handy-man.
Thanks a lot for undertaking all hard-work.
i m giving some CreateXmlFile
procedure using visual studio
————-procedure WriteDataToXml—————————–
Create proc Writedatatoxml @tbname nvarchar(20),@path nvarchar(100)
as
external name myxmlconvertionassembly.[createxml.converttoxml].writexml
how to add validation & condition (time) for store procedure
Hi,
How can i get the particular character location/position from table column.
ex. select * from TABLENAME Functionname(table_column,’;’)
use patindex or charindex
hello sir … I am using 3 updated query inside the stored procedure… Like..
declare @qry nvarchar(500)
begin transaction
update table1 set status=’A’ where appno=@appno
update table2 set entrydate=@entrydate where appno=@appno
set @qry=’update ‘ + tablename+ ‘ set status=@status where appno=@appno
exec sp_executesql @qry
commit transaction
when I am calling this SP from my .net code .. the queries inside transaction are not updating the table and SP give no error … what should I do?? how to troubleshoot this problem…… thanks in advance
Hi,
I have written stored procedure with the where clause(e.g description=’End of process’). Can i find stored procedures with the above description.
Thanks
Poongodi
I HAVE TO PASS ARGUEMENTS FROM ONE SP TO ANOTHER. WHAT SHOULD I DO????
You can call another procedure inside the main procedure that uses main procedure’s argument
hello sir
I want to search a particular string from text and retrieve the sub-string which is separated by ‘.’ and that sentence contains searching string
Thanks for the excellent post!
How I can Get all Store Procedure in which a table insert its value
What do you mean by it?
How to pass a database name as input parameter to a stored procedure in SQL Server 2008?
CREATE PROCEDURE [dbo].[SP_BUSCA_CONTENIDO_ALL_SPS] @BUSCAR NVARCHAR(800)
AS
BEGIN
DECLARE @COMANDO AS NVARCHAR(800)
— create temporary Result table to gather names and text
— of the procedures in the DataBaseName database :
IF OBJECT_ID(‘tempdb..#Result’) IS NOT NULL drop table #Result
IF OBJECT_ID(‘tempdb..#ProcList’) IS NOT NULL drop table #ProcList
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
— Visualiza el resultado de la busqueda en una tabla
SET @COMANDO = 'SELECT * FROM #Result WHERE TextField LIKE '+CHAR(39)+'%'+@BUSCAR+'%'+CHAR(39)
EXEC SP_EXECUTESQL @COMANDO
— clean up
DROP TABLE #Result
DROP TABLE #ProcList
END
select object_name(id) from syscomments where text like ‘%searchtext%’
What’s up, everything is going sound here and ofcourse every
one is sharing data, that’s genuinely fine, keep up
writing.