Dependencies,Please help

Question

Dependencies,Please help

mary_s

Newbie

Points: 3
More actions
April 1, 2008 at 1:51 pm

#154245

Hi
I am trying to move objects,Sps,views,Udfs from one database to another meaning cleaning the junk.is there any script that I could find Tables,Sps.views udf's dependencies and than recreate the necessary objects in order in another database.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

sys.sysdepends has the dependency information.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Susantha Bathige Ten Centuries Points: 1040 More actions · Answer 2

In case if your using SQL Server 2005, use sys.sql_dependencies.

Remember, there are some situations you will not find dependies recorded in the system view. Specially when you rename an object, dependancies will not update.

Susantha

nmoore SSCrazy Points: 2414 More actions · Answer 3

Ordering the objects by creation date will get you most of the way there.

Nigel Moore
======================

Tom Garth SSCertifiable Points: 6193 More actions · Answer 4

salibindla_m (4/1/2008)
Hi
I am trying to move objects,Sps,views,Udfs from one database to another meaning cleaning the junk.is there any script that I could find Tables,Sps.views udf's dependencies and than recreate the necessary objects in order in another database.

I you are scripting them all to a single batch, then you can use a BEGIN TRANSACTION at the top of the script, and a ROLLBACK TRANSACTION at the bottom of the script. Run and reorganize until there are no more missing dependencies messages. Then commit the batch.

Tom Garth
Vertical Solutions[/url]

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

gan-685682 Ten Centuries Points: 1216 More actions · Answer 5

Select name, Category =

CASE xType

WHEN 'P'THEN 'Stored procedure'

WHEN 'TR'THEN 'Trigger'

WHEN 'V'THEN 'View'

WHEN 'U'THEN 'User table'

WHEN 'TF'THEN 'Table function'

ELSE 'Not yet categorized'

END

from sysobjects

where ID in

(

Select id from sysdepends

where

depid = (Select ID from sysobjects so2 where name = @SysObject)

)

and xType in ('P', 'TR', 'V', 'U', 'TF')

UNION

Select

(Select name from sysobjects so where so.id = sf.fkeyid)

, 'FOREIGN KEY'

from dbo.sysforeignkeys sf -- use dbo instead of sys for backward compatibility

where sf.rKeyid =

(

Select ID from sysobjects where name= @SysObject

)