SQL script views in order

Question

SQL script views in order

mossmanm

Grasshopper

Points: 19
More actions
October 6, 2009 at 11:45 pm

#138546

Hi
I need to script all my views in my dev database and put into the production database. Its a constant pain when some views depend on other views and I need them scripted in a certain order.
Anyone know a tool that does this ?

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

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

Mel Harbour SSC Eights! Points: 857 More actions · Answer 1

Note, I work for Red Gate (obviously), so this is a slight plug. We do actually use our own tools for exactly this sort of thing though, so I'm not just saying it because it's a tool we produce, but also because I think it's actually quite good!

Try SQL Compare: http://www.red-gate.com/products/SQL_Compare/index.htm

Mel

Mel HarbourProject ManagerRed Gate Software

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

A Quick & Dirty way around this problem is to include the "IF NOT EXISTS" test in the CREATE VIEWs script and then when you execute it in the target DB, just keep re-executing it until all of the errors go away. 🙂

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Lowell SSC Guru Points: 323518 More actions · Answer 3

here's a do it yourself way.

it just a cursor that goes thru the results of a temp table populated by the procedure sp_msdependencies

maybe this will help:

SET NOCOUNT ON

CREATE TABLE #MyObjectHierarchy

(

HID int identity(1,1) not null primary key,

ObjectId int,

TYPE int,OBJECTTYPE AS CASE

WHEN TYPE = 1 THEN 'FUNCTION'

WHEN TYPE = 4 THEN 'VIEW'

WHEN TYPE = 8 THEN 'TABLE'

WHEN TYPE = 16 THEN 'PROCEDURE'

WHEN TYPE =128 THEN 'RULE'

ELSE ''

END,

ONAME varchar(255),

OOWNER varchar(255),

SEQ int

)

CREATE TABLE #tmp

(

DefinitionID int identity(1,1),

Definition varchar(max) )

--our list of objects in dependancy order

INSERT #MyObjectHierarchy (TYPE,ONAME,OOWNER,SEQ)

EXEC sp_msdependencies @intrans = 1

UPDATE #MyObjectHierarchy SET ObjectId = object_id(OOWNER + '.' + ONAME)

--remove everything except views for this biz rule

DELETE FROM #MyObjectHierarchy WHERE OBJECTTYPE <> 'VIEW'

DECLARE

@objID int,

@schemaname varchar(255),

@objname varchar(255),

@objecttype varchar(20),

@FullObjectName varchar(510),

@VersionDate datetime

SET @VersionDate=getdate()

DECLARE cur1 CURSOR FOR

SELECT ObjectID,OOWNER,ONAME,OBJECTTYPE FROM #MyObjectHierarchy ORDER BY HID

OPEN cur1

FETCH NEXT FROM cur1 INTO @objID,@schemaname,@objname,@objecttype

WHILE @@fetch_status <> -1

BEGIN

INSERT INTO #tmp(Definition)

SELECT definition + 'GO' + CHAR(13) from sys.sql_modules where object_id = @objID

FETCH NEXT FROM cur1 INTO @objID,@schemaname,@objname,@objecttype

END

CLOSE cur1

DEALLOCATE cur1

SELECT * FROM #tmp ORDER BY DefinitionID

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

The problem is that the SQL Server maintained Dependency information in SQL Server 2000 and 2005 is not itself dependable.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Lowell SSC Guru Points: 323518 More actions · Answer 5

RBarryYoung (10/7/2009)
The problem is that the SQL Server maintained Dependency information in SQL Server 2000 and 2005 is not itself dependable.

i thought the dependancy issue was only (potentially) inaccurate only when it came to stored procedures?

or can it be for views also?

I know you can write a proc that references a table or view that does not exist, but

i thought you cannot create a view if the underlying tables or views are not already in place, but that procs can use late binding and are the items subject to inaccurate dependancy info?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

Lowell (10/7/2009)
RBarryYoung (10/7/2009)
The problem is that the SQL Server maintained Dependency information in SQL Server 2000 and 2005 is not itself dependable.
i thought the dependancy issue was only (potentially) inaccurate only when it came to stored procedures?
or can it be for views also?
I know you can write a proc that references a table or view that does not exist, but
i thought you cannot create a view if the underlying tables or views are not already in place, but that procs can use late binding and are the items subject to inaccurate dependancy info?

Huh. Good point, I didn't think of that. ... :blush:

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]