Search through Stored Procedure Code? 3

[VBRookie]

Hi ... I need to make a schema change in our database and I need to make sure that all stored procedures that reference that table are updated to reflect the changes.

Is there anyway that I can search through the actual code of each stored procedure for the table name?

Many Thanks,
- VB Rookie

 

[TysonLPrice]

I don't know if Enterprise Manager has a similar tool but if you go to

http://www.toadsoft.com

and download the free SQL 2000 tools you can get a list of all the stored procedures that a table is used in by looking at the table's dependencies. It also has a number of handy tools with it.

Someone else may know how but I believe you can copy all the stored procedures into a text file and search that way. If you don't have the Toad.exe I think it's worth looking at.

 

[nikhilparchure]

Hi ,

In Query Analyser --> F8 (Object Browser) --> Goto your Working Database ---> User Tables ---> Table__Name --> Dependencies
(Here you will find all the dependent objects on the Table)
There is a system stored procedure sp_depends
sp_depends Table__Name which also gives you the list of dependent objects.

Might be of your help

Regards
Nikhil

 

[hmckillop]

We use a stored proc to search across the database objects for specific words: code is below:

CREATE PROC sp_search_code
(
@SearchStr varchar(100),
@RowsReturned int = NULL OUT
)
AS

/*
To search your database code for the keyword 'unauthorized':
EXEC sp_search_code 'unauthorized'

To search your database code for the keyword 'FlowerOrders' and also find out the number of hits:
DECLARE @Hits int
EXEC sp_search_code 'FlowerOrders', @Hits OUT
SELECT 'Found ' + LTRIM(STR(@Hits)) + ' object(s) containing this keyword' AS Result
*************************************************************************************************/
BEGIN
SET NOCOUNT ON

SELECT DISTINCT USER_NAME(o.uid) + '.' + OBJECT_NAME(c.id) AS 'Object name',
CASE
WHEN OBJECTPROPERTY(c.id, 'IsReplProc') = 1
THEN 'Replication stored procedure'
WHEN OBJECTPROPERTY(c.id, 'IsExtendedProc') = 1
THEN 'Extended stored procedure'
WHEN OBJECTPROPERTY(c.id, 'IsProcedure') = 1
THEN 'Stored Procedure'
WHEN OBJECTPROPERTY(c.id, 'IsTrigger') = 1
THEN 'Trigger'
WHEN OBJECTPROPERTY(c.id, 'IsTableFunction') = 1
THEN 'Table-valued function'
WHEN OBJECTPROPERTY(c.id, 'IsScalarFunction') = 1
THEN 'Scalar-valued function'
WHEN OBJECTPROPERTY(c.id, 'IsInlineFunction') = 1
THEN 'Inline function'
END AS 'Object type',
'EXEC sp_helptext ''' + USER_NAME(o.uid) + '.' + OBJECT_NAME(c.id) + '''' AS 'Run this command to see the object text'
FROM syscomments c
INNER JOIN
sysobjects o
ON c.id = o.id
WHERE c.text LIKE '%' + @SearchStr + '%' AND
encrypted = 0 AND
(
OBJECTPROPERTY(c.id, 'IsReplProc') = 1 OR
OBJECTPROPERTY(c.id, 'IsExtendedProc') = 1 OR
OBJECTPROPERTY(c.id, 'IsProcedure') = 1 OR
OBJECTPROPERTY(c.id, 'IsTrigger') = 1 OR
OBJECTPROPERTY(c.id, 'IsTableFunction') = 1 OR
OBJECTPROPERTY(c.id, 'IsScalarFunction') = 1 OR
OBJECTPROPERTY(c.id, 'IsInlineFunction') = 1
)

ORDER BY 'Object type', 'Object name'

SET @RowsReturned = @@ROWCOUNT
END



"Own only what you can carry with you; know language, know countries, know people. Let your memory be your travel bag.

 

[VBRookie]

Thanks so much!

hmckillop's stored procedure appears to be what I was looking for, but thanks to you all for your help!

- VB Rookie