How to copy views from one database to another dat

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!

Answer 1

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.

Answer 2

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.

Answer 3

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;

Answer 4

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.

Answer 5

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)