Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2004
    Posts
    69

    Unanswered: Identifying dependencies

    How can get an object's dependencies in SQL Server. For example it I have written a procedure which accesses some tables inside it then the procedure is said to be dependent on that table. Or one procedure might call another procedure and hence dependent.

    Can I know an object's dependent objects from any system table. I think Oracle has a table USER_DEPENDENCIES which provides this info (I may be wrong :-().

    Can anyone help ?

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Use
    sp_depends @objectname
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Apr 2004
    Posts
    69
    Thanks. That worked !

  4. #4
    Join Date
    Apr 2004
    Posts
    69
    I had another doubt. When I execute the proc, how can access the Result Set. Or in other words what will the Result Set be like.

  5. #5
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    What exactly do you want to do with the result set ??
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  6. #6
    Join Date
    Apr 2004
    Posts
    69
    well I actually want to find/get out a list of dependent objects for a particular object. I think I should do it with a select query combining sysobjects and sysdepends tables to do that right ?

  7. #7
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Just take a look at sp_depends
    Code:
    use master
    sp_helptext sp_depends
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  8. #8
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Sorry to ressurect this thread...and perhaps unnecessarily, but I have found that the sysdepends route is entirely (OK, only 98.9974%) useless and cannot really be relied upon...especially if you are moving procs around from one DB to another using scripting or importing. Seems that the order of definition of the procs, etc. (or course) determines whether or not an entry is made in sysdepends for a db object or not.

    Also, I find that if someone forgets to put [dbo] on [dbo].[procname] the proc is not identified in dependencies (though, to be truthful, that could have been a result of sequencing the addition/creation of the proc, I wasn't "sequencing issues aware" back when I noticed this happening...

    Does anyone know of a way other than
    Code:
    select distinct so.name from syscomments sc 
    inner join sysobjects so on sc.id = so.id 
    where xtype = 'P' AND charindex('T_TableName', text) > 0
    (which only seems to report procs dependent on a particular table) to get around this limitation: Is there a stored proc out there that someone else has sweated out to update the Sysdepends table in one fell swoop? (heck, I'd even take a fell swoop and a half!)
    Last edited by TallCowboy0614; 10-12-04 at 15:00.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •