Comments posted to this topic are about the item Tables referenced by a stored proc (2000)
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'
from sysobjects so,
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
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'
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).
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 ?
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?
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,
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.
I've just tried out your latest post, compared against my script.
Against a (random) sample stored procedure, yours retrieves 9 tables (listed below)
whereas mine retrieves 13 tables (also listed)
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.
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
Perhaps I need to clarify - it listed a table the SP does not use at all.
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.
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.
I altered th SP - your routine still only lists a dependent SP, it lists not tables. SQL 2005.
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.)
Viewing 15 posts - 1 through 15 (of 19 total)