[dba-SQLServer] Automating view creation

If you are running sqlserver 2005, the following will get you going:

select o.name
,o.object_id
into #views
from sys.objects o
join sys.schemas s
on s.schema_id = o.schema_id
where o.type in  ('V') --  and s.name = @schema


select definition + char(10) + 'go' + char(10)
from sys.sql_modules c  join sys.objects o
on c.object_id = o.object_id  join #views o2
on o.object_id = o2.OBJECT_ID

DROP #Views

That will give you a result set with the text for the view definition.
If you examine it and are happy with it (I don't make any guarantees without
knowing more about your db), you have a couple of options:  1) you can place the select statement within a cursor and scroll through #views and execute the statement, which will automatically, so to speak, create the views.
A safer way to go is to copy and paste the output of the above into window in ssms, verify it is what you need,  and execute the statements one by one.

A cursor example might look something like the following (aircode.....)
-------------------------------------

select o.name
,o.object_id
into #views
from sys.objects o
join sys.schemas s
on s.schema_id = o.schema_id
where o.type in  ('V') --  and s.name = @schema


DECLARE @strSQL VARCHAR(MAX)
DECLARE CurB CURSOR FOR
select definition + char(10) + 'go' + char(10)
from sys.sql_modules c  join sys.objects o
on c.object_id = o.object_id  join #views o2
on o.object_id = o2.OBJECT_ID

OPEN CurB

FETCH NEXT FROM CurB INTO @strSQL
WHILE @@FETCH_STATUS=0
BEGIN

        PRINT @strSQL --print it to the results window
        EXEC (@strSQL) -execute it, in this case create the view

        FETCH NEXT FROM CurB INTO @strSQL
END

CLOSE CurB
DEALLOCATE CurB


DROP #Views


This will create views, but remember the permissions part of it...


-----Original Message-----


Message: 1
Date: Fri, 24 Apr 2009 14:00:25 -0500
From: "Robert L. Stewart" <robert at webedb.com>
Subject: Re: [dba-SQLServer] Automating the creation of views
To: dba-sqlserver at databaseadvisors.com
Message-ID: <200904241901.n3OJ10eK012020 at databaseadvisors.com>
Content-Type: text/plain; charset="us-ascii"; format=flowed

Stu,

When  you right click on a view, you can edit it.
When you edit it, you get the SQL script.
Change the alter to create.
Add the sourcedatabase. where you need to.
Right click on the view tab.
change the connection to the database you want to save it in.
Execute the SQL.

There is no way of automating it beyond that.


At 12:00 PM 4/24/2009, you wrote:
>Date: Fri, 24 Apr 2009 08:14:53 -0500
>From: sdunlap at brownshoe.com
>Subject: Re: [dba-SQLServer] Automating the creation of views
>To: dba-sqlserver at databaseadvisors.com
>Message-ID:
>
><OFD1168E8D.6B19CFE9-ON862575A2.004635B7-862575A2.0048C780 at brownshoe.com>
>
>Content-Type: text/plain; charset="US-ASCII"
>
>Francisco / Betsy,
>
>Thank you both for your replies.  Francisco, I read the article you
>provided, but I'm still stuck.  I'm able to successfully transport the
>results of the View from the source database to a table in the target
>database using the SSIS wizard.
>However, what I want to do is take a view from the source database, such
>as:  "SELECT * FROM dbo.MySourceTable WHERE MyField = 'xxx'"
>
>and create a new view in the target database like this:   "SELECT * FROM
>SourceDatabase.dbo.MySourceTable WHERE MyField = 'xxx'"
>
>... or alternatively, I suppose it could be:  "SELECT * FROM
>SourceDatabase.dbo.MyView"
>
>Does anyone know of a way to have the view copied into the target database
>as a view?
>
>Thanks,
>Stu

********************************************

The contents of this e-mail message and its attachments are covered by the Electronic Communications Privacy Act (18 U.S.C. 2510-2521) and are intended solely for the addressee(s) hereof. If you are not the named recipient, or the employee or agent responsible for delivering the message to the intended recipient, or if this message has been addressed to you in error, you are directed not to read, disclose, reproduce, distribute, disseminate or otherwise use this transmission.  If you have received this communication in error, please notify us immediately by return e-mail or by telephone, 530-893-3520, and delete and/or destroy all copies of the message immediately.