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