Listing tables referenced by a stored procedure

Question

Listing tables referenced by a stored procedure

Lashams

Ten Centuries

Points: 1026
More actions
July 17, 2006 at 5:56 pm

#91232

I am trying to discover a way of listing all tables used by a stored procedure, or all stored procedures where a table is used. The type of functionality I require is similar to the system stored procedure sp_depends, however this only lists the dependencies when both stored procedure and tables are within the same database.
My company has set their database up however so that stored procedures reside in one database and tables to be queried within another database.
i.e.
DB_StoredProcs
- sp_List_customer_invoices
DB_Tables
- dbo.customers
- dbo.invoices
SQL for sp_List_customer_invoices
Use DB_StoredProcs
GO
Select * from DB_tables.dbo.customers as A
inner join DB_tables.dbo.invoices as B
on A.custid = B.custid
So what I require is a way of listing all the tables in DB_Tables that are referenced by the stored procedure in DB_StoredProcs.
Any assistance is greatly appreciated.
Thanks
Stephen

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 1

That's a nice way to do things!! I use this proc to search for a table in procs etc. I'm sure you can grasp the principle - it's not particularly rocket science < grin >

Use Master

go

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_FindText]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[sp_FindText]

GO

Create procedure sp_FindText

@wot varchar(250)=' ',

@wot2 varchar(250)=' '

-- ============================================================================

-- Stored Procedure: sp_FindText

--

-- Written by: Colin Leversuch-Roberts

-- kelem consulting limited

-- http://www.kelemconsulting.co.uk

--

-- Purpose: Search for system objects containing the passed string(s)

-- These are wild card searches

-- Returns the object code and type of object

--

-- System: master database

-- does not need to be marked as a system object

--

-- Input Paramters: @wot varchar Search string

-- @wot2 varchar Search string

--

-- Output Parameters: None

--

-- Usage: Call from user database to be searched

-- EXEC dbo.sp_findtext 'tbl_sales'

-- EXEC dbo.sp_findtext 'aug','uat'

--

-- Calls: nothing

-- Uses: syscomments, sysobjects

--

-- Data Modifications: None

--

-- VERSION HISTORY

-- Version No Date Description

-- 1 22-August-2004 Initial Release

-- ============================================================================

as

set nocount on

--

select obj.name,obj.xtype,

case obj.xtype

when 'TR' then 'Trigger'

when 'P' then 'Procedure'

when 'V' then 'View'

when 'TF' then 'Function'

when 'IF' then 'Function'

when 'FN' then 'Function'

else 'Unknown'

end

,c.text

from dbo.syscomments c join dbo.sysobjects obj

on obj.id=c.id

where

patindex(<A href="mailto:'%'+@wot+'%',text)0">'%'+@wot+'%',text)<>0 and patindex(<A href="mailto:'%'+@wot2+'%',text)0">'%'+@wot2+'%',text)<>0

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

noeld SSC Guru Points: 96590 More actions · Answer 2

If you are looking for a 'reliable' way to do it

use information_schema.routines and check the routine_definiton column.

NOTE: this works only for SP and Functions

* Noel

Lashams Ten Centuries Points: 1026 More actions · Answer 3

Many thanks for both these responses. I have been able to see how both work, and made use of them successfully.

The problem with these as a method, as compared to the sp_depends results, is that sp_depends looks at actual dependencies, while these methods look for a text string, but if the text string occurs within comment it still identifies it. One of our standards is to comment out lines of code if they are being removed, rather than remove them, so the above methods still identify table names in stored procedures even when they are commented out.

Luckily the example I was working with was only referenced in eight stored procedures, so was not to involved to read through them and find the valid ones.

It would be nice if sp_depends could look at dependencies across databases, but I guess not in SQL/2000. Does anyone know if it does in SQL/2005?

Thanks for the help above, as both methods were very good and I will use them again in future.

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 4

I seem to remember sp_depends wasn't too good. Nothing beats documentation at the design and implementation stage < grin >

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

john-132685 SSCertifiable Points: 6134 More actions · Answer 5

Yeah, there's this little message that occurs when compiling out of (dependent) sequence procs in SQL2k. It says right there that sysdepends ain't gonna be updated because one of the objects referenced in your code ain't there.

Also, there's that possibility of using dynamic SQL in procs (which I often use in administration routines).

Bottom line is: write a nearly complete syntax parser for syscomments or, listen to Colin's advice.