Finding dependent objects in different DB 
Author Message
 Finding dependent objects in different DB
Hi,

using sp_depends can help fining dependent objects in the current DB.  Can,
and if yes how, one use it to find dependent objects in DB other than the
current one?  Or is there any other SP/methods for this purpose?

Quentin



Sat, 20 Sep 2003 04:39:08 GMT
 Finding dependent objects in different DB

There's no such stored procedure. You would have to verify this manually
querying system tables or using informacion_schema views.
--
HTH
---------------------------------------------------------------------
Carlos Eduardo Rojas
MCSE+I, MCDBA, MCSS, SQL Server MVP
Pass Spanish Group


Quote:
> Hi,

> using sp_depends can help fining dependent objects in the current DB.
Can,
> and if yes how, one use it to find dependent objects in DB other than the
> current one?  Or is there any other SP/methods for this purpose?

> Quentin



Sat, 20 Sep 2003 05:12:54 GMT
 Finding dependent objects in different DB
Here you go...

-- Listing depends for an object

if object_id('usp_list_depends','p') is not null
drop proc usp_list_depends
go
create procedure usp_list_depends


as

-- Listing depends for an object
/*


Select


*/

--dynamic query


'
select distinct
 ''name'' = substring((s.name + ''.'' + o.name), 1, 40),
   type = substring(v.name, 5, 16)


where o.id = d.id
     and o.xtype = substring(v.name,1,2) and v.type = ''O9T''

     and o.uid = s.uid
'


go

exec usp_list_depends 'pubs','authors'
go


Quote:
> Hi,

> using sp_depends can help fining dependent objects in the current DB.
Can,
> and if yes how, one use it to find dependent objects in DB other than the
> current one?  Or is there any other SP/methods for this purpose?

> Quentin



Sat, 20 Sep 2003 05:18:29 GMT
 Finding dependent objects in different DB
Just a note about sysdepends that you should be aware of. Sysdepends is not
always accurate and should never be depended on, just used as a reference
only.

--
Andrew J. Kelly
Targitmail.com

Quote:

> Here you go...

> -- Listing depends for an object

> if object_id('usp_list_depends','p') is not null
> drop proc usp_list_depends
> go
> create procedure usp_list_depends


> as

> -- Listing depends for an object
> /*


> Select


> */

> --dynamic query


> '
> select distinct
>  ''name'' = substring((s.name + ''.'' + o.name), 1, 40),
>    type = substring(v.name, 5, 16)


> where o.id = d.id
>      and o.xtype = substring(v.name,1,2) and v.type = ''O9T''

>      and o.uid = s.uid
> '


> go

> exec usp_list_depends 'pubs','authors'
> go



> > Hi,

> > using sp_depends can help fining dependent objects in the current DB.
> Can,
> > and if yes how, one use it to find dependent objects in DB other than
the
> > current one?  Or is there any other SP/methods for this purpose?

> > Quentin



Sat, 20 Sep 2003 05:58:35 GMT
 Finding dependent objects in different DB
Thank you guys for the responses.

I have a further question stemming from your messages: the table sysdepends
(or practically all system tables) does not span over multiple databases,
i.e. it does not contain dependency information across databases.  Is this a
true statement?

This is still from my original question: I have object AO in database A, and
object BO in database B.  BO is dependent on AO and I want to have a method
to find out that there is such a dependency (to be used to find dependencies
I do NOT know).

OJ's procedure seems to work for dependencies BETWEEN OBJECTS IN DATABASE B,
if I want to query from database A, but not for inter-database
dependencies -- please let me know if my understanding is incorrect.  I
looked up in BOL, but there is no clear statement about cross-database
dependency.

Quentin



Quote:
> Just a note about sysdepends that you should be aware of. Sysdepends is
not
> always accurate and should never be depended on, just used as a reference
> only.

> --
> Andrew J. Kelly
> Targitmail.com


> > Here you go...

> > -- Listing depends for an object

> > if object_id('usp_list_depends','p') is not null
> > drop proc usp_list_depends
> > go
> > create procedure usp_list_depends


> > as

> > -- Listing depends for an object
> > /*


> > Select


> > */

> > --dynamic query


> > '
> > select distinct
> >  ''name'' = substring((s.name + ''.'' + o.name), 1, 40),
> >    type = substring(v.name, 5, 16)

+

> > where o.id = d.id
> >      and o.xtype = substring(v.name,1,2) and v.type = ''O9T''

> >      and o.uid = s.uid
> > '


> > go

> > exec usp_list_depends 'pubs','authors'
> > go



> > > Hi,

> > > using sp_depends can help fining dependent objects in the current DB.
> > Can,
> > > and if yes how, one use it to find dependent objects in DB other than
> the
> > > current one?  Or is there any other SP/methods for this purpose?

> > > Quentin



Sat, 20 Sep 2003 22:47:33 GMT
 Finding dependent objects in different DB
As far as  I know, there isn't a system table that contains such
dependencies across databases.

-oj


Quote:
> Thank you guys for the responses.

> I have a further question stemming from your messages: the table
sysdepends
> (or practically all system tables) does not span over multiple databases,
> i.e. it does not contain dependency information across databases.  Is this
a
> true statement?

> This is still from my original question: I have object AO in database A,
and
> object BO in database B.  BO is dependent on AO and I want to have a
method
> to find out that there is such a dependency (to be used to find
dependencies
> I do NOT know).

> OJ's procedure seems to work for dependencies BETWEEN OBJECTS IN DATABASE
B,
> if I want to query from database A, but not for inter-database
> dependencies -- please let me know if my understanding is incorrect.  I
> looked up in BOL, but there is no clear statement about cross-database
> dependency.

> Quentin



> > Just a note about sysdepends that you should be aware of. Sysdepends is
> not
> > always accurate and should never be depended on, just used as a
reference
> > only.

> > --
> > Andrew J. Kelly
> > Targitmail.com




- Show quoted text -

Quote:
> > > Here you go...

> > > -- Listing depends for an object

> > > if object_id('usp_list_depends','p') is not null
> > > drop proc usp_list_depends
> > > go
> > > create procedure usp_list_depends


> > > as

> > > -- Listing depends for an object
> > > /*


> > > Select


> > > */

> > > --dynamic query


> > > '
> > > select distinct
> > >  ''name'' = substring((s.name + ''.'' + o.name), 1, 40),
> > >    type = substring(v.name, 5, 16)

> +

> > > where o.id = d.id
> > >      and o.xtype = substring(v.name,1,2) and v.type = ''O9T''

> > >      and o.uid = s.uid
> > > '


> > > go

> > > exec usp_list_depends 'pubs','authors'
> > > go



> > > > Hi,

> > > > using sp_depends can help fining dependent objects in the current
DB.
> > > Can,
> > > > and if yes how, one use it to find dependent objects in DB other
than
> > the
> > > > current one?  Or is there any other SP/methods for this purpose?

> > > > Quentin



Sun, 21 Sep 2003 09:45:17 GMT
 Finding dependent objects in different DB
Thanks OJ.

Quote:

> As far as  I know, there isn't a system table that contains such
> dependencies across databases.

> -oj



> > Thank you guys for the responses.

> > I have a further question stemming from your messages: the table
> sysdepends
> > (or practically all system tables) does not span over multiple
databases,
> > i.e. it does not contain dependency information across databases.  Is
this
> a
> > true statement?

> > This is still from my original question: I have object AO in database A,
> and
> > object BO in database B.  BO is dependent on AO and I want to have a
> method
> > to find out that there is such a dependency (to be used to find
> dependencies
> > I do NOT know).

> > OJ's procedure seems to work for dependencies BETWEEN OBJECTS IN
DATABASE
> B,
> > if I want to query from database A, but not for inter-database
> > dependencies -- please let me know if my understanding is incorrect.  I
> > looked up in BOL, but there is no clear statement about cross-database
> > dependency.

> > Quentin



> > > Just a note about sysdepends that you should be aware of. Sysdepends
is
> > not
> > > always accurate and should never be depended on, just used as a
> reference
> > > only.

> > > --
> > > Andrew J. Kelly
> > > Targitmail.com



> > > > Here you go...

> > > > -- Listing depends for an object

> > > > if object_id('usp_list_depends','p') is not null
> > > > drop proc usp_list_depends
> > > > go
> > > > create procedure usp_list_depends


> > > > as

> > > > -- Listing depends for an object
> > > > /*


> > > > Select


> > > > */

> > > > --dynamic query


> > > > '
> > > > select distinct
> > > >  ''name'' = substring((s.name + ''.'' + o.name), 1, 40),
> > > >    type = substring(v.name, 5, 16)


> > +

> > > > where o.id = d.id
> > > >      and o.xtype = substring(v.name,1,2) and v.type = ''O9T''

> > > >      and o.uid = s.uid
> > > > '


> > > > go

> > > > exec usp_list_depends 'pubs','authors'
> > > > go



> > > > > Hi,

> > > > > using sp_depends can help fining dependent objects in the current
> DB.
> > > > Can,
> > > > > and if yes how, one use it to find dependent objects in DB other
> than
> > > the
> > > > > current one?  Or is there any other SP/methods for this purpose?

> > > > > Quentin



Tue, 23 Sep 2003 23:38:26 GMT
 
 [ 7 post ] 

 Relevant Pages 

1. Dependent objects script is empty although many dependent objects exist

2. SQL Server Copy Object Task: include dependent objects

3. How can an object found can become a not found object

4. How to disable check dependent object ?

5. SQL 7.0 Generate SQL Scripts with Dependent Objects

6. DTS Dependent Objects

7. Dependent Objects in scripts

8. How To List the dependent objects of defaults

9. Update from object types with type or table dependents

10. Update from object types with type or table dependents

11. SP_DEPENDS not showing all dependent objects

12. Access right inheritance (for dependent objects)


 
Powered by phpBB® Forum Software