How to copy views from one database to another database

Question

I have two databases with same structure in MS SQL server.

I'd like to copy all views another database.

I tried to use Export data functionality by DTS (that works with the table objects).

But that executes the SQL & creates the table object.

I don't want to execute that just want to copy the view so that I can open them in design view.

I tried to use create new view in destination database & copy SQL query of the view of the source database & save the view. That works works exactly same that I want, But I have number of views & number of copies!

Solution 1

Right click on your database and say Tasks->Generate scripts. SQL Server Management Studio is able to generate the CREATE scripts for you.

Then you simple copy this script and execute it on the target server/database.

Solution 2

I know this is a VERY late answer, however i think this might prove usefull for some (if you do not have a gui like sql server management studio)

select * 
from INFORMATION_SCHEMA.VIEWS

here you get a column named "view_definition" in sql server, (this works on databases from other vendors too)

Solution 3

Right click the database, choose Tasks, and then Generate Script. This will allow you to generate a single script containing all views in the database.

Solution 4

simple code to copy one view

USE DatabaseA;
GO

DECLARE @sql NVARCHAR(MAX);

SELECT @sql = definition
FROM sys.sql_modules
WHERE [object_id] = OBJECT_ID('dbo.ViewName');

EXEC DatabaseB..sp_executesql @sql;

Solution 5

If you have access to Visual Studio and have a database project type, you can 1) Import all the ddl, views and tables included 2) Easily add these to integrated source control 3) Migrate whole or part to new database

After the initial creation of a database project, you will be prompted for connection to SQL Server instance and a database name. When done importing, the ddl for the entire database will be available in a tree very similar to SSMS tree but with the DDL files rather than the objects from which it was derived.

View more solutions

Comments

[Christopher Bonitz]

Please supply information about version. Are there anything odd about the view. Do you use encryption?

[Micah]

In SQL Server 2012 at least, this field strips all the line breaks out of the view creation script, making it useless for any view with a comment before its SQL statement.

[Bernd Wechner]

This is fine, but how do you take this and insert in into INFORMATION_SCHEMA.VIEWS in another database?

[Christopher Bonitz]

@BerndWechner you should be able to just take the content from column: view_definition And then input that straight into the other DB. Although do notice that migration between different versions of SQL databases may cause errors.