How to copy views from one database to another database

user_415865 0
Viewed 80244

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!

views sql-server
5 Answers

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.

user_1730270

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.

user_505520

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;
user_15439910
Related