Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Tables referenced by a stored proc (2000)


Tables referenced by a stored proc (2000)

Author
Message
Douglas Osborne-456728
Douglas Osborne-456728
SSC-Enthusiastic
SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)

Group: General Forum Members
Points: 156 Visits: 327
David,

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

Best,
Doug
Douglas Osborne-456728
Douglas Osborne-456728
SSC-Enthusiastic
SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)

Group: General Forum Members
Points: 156 Visits: 327
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
Douglas Osborne-456728
SSC-Enthusiastic
SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)

Group: General Forum Members
Points: 156 Visits: 327
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
Douglas Osborne-456728
SSC-Enthusiastic
SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)

Group: General Forum Members
Points: 156 Visits: 327
Carlo,

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

Doug
David McKinney
David McKinney
Right there with Babe
Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)

Group: General Forum Members
Points: 769 Visits: 2090
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.
Douglas Osborne-456728
Douglas Osborne-456728
SSC-Enthusiastic
SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)

Group: General Forum Members
Points: 156 Visits: 327
David,

Missed that in your text. My bad.BigGrin

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.Wink

Doug
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10704 Visits: 12001
[b]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
Douglas Osborne-456728
SSC-Enthusiastic
SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)

Group: General Forum Members
Points: 156 Visits: 327
Tom,

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

Give us an example of your solution please.

Doug
Carlo Romagnano
Carlo Romagnano
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3602 Visits: 3235
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(
replace(
replace(
replace(
replace(
replace(
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
David McKinney
Right there with Babe
Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)

Group: General Forum Members
Points: 769 Visits: 2090

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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search