Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite

I want to copy a VIEW from one server to another. I could copy tables but I can't copy views. Is there a way to do so? Thanks

share|improve this question
Does the same schema exist on both servers? If not then the view may not work if applied to the other server. You can check the view's dependencies by right clicking the view and selecting dependent objects to ensure the other server has the need objects. – SoftwareCarpenter Aug 8 '12 at 3:00

migrated from stackoverflow.com Aug 7 '12 at 23:29

2 Answers

up vote 5 down vote

The easiest way if you just want to do a quick CREATE VIEW on your destination server would be to script it out:

  1. Right-click your view
  2. Script View as -> CREATE to -> (select your choice here)

The possible choices as a destination for the CREATE VIEW script can be a new query window, a file, the clipboard, or an agent job. If you need to run this create script at a later date, the file destination is probably your best bet. But if you want to run it immediately, and you have access to the server, just script it to a new query window and connect to your destination instance/database and run the CREATE VIEW script.

share|improve this answer
The view will still need to reference the tables on the original server. Your answer is assuming that the tables inside the view exist on the other server. Are you suggesting creating the view this way will reference the original server and tables? – SoftwareCarpenter Aug 8 '12 at 2:51
1  
The OP asked about how to copy a view, not the underlying table(s). – Thomas Stringer Aug 8 '12 at 4:36
Yes, from one server to another server. Views are based on tables. The method you described will create a new view, but if applied on another server the tables need to exist as well or linked server's need to be setup. Based on the question it is hard to assume that the tables exist on the other server. This is all I am pointing out. – SoftwareCarpenter Aug 8 '12 at 5:08
up vote 1 down vote

Let's say you want to copy a view from database DB1 to database DB2 The script generated using Script View as -> CREATE to -> starts with

USE [DB1]

GO

When you execute it, it's executed against the source database, so you have to change this to

USE [DB2]

GO

Also note that you have to have all the tables that the view references in DB2. Otherwise you'll get a message such as:

Invalid object name 'Schema.TableName'.

You can create the tables using the same steps as views.

Another option is to use a 3rd party tool that will create the view DDL and and script all dependent objects, so you don't have to worry about the tables.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.