order in which to run scripts - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

order in which to run scripts

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2014-10-06 : 11:39:31

So we have a database on one server running sql 2008R2 that needed to be moved to another server running 2008 (not R2). Since backup/restore was not an option, the database, all components and content were scripted out from within SSMS, with one script per object. The db is large, and now i have about 50 or so scripts to generate the db, views, sprocs, data, users and logons. In what order should these scripts be run? Some of the scripts running successfully will depend on other data already being there. Any guidance is appreciated.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-10-06 : 14:03:03

It all depends on the dependencies. In gross terms, there is a hierarchy: Types, Tables, Views, Functions and Stored Procedures. This generally is the trend but it can get convoluted. The tables can also have dependencies based on foreign keys. As an alternative, you could create the tables but omit the foreign keys under after the data was migrated. There is a system table named "sys.sql_expression_dependencies" that shows which objects rely on others. There is another system table that shows foreign key dependencies. Here are a couple of scripts that show these. They are designed to be used for a single entity but you can probably modify them to be more general. Foreign Keys:

-----------------------------------------------------------------------------
--- cteFKDepends
---
--- Description: Given an object name, find all tables that depend
---		on it through a Foreign Key, either directly or indirectly.
-----------------------------------------------------------------------------

declare
	@object	sysname = N'MyTable'			-- <<< SET THIS VALUE

-----------------------------------------------------------------------------

;with cteDepends
as	(
select
	Name Root,
	Name TableName,
	object_id,
	cast(Name as varchar(max)) Lineage,
	0 Depth
from sys.tables
where Name = @Object

union all

select
	d.Root,
	OBJECT_NAME(fk.parent_object_id) TableName,
	fk.parent_object_id,
	cast(d.Lineage + '<-' + OBJECT_NAME(fk.parent_object_id) as varchar(max)),
	Depth + 1
from
	cteDepends d
inner join
	sys.foreign_keys fk
		on	fk.referenced_object_id = d.object_id
)
select *
from cteDepends
where
	Depth > 0
--and root like 'usv_%'
order by
	Root, Depth DESC, Lineage	--, TableName

And Objects:

-----------------------------------------------------------------------------
--- cteDepends
---
--- Description: Given an object name, find all schema objects that depend
---		on it, either directly or indirectly.
-----------------------------------------------------------------------------

declare
	@object	sysname = N'MyObject'			-- <<< SET THIS VALUE

-----------------------------------------------------------------------------

;with cteDepends
as	(
select 
	Name Root, 
	Type,
	Name,
	cast(Name as varchar(max)) Lineage,
	0 Depth
from sys.objects
where Name = @Object

union all

select
	d.Root,
	o.Type,
	OBJECT_NAME(sed.referencing_id) Name,
	--cast(OBJECT_NAME(sed.referencing_id) + '->' + d.Lineage as varchar(max)),
	cast(d.Lineage + '<-' + OBJECT_NAME(sed.referencing_id) as varchar(max)),
	Depth + 1
from
	cteDepends d
inner join
	sys.sql_expression_dependencies sed
		on	sed.referenced_entity_name = d.Name
		and	sed.referencing_class <> 7	-- 7 = Index : 1 = Object/Column (vs Trigger or Index)
inner join
	sys.objects o
		on	o.object_id = sed.referencing_id
)
select *
from cteDepends
--where root like 'usv_%'
order by
	Root, Type, Lineage, Name, Depth

Good luck!

No amount of belief makes something a fact. -James Randi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources