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.
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.