Searching SQL Server 2000
Any ideas?
Thanks all
Jim
[257 byte] By [jcb1269] at [2007-11-11 7:13:49] 
Is there any way to search each stored procedure in your database that uses a specific table? I have over 100 stored procedure and I really don't want to open each one and look for the table I need.
Any ideas?
Thanks all
Jim
# 1 Re: Searching SQL Server 2000
A Google search for "search stored procedure" found these:http://www.freevbcode.com/ShowCode.asp?ID=6521
http://vyaskn.tripod.com/sql_server_search_stored_procedure_code.htm
Phil Weber at 2007-11-11 23:47:52 >
# 2 Re: Searching SQL Server 2000
jimjcb1269 at 2007-11-11 23:48:57 >
# 3 Re: Searching SQL Server 2000
In Enterprise Manager , on the when you right click on the table and choose All Tasks > Display Dependencies you would get the dependencies on the tablesrinivas_s at 2007-11-11 23:49:56 >
# 4 Re: Searching SQL Server 2000
Is the a way in Enterprise mgr or QA to copy the contents of a stored procedure to a text file.Thank for the help all..
Jim
jcb1269 at 2007-11-11 23:51:01 >
# 5 Re: Searching SQL Server 2000
Normally on objects created by the user , when you right click , and choose all tasks you will have a option generate SQL Script available there are many options you can configure of what you want to script so check it outsrinivas_s at 2007-11-11 23:51:54 >
# 6 Re: Searching SQL Server 2000
In Query Analyser try this queryCREATE PROCEDURE [dbo].[GetSPCode]
(@SPName VARCHAR(100)
) AS
SELECT
c.text
FROM
sysobjects o
INNER JOIN
syscomments c
ON o.id = c.id
WHERE o.type = 'P' AND o.name = @SPName and o.status >=0
GO
srinivas_s at 2007-11-11 23:52:53 >
# 7 Re: Searching SQL Server 2000
Transact SQL has a system stored procedure sp_depends which could be used to find object dependencies.Transact SQL sp_depends ( http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_da-di_9qlv.asp)
aniseed at 2007-11-11 23:54:02 >
# 8 Re: Searching SQL Server 2000
Jimjcb1269 at 2007-11-11 23:55:05 >
