Tables referenced by a stored proc (2000)

Question

Post reply

Tables referenced by a stored proc (2000)

Viewing 5 posts - 16 through 19 (of 19 total)

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

Douglas Osborne-456728 SSCommitted Points: 1569 More actions · Answer 1

David,

Missed that in your text. My bad.:D

Couldn't you double parse by writing all the text you encounter prior to -- to a temp table and then process that?

Ah - but then you'd also have to handle /* and */ - oh well - perhaps a second article.;)

Doug

TomThomson SSC Guru Points: 104773 More actions · Answer 2

David McKinney (1/2/2009)
ANY SOLUTION based on sysdepends isn't going to work.

The obvious solution based on sysdepends - running alterproc for the SP with no changes before using sysdepends - works better than your proposed solution: it avoids listing tables that are mentioned only in comments.

Your solution suffers from a rather obvious defect: it doesn't handle views; that can be fixed rather trivially; avoiding comments is rather harder; handling dependencies arising from access to other databases or by the many indirect access mechanisms is quite hard too

If one really wants to understand dependencies between SPs and tables, one has to consider (i) tables in the current database accessed by the SP; (ii) tables in other databases accessed by the SP; (iii) tables (in this or other databases) involved in views accessed by this SP; (iv) tables (in this or other databases) used by SPs, or UDFs called by the SP; and (v) tables accessed (including indirect access through views or further triggers of SP or Function calls) by triggers fired by actions of this SP. Your current script is useful in a single database world with no triggers, views or functions and where no SP calls any other SPs, but not much use elsewhere.

It's quite (but not very) hard to write something that does the job properly in SQLS 2000; it's a bit easier in SQLS 2005 (recursive queries help).

Tom

Douglas Osborne-456728 SSCommitted Points: 1569 More actions · Answer 3

Tom,

Saying everyone is wrong really doesn't help us solve this. 🙂

Give us an example of your solution please.

Doug

Carlo Romagnano SSC-Insane Points: 22603 More actions · Answer 4

Last idea: follow the rule of KISS (Keep It Simple & Stupid) write store procedure formatting it with the following rule: every command that reference a table should be on one line and should be the only one, e.g.

UPDATE table1

..

SELECT a,b,c

FROM dbo.table2

CROSS JOIN server1.mydb.dbo.TABLE3

FULL JOIN zzzzz

DELETE FROM #kkk

INSERT INTO somedb.dbo.tableX

(

A,b,c

) select x,y,z

FROM ANOTHERTABLE

So, your scanning routine should discard comments and searching only for DML. At last, replace commands with '', trim it and you’ll get the tablename + eventually alias for the table.

Here you are a sample:

create table #a (t varchar(8000))

delete #a

insert #a exec sp_helptext ‘mysp_elab’

update #a set

t = rtrim(ltrim(replace(t,char(9),' ')))

-- write here code to cut out comments

-- ...

-- end

delete from #a

WHERE t not like 'delete %'

and t not like 'update %'

and t not like 'insert %'

and t not like 'from %'

and t not like 'JOIN %'

SELECT

ltrim

(

replace(

replace(t,'delete ','')

,'update ','')

,'insert ','')

,'from ','')

,'JOIN ','')

,'INNER ','')

,'LEFT ','')

,'RIGHT ','')

,'FULL ','')

)

FROM #a

With this method you can find also table in quoted dynamic sql.

David McKinney SSChampion Points: 10472 More actions · Answer 5

Your solution suffers from a rather obvious defect: it doesn't handle views

Look at the title...Tables referenced by a stored proc!

It does what it says on the tin....and there's a warning on the tin regarding commented tables.

You've said it's not very hard to do better...I look forward to seeing it.

Best,

David.