Copying Views

Is there an easy way to copy all views from one database to another? Using
the import/export wizard tries to copy the data in a View to a Table in the
destination table. I don't want to create new tables just copy the view
object over (the databases are equivalent, one production, one for testing).
I use Views for pre-defined queries often used in the production
applications to give the end-user views of the data that makes more sense.
Am I using Views wrong?
By the way I have found a work around by right clicking on the view and go
to Script As -> CREATE TO -> New Query. Then change the first line from:
USE [SourceDatabaseName]
to
USE [DestinationDatabaseName]
This process is unyieldy though because I have to repeat the multi-step
process for every view I want to copy.
Thanks,
RyanRyan
You can use SQLDMO object library to script out all views and then run it on
destination server
Sub ScriptDB(strLogin As String, strPwd As String, _
strDataBase As String, StrFilePath As String)
Dim sql As Object
Dim db As Object
Dim objTrigger As Object
Dim intOptions As Long
Dim genObj
Set sql = CreateObject("SQLDMO.SQLServer")
Set db = CreateObject("SQLDMO.Database")
Set objTrigger = CreateObject("SQLDMO.Trigger")
Const sDrops As Integer = 1
Const sIncludeHeaders As Long = 131072
Const sDefault As Integer = 4
Const sAppendToFile As Integer = 256
Const sBindings As Integer = 128
Const SQLDMOScript2_NoCollation As Long = 8388608
' Set scripting options. Because you need to specify multiple behaviors
' for the ScriptType argument, you use "Or" to combine these.
intOptions = sDrops Or sIncludeHeaders Or _
sDefault Or sAppendToFile Or sBindings Or SQLDMOScript2_NoCollation
' Connect to local server
sql.Connect "(local)", strLogin, strPwd
Set db = sql.Databases(strDataBase, "dbo")
' Script Views, ignoring system views and informational schemas
For Each genObj In db.Views
If genObj.SystemObject = False Then
genObj.Script intOptions, StrFilePath
End If
Next
MsgBox "Finished generating SQL scripts."
End Sub
--usage
Call ScriptDB("UserName","Password","DatabaseName","C:\MyResults.SQL")
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message
news:uulTfxXOHHA.4100@.TK2MSFTNGP04.phx.gbl...
> Is there an easy way to copy all views from one database to another?
> Using the import/export wizard tries to copy the data in a View to a Table
> in the destination table. I don't want to create new tables just copy the
> view object over (the databases are equivalent, one production, one for
> testing). I use Views for pre-defined queries often used in the production
> applications to give the end-user views of the data that makes more sense.
> Am I using Views wrong?
> By the way I have found a work around by right clicking on the view and go
> to Script As -> CREATE TO -> New Query. Then change the first line from:
> USE [SourceDatabaseName]
> to
> USE [DestinationDatabaseName]
> This process is unyieldy though because I have to repeat the multi-step
> process for every view I want to copy.
> Thanks,
> Ryan
>|||Ryan wrote:
> Is there an easy way to copy all views from one database to another? Usin
g
> the import/export wizard tries to copy the data in a View to a Table in th
e
> destination table. I don't want to create new tables just copy the view
> object over (the databases are equivalent, one production, one for testing
).
> I use Views for pre-defined queries often used in the production
> applications to give the end-user views of the data that makes more sense.
> Am I using Views wrong?
> By the way I have found a work around by right clicking on the view and go
> to Script As -> CREATE TO -> New Query. Then change the first line from:
> USE [SourceDatabaseName]
> to
> USE [DestinationDatabaseName]
> This process is unyieldy though because I have to repeat the multi-step
> process for every view I want to copy.
> Thanks,
> Ryan
>
I wouldn't say you're "using" them wrong, I'd say you're "creating" them
wrong. The scripting method that you discovered is not a workaround,
it's the accepted method of working with database objects. You should
be creating your views with a script, and archiving that script in a
version control system, like Visual SourceSafe or Perforce. This allows
you to track the revision history of your objects, gives you a
"rollback" method for undo-ing changes, AND it makes your objects more
portable. To "copy" and object to a new location, you simply run the
CREATE script again, in the new location.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23zxv31XOHHA.2468@.TK2MSFTNGP06.phx.gbl...
> Ryan
> You can use SQLDMO object library to script out all views and then run it
> on destination server
>
> Sub ScriptDB(strLogin As String, strPwd As String, _
> strDataBase As String, StrFilePath As String)
> Dim sql As Object
> Dim db As Object
> Dim objTrigger As Object
> Dim intOptions As Long
> Dim genObj
> Set sql = CreateObject("SQLDMO.SQLServer")
> Set db = CreateObject("SQLDMO.Database")
> Set objTrigger = CreateObject("SQLDMO.Trigger")
> Const sDrops As Integer = 1
> Const sIncludeHeaders As Long = 131072
> Const sDefault As Integer = 4
> Const sAppendToFile As Integer = 256
> Const sBindings As Integer = 128
> Const SQLDMOScript2_NoCollation As Long = 8388608
> ' Set scripting options. Because you need to specify multiple behaviors
> ' for the ScriptType argument, you use "Or" to combine these.
> intOptions = sDrops Or sIncludeHeaders Or _
> sDefault Or sAppendToFile Or sBindings Or SQLDMOScript2_NoCollation
> ' Connect to local server
> sql.Connect "(local)", strLogin, strPwd
> Set db = sql.Databases(strDataBase, "dbo")
>
> ' Script Views, ignoring system views and informational schemas
> For Each genObj In db.Views
> If genObj.SystemObject = False Then
> genObj.Script intOptions, StrFilePath
> End If
> Next
> MsgBox "Finished generating SQL scripts."
> End Sub
> --usage
> Call ScriptDB("UserName","Password","DatabaseName","C:\MyResults.SQL")
>
> "Ryan" <Tyveil@.newsgroups.nospam> wrote in message
> news:uulTfxXOHHA.4100@.TK2MSFTNGP04.phx.gbl...
>|||Sure, that makes sense. The thing is this isn't a usual process. I am
needing to create a non-production (test) database off of the production
database so I thought there would be a way to simply copy the entire
database (including objects such as views, stored procedures, etc) in one
fell swoop. I guess another option would be to detach the production
database and make a copy of the db files before reattaching it. The only
downside to this is downtime on the production side. Thanks for the info.
Ryan
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45ACE89E.2020400@.realsqlguy.com...
> Ryan wrote:
> I wouldn't say you're "using" them wrong, I'd say you're "creating" them
> wrong. The scripting method that you discovered is not a workaround, it's
> the accepted method of working with database objects. You should be
> creating your views with a script, and archiving that script in a version
> control system, like Visual SourceSafe or Perforce. This allows you to
> track the revision history of your objects, gives you a "rollback" method
> for undo-ing changes, AND it makes your objects more portable. To "copy"
> and object to a new location, you simply run the CREATE script again, in
> the new location.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Ryan wrote:
> Sure, that makes sense. The thing is this isn't a usual process. I am
> needing to create a non-production (test) database off of the production
> database so I thought there would be a way to simply copy the entire
> database (including objects such as views, stored procedures, etc) in one
> fell swoop. I guess another option would be to detach the production
> database and make a copy of the db files before reattaching it. The only
> downside to this is downtime on the production side. Thanks for the info.
>
Why not just do a backup of the production DB, and restore that onto
your Dev server?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Because the database structure doesn't exist on the Dev server. To do a
restore doesn't the database structure have to already exist? At least I
couldn't find a way to do it otherwise. I just did a detach, copy file,
reattach to both the dev and production server. This seems to be the
easiest solution.
Ryan
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45AD3AEC.3080907@.realsqlguy.com...
> Ryan wrote:
> Why not just do a backup of the production DB, and restore that onto your
> Dev server?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Ryan wrote:
> Because the database structure doesn't exist on the Dev server. To do a
> restore doesn't the database structure have to already exist? At least I
> couldn't find a way to do it otherwise. I just did a detach, copy file,
> reattach to both the dev and production server. This seems to be the
> easiest solution.
>
Nope, definately not true. You can restore a backup, give it a new
database name, and it will create the database "on the fly".
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Ah.. ok thanks for the info. I will definitely use this for future
solutions.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45AE2D47.8030504@.realsqlguy.com...
> Ryan wrote:
> Nope, definately not true. You can restore a backup, give it a new
> database name, and it will create the database "on the fly".
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com