I am looking for code or a solution to find all
dependencies in a .db fast and simple (or code). Any
suggestions would be great!
Example:
Here is the dependencies among these 4 tables:
1) Publishers (base table): does not depend on other
tables.
2) Authors (base table): does not depend on other tables.
3) Titles: has foreign key which depend on publishers
table.
4) Titleauthor: has 2 foreign keys which depend on
authors and publishers titleswhy?
is it because you want to make sure your new code doesn't
break any existing code?
check out DB Ghost @. www.dbghost.com - it can guarentee
your code doesn't break anything and make deploying your
changes a breeze.
>--Original Message--
>I am looking for code or a solution to find all
>dependencies in a .db fast and simple (or code). Any
>suggestions would be great!
>Example:
>Here is the dependencies among these 4 tables:
>1) Publishers (base table): does not depend on other
>tables.
>2) Authors (base table): does not depend on other tables.
>3) Titles: has foreign key which depend on publishers
>table.
>4) Titleauthor: has 2 foreign keys which depend on
>authors and publishers titles
>.
>|||Have you looked into the stored procedure sp_depends?
-- Keith, SQL Server MVP
"Mike A" <mike@.countrymedic.com> wrote in message =news:000301c34a11$6be5af20$a101280a@.phx.gbl...
> I am looking for code or a solution to find all > dependencies in a .db fast and simple (or code). Any > suggestions would be great!
> > Example:
> Here is the dependencies among these 4 tables:
> 1) Publishers (base table): does not depend on other > tables.
> 2) Authors (base table): does not depend on other tables.
> 3) Titles: has foreign key which depend on publishers > table.
> 4) Titleauthor: has 2 foreign keys which depend on > authors and publishers titles
>|||Yes, sp_depends does not solve the issue. It shows
dependencies on sp.
>--Original Message--
>Have you looked into the stored procedure sp_depends?
>--
>Keith, SQL Server MVP
>"Mike A" <mike@.countrymedic.com> wrote in message
news:000301c34a11$6be5af20$a101280a@.phx.gbl...
>> I am looking for code or a solution to find all
>> dependencies in a .db fast and simple (or code). Any
>> suggestions would be great!
>> Example:
>> Here is the dependencies among these 4 tables:
>> 1) Publishers (base table): does not depend on other
>> tables.
>> 2) Authors (base table): does not depend on other
tables.
>> 3) Titles: has foreign key which depend on publishers
>> table.
>> 4) Titleauthor: has 2 foreign keys which depend on
>> authors and publishers titles
>.
>|||No trying to determine population order of the .db built
in another state.
>--Original Message--
>why?
>is it because you want to make sure your new code
doesn't
>break any existing code?
>check out DB Ghost @. www.dbghost.com - it can guarentee
>your code doesn't break anything and make deploying your
>changes a breeze.
>>--Original Message--
>>I am looking for code or a solution to find all
>>dependencies in a .db fast and simple (or code). Any
>>suggestions would be great!
>>Example:
>>Here is the dependencies among these 4 tables:
>>1) Publishers (base table): does not depend on other
>>tables.
>>2) Authors (base table): does not depend on other
tables.
>>3) Titles: has foreign key which depend on publishers
>>table.
>>4) Titleauthor: has 2 foreign keys which depend on
>>authors and publishers titles
>>.
>.
>|||hi Mike,
"Mike A" <mike@.countrymedic.com> ha scritto nel messaggio
news:000301c34a11$6be5af20$a101280a@.phx.gbl...
> I am looking for code or a solution to find all
> dependencies in a .db fast and simple (or code). Any
> suggestions would be great!
> Example:
> Here is the dependencies among these 4 tables:
> 1) Publishers (base table): does not depend on other
> tables.
> 2) Authors (base table): does not depend on other tables.
> 3) Titles: has foreign key which depend on publishers
> table.
> 4) Titleauthor: has 2 foreign keys which depend on
> authors and publishers titles
You can have a look at the UNDOCUMENTED (unsupported) stored procedure
sp_MSforeachtable..
sp_MSforeachtable can execute the command you pass as a parameter for each
table present in your database and you can try using sp_depends, that
returns all dependencies for the specified ('?') table object
EXEC sp_MSforeachtable 'SELECT ''?'' AS ObjName EXEC sp_depends ''?'''
you'll get an output similar to
ObjName
--
dbo.tableXXXXX
no object refers to this object....
or
ObjName
--
dbo.tableXXXXX
In the current database, the specified object is referenced by the
following:
name type
xxxxx xxxxx
to list FK constraint you can use a select which actually includes some
joins....
mixing the 2 command you can give this a try...
----
EXEC sp_MSforeachtable 'SELECT ''?'' AS ObjName EXEC sp_depends ''?'''
DECLARE @.MasterCMD VARCHAR (1000)
SET @.MasterCMD ='SELECT ''?'' AS ObjName
PRINT ''sp_depends''
PRINT ''--''
PRINT ''fk_scan''
PRINT ''--''
SELECT SO3.NAME FK_NAME, ''['' + SU.NAME + ''].['' + SO.NAME + '']''
FROM SYSFOREIGNKEYS SYSFK
--Foreign Key Constraint - Table info
INNER JOIN (SELECT UID, ID, NAME FROM SYSOBJECTS WHERE XTYPE = ''U'') SO
ON SYSFK.FKEYID = SO.ID
INNER JOIN (SELECT UID, ID, NAME FROM SYSOBJECTS WHERE XTYPE = ''U'') SO2
ON SYSFK.RKEYID = SO2.ID
--Name and ID of the FOREIGN KEY constraint
INNER JOIN (SELECT ID, NAME FROM SYSOBJECTS) SO3 ON SYSFK.CONSTID = SO3.ID
--Foreign Key Constraint - Owner info
INNER JOIN SYSUSERS SU ON SO.UID = SU.UID
--Referenced in the FOREIGN KEY constraint - Owner info
INNER JOIN SYSUSERS SU2 ON SO2.UID = SU2.UID
WHERE SU.NAME + ''.'' + SO.NAME = ''?'''
EXEC sp_MSforeachtable @.MasterCMD
----
you must be warned that Microsot does not recommend the use of UNDOCUMENTED
stored procedures that are unsupported and system objects direct
manipulation
hth
Andrea Montanari
montanari_andrea@.virgilio.it
http://www.asql.biz/DbaMgr.shtm
DbaMgr2k ver 0.4.0 - DbaMgr ver 0.50.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply|||It also shows dependencies on a table...
USE pubs
GO
exec sp_depends 'authors'
-- Keith, SQL Server MVP
"Mike A" <mike@.countrymedic.com> wrote in message =news:0a3001c34a22$5c817f80$a501280a@.phx.gbl...
> Yes, sp_depends does not solve the issue. It shows > dependencies on sp.
> > > >--Original Message--
> >Have you looked into the stored procedure sp_depends?
> >
> >-- > >Keith, SQL Server MVP
> > > >"Mike A" <mike@.countrymedic.com> wrote in message > news:000301c34a11$6be5af20$a101280a@.phx.gbl...
> >> I am looking for code or a solution to find all > >> dependencies in a .db fast and simple (or code). Any > >> suggestions would be great!
> >> > >> Example:
> >> Here is the dependencies among these 4 tables:
> >> 1) Publishers (base table): does not depend on other > >> tables.
> >> 2) Authors (base table): does not depend on other > tables.
> >> 3) Titles: has foreign key which depend on publishers > >> table.
> >> 4) Titleauthor: has 2 foreign keys which depend on > >> authors and publishers titles
> >> > >.
> >
