copy views to another database in Micorsoft SQL

When I tried to use DTS to copy views, instead of copying views, it created new tables based on the views and did not copy views.

There must be a code out there that can do the work.

Who is Participating?
Reza RadConnect With a Mentor Consultant, TrainerCommented:
in sql server management studio
just right click on your first database and go to tasks and then generate sql scripts
create scripts for views
and then run this script on your destination database

jogosConnect With a Mentor Commented:
Copying a view is only copying the definition of a view.

Exec sp_helptext 'myviewname'

will give you the view-definition.  The result you get you can execute on the other database.
oldbridgeAuthor Commented:
That will require to copy one view at a time. There is no mechanisam to copy all views? There must be some way. I am surprise that Microsoft did not think of offering simple copy tool.
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

oldbridgeAuthor Commented:
I need to keep the same view names in the new database. We use excel to create reports that are linked to views. Each year database is indepedent. All we do is change year in excel to refer to new database. That is why the best option is to be able to copy all views to new database with same name.
Reza RadConsultant, TrainerCommented:
the way that I suggest to you will do this for all views! just do it

but there is another way too:
create an SSIS package ( in business intelligence development studio - BIDS create a SSIS package) this is new version of old DTS pacakge .
in this package add a Transfer SQL Server Objects Task. in this task you can set source server, destination server and all objects you want to transfer, you can select all views for example
The sp_helptext solution is perfectly suitable for all views, thats why is suggested it. Just put it in a curosr-loop ( on all the views you want ( .
Output to a file and you have your 'initial view creation script' that you can review and store (source controle?) before executing.
A bit more complex is getting it executed immediate, but I strongly advise not to do this. How do you ever know what you have done?
(and yes I try to let you gain knowledge, not to be cheap labor. So I won't try to type a possible sollution I don't have in a copy-paste format at the moment)

Anthony PerkinsConnect With a Mentor Commented:
>>That will require to copy one view at a time. <<

>>There is no mechanisam to copy all views?<<
If you are talking about doing it with SQL Server Management system then all you have to do is script them all out at one time.  You can do this in under one minute.

>>I am surprise that Microsoft did not think of offering simple copy tool.<<
They did.  See above.
Anthony PerkinsCommented:
P.S.  DTS is overkill for such a simple task and besides the tool is deprecated in SQL Server 2005 in favor of SSIS.
3 suitable sollutions posted (2 by reza_rad, one by me but reza_rad was first).
And 'acperkins' asked why on a first 'not suitable' comment, but author didn't respond.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.