Tables referenced by a stored proc (2000)

Question

Tables referenced by a stored proc (2000)

David McKinney

SSChampion

Points: 10472
More actions
December 6, 2008 at 4:41 am

#203793

Comments posted to this topic are about the item Tables referenced by a stored proc (2000)

Viewing 15 posts - 1 through 15 (of 19 total)

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

David McKinney SSChampion Points: 10472 More actions · Answer 1

I've since found another couple of issues. One is that it in the same way as I need and #endchars table to stop table1 matching on table12, I would also need a #startchars table to stop table1 matching on constable1. However if I include a startchars as another cross join like I do endchars, performance goes down the drain.

For this reason, I worked on a modified version using patindex and a blacklist of endchars (rather than the whitelist above).

Note that tables from other databases won't be considered (as we're looking at sysobjects from the current db.) Also any tables which are commented out will nonetheless be included.

declare @sproc varchar(256)

set @sproc = 'spToBeSearched'

select so.name

from sysobjects so,

syscomments sc

where so.type = 'U'

and object_name(sc.id) = @sproc

group by so.name

having sum(patindex('%[^a-zA-Z0-9~]' + replace(so.name,'_','~') + '[^a-zA-Z0-9~]%', replace(sc.text,'_','~') + ' ')) <> 0

order by name

Carlo Romagnano SSC-Insane Points: 22668 More actions · Answer 2

If you do not use dynamic sql in your sp, you have the sysdepends table with all dependencies between objects.

Here you are:

SELECT object_name(id) as sp,object_name(depid) as dep_obj FROM sysdepends

WHERE object_name(id) = 'mysp_elab'

David McKinney SSChampion Points: 10472 More actions · Answer 3

Hi Carlo,

sysdepends is notoriously unreliable due to deferred name resolution - which means that I can create a stored procedure before the objects it references exist.

In the stored procedures I tested with, sysdepends only returned about half of the referenced tables (20 instead of 40).

Regards,

David.

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

David,

Weird stuff with your code - it prints out a table NOT in the SP.

Also, it DOES print tables from another DB - could this be because they are in the SP as DB2..DBTable1 ?

Wondering,

Doug

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

Carlo,

Your code did not list ANY tables in the SP, but it did list another SP called from within it?

Are either yours or David's routines dependent upon ownership in the code eg spname vs tablename vs dbo.spname vs dbo.tablename or database.dbo.tablename?

Best,

Doug

David McKinney SSChampion Points: 10472 More actions · Answer 6

Hi Doug,

Thanks for your feedback! The table names it displays come from sysobjects which is dependent on the database you're running under i.e. the USE statement. I can't imagine that sysobjects in one database could contain tables found in another db. Is it possible you're running the database against a different database? (master perhaps?).

By the way, which script are you referring to - I'd recommend the script in the forum, rather than in the article. Do you get the same results (or similar anomolies) with both?

Again thanks for taking the time to share your findings.

Happy new year,

David.

Carlo Romagnano SSC-Insane Points: 22668 More actions · Answer 7

Please, try this one:

exec sp_MSdependencies N'[dbo].[mysp_elab]', null, 1053183

Third parameter is 1053183, that means only table named in the sp.

Without parms, it returns all dipendencies: table used in the sp and their FK.

David McKinney SSChampion Points: 10472 More actions · Answer 8

Hi Carlo,

I've just tried out your latest post, compared against my script.

Against a (random) sample stored procedure, yours retrieves 9 tables (listed below)

ATTR_LN_PTF_BENCH

BENCHMARK

D_TIME

PORTF_SHARE

PORTFOLIO

PORTFOLIO_PERFORMANCES

S_PORTF_SHARE_PERF

SHARE_PERF_SETTING

T_SHARE_CLASS

whereas mine retrieves 13 tables (also listed)

ATTR_LN_PTF_BENCH

BENCHMARK

D_TIME

PERF_CALENDAR

PORTF_SHARE

PORTFOLIO

PORTFOLIO_PERFORMANCES

S_PORTF_SHARE_CARAC

S_PORTF_SHARE_PERF

S_PORTFOLIO_CARAC

SHARE_NET_VALUE

SHARE_PERF_SETTING

T_SHARE_CLASS

I've checked the tables which aren't in your results and they are 1) not commented 2) not dynamic SQL.

I just want to reiterate that sysdepends is totally unreliable, and sp_MSdependencies is built on sysdepends. (Do an sp_helptext on it and search for sysdepends.)

ANY SOLUTION based on sysdepends isn't going to work.

Regards,

David.

Carlo Romagnano SSC-Insane Points: 22668 More actions · Answer 9

sysdepends lists tables only if they are in the same database and the sp is created after the table. To update syspedends please ALTER PROCEDURE and then run exec sp_MSdependencies N'[dbo].[mysp_elab]', null, 1053183

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

David,

Perhaps I need to clarify - it listed a table the SP does not use at all.

Best,

Doug

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

David,

What I think happens it that you code finds a word in the comments that is a valid table in the database eg

-- Patients is linked to the Accounts table

Where Accounts is a table, but it is not referenced in SQL code.

Doug

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

Carlo,

No go with that call - it still only show an SP that my Stored Procedure calls, not any tables.

It says the oType is 16, if that is any help.

Doug

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

Carlo,

I altered th SP - your routine still only lists a dependent SP, it lists not tables. SQL 2005.

Doug

David McKinney SSChampion Points: 10472 More actions · Answer 14

Hi Doug,

I confirm (as I mentioned in the article) that table names which are commented will be picked up.

The script does a purely textual search on the stored procedure (the text being stored in syscomments.) It doesn't at all consider the context of the matches (whether it is in comments etc.)

Regards,

David.