copy views from one database to another in the same server 2

[shanghai2004]

Dear Expert,

I need to copy all views from one database to another in the same SQL server. Can I create a SQL program to do it?
If I pick all view names from sysobjects table, how do I avoid picking the system views?

Thanks

 

[jbenson001]

Go into enterprise manager to the db where the views exist. Select all the views you want to copy. Right click and choose All Tasks -->> Generate SQL script. Click on preview. Click on Copy. You can then paste the script into query analyzer and run it.

Jim

 

[shanghai2004]

Hi Jim,

Follow your instructions I copied Views successfully from one database to another. But when I tried to copy Stored Procedures I got many messages like the following:

Cannot add rows to sysdepends for the current stored procedure because it
depends on the missing object 'ITAUTOASGNCOND'. The stored procedure will
still be created.

The numbers of procedures in both databases are the same. But the numbers of rows in sysdepends table are not equal in two databases. Do I need to worry about the sydepends table?

I guess it may relate to the order of the Stored Procedures that I create. Should I need to change the script file generated to make it in right order? If yes, is there an easy way to find the right order?

Thanks


Winnie

 

[SQLDenis]

just run the script twice for the procs

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[shanghai2004]

Hi Denis,

I think that 'run the script twice for the procs' does not solve my problem because there are drop procedure statements in the script. Also all the drop statements are executed before executing create procedures statements.
If I do not drop a procedure will SQL lets me create a new procedure with the same name?


Thanks

Winnie

 

[SQLDenis]

take out all the drop statements, replace all the create proc with alter proc statements (I left that part out ;-( )
and then rerun it

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/