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?
The easiest way if you just want to do a quick
CREATE VIEW on your destination server would be to script it out:
- Right-click your view
- 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.
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
When you execute it, it's executed against the source database, so you have to change this to
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.