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)
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 [...]
how to find database name is already use in win form at runtime
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;
[...] 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)
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
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
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?
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,….
@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.
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
http://beyondrelational.com/blogs/madhivanan/archive/2010/02/02/routine-definition-from-information-schema-routines.aspx
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
i want to stored procedure for fetch data from more than two table.
You can use a join
or if you want two resultsets
select columns from table1
select columns from table2
Such a shame this sort of functionality can’t be tied in to the “Edit | Find” dialog in Management Studio.
ApexSQL Edit does this really well but I’d rather not have to use two editors.
Sigh.
I want to get from basic knowledge abou the Stored procedure.
In SQL Server help file, refer this link
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/fb4d45b6-faef-4a83-aa1b-44a2c8fda48e.htm
Nice post..Keep them coming :) Thanks for sharing.
Hai please explain the following statement in sql server 2005
set @days=’1=1′
Post the full code you used
Probable there is a dynamic sql where WHERE clause is constructed based on the inputs
Hi Sir,
how to execute stored procedure instead of ‘exec’ in sqlserver?
You can’t execute it without using EXECUTE
Colud you please help to reach out…
I am storing VarBinary data in a column of table.(data may be …Img / Doc/ txt ect). now i want to create a physical file on server disk, using the table data by either stored procedure or trigger… which is executing on server side.
hi sir
this is chaitanya .is there any statement to find select statement which is written in stored procedure .if any statement is there plz tell the quary.
thanks
One option is to script the procedures to text file and do search
Hello Pinal,
Data Synchronization In SQL Server database table using sql script or TSQL script or code.
I want to Synchronize my SQL Server database table at server end using client database table’s records.
Actually, I have to updated SQL Server database table data/records with the client database.
The client is sitting in the districts, who is entering the data/records collected from villages and block where there is no Internate connection, so that
data/records are entered offline at the client database these records must be updated and reflected at the SQL Server database at the end.
If this can be accomplish by using TSQL ,Stored Procedure.
Deepak Chandra
Hi. I have scripted all stored procedures into a big script and now I have to replace “dbo.” with my new schema name given by
(select Table_schema from INFORMATION_SCHEMA.TABLES where TABLE_NAME=@Schemaname)
In my script some tables don’t have “dbo.”, some have “fn_” in front of them.
Did anyone did this and have some code related to it?
I would appreciate your help.
Denise
I have missed to mention my tables from the script that needs to be updated with schema names may contain in front of them any of “UPDATE”, “DELETE”, INTO”, “TRUNCATE”, “dbo.”, “FROM” and also has to ignore while spaces and line breaks.
Thank you very much in case anyone has any code already done.
how i can pass parameters to other Sp in another stored procedure
would u pls send me the code for advance searching
I am new to the software development so would u help me by giving a search query
What will be used to find a text in user-defined-function instead of procedure (sys.procedures) ?
I have checked with sys.functions.
But its not working.
Please help.
Dear
Sir/Mam
I am shipra, Jaipur.
I want to know all the types of stored procedures in SQL Server 2008 of Asp.net C sharp code and their uses.Kindly sort out my Query.
Hello Dave,
how can I read encrypted Stored procedures.
Thanks
mike d.
@Mike,
Tell me what do you get when you search for the string “how to decrypt stored procedure in sql 2005″ in Google.com
~IM.
I want create complex view of SQL Server for a ERP Project. But I am not habituate with creating view in SQL Server 2005. I need a proper guideline.
Find Text in table-valued function.
I was able to modify the query to search stored procedures to search table-valued functions if it is of use to anyone. I needed to change column references in a number of places and this was helpful to me in verifying I found all the places I needed to modify.
SELECT o.Name
FROM sys.objects o
WHERE type = ‘IF’ AND OBJECT_DEFINITION(OBJECT_ID) LIKE ‘%Employee%’
The only difference from the statement that Pinal posted is that the Id for the OBJECT_DEFINITION function is from the sys.objects view instead of the sys.procedures view.
create procupval
as
begin
declare @price int
select @price = avg(price) from Toys_DataBind
while( @price < 22)
begin
update Toys_DataBind set price = price + 2.50
break;
end
end
exec upval
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,’;')
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