?
Solved

script out a list of tables/procs

Posted on 2009-12-28
7
Medium Priority
?
242 Views
Last Modified: 2012-05-08
I have a list of tables and Stored Procs I want to move from One server to another. Is it possible to pass the list of tablenames and Stored procs and just pull out a script to run in the new server?

Thanks
0
Comment
Question by:anushahanna
7 Comments
 
LVL 6

Author Comment

by:anushahanna
ID: 26135183
Just wanted to explain more clearly..

The tables and procs do not exist in the second server.

As an example, Database1 in Server1 may have 200 tables, and Database1 in Server2 has only 160 tables. I can generate the list of the remaining 40 tables and I would like to create a script with the 'create table ' scripts.. Likewise with the Stored Procs also..

thanks
0
 
LVL 5

Expert Comment

by:fhillyer1
ID: 26135217
have you considered backing up on the first server and then restoring on the secong one?
i dont know if the data on the second server can be replaced, or you just want the structure with no data?
0
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 2000 total points
ID: 26135222
Yes.. Use either

1. Apex SQL Diff (http://www.apexsql.com/sql_tools_diff.asp)
or
2. Red Gate Compare ( http://www.red-gate.com/products/SQL_Compare/index.htm)

and then provide the tables and procedures to be compared in both databases..
Since it is not present in your second server, both tools provide you with the option to synchronize the second server with your first server by creating a Synchronization script ( which is what you require)..

Just run the Synchronization script to get it created in the second server..
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 2000 total points
ID: 26135225
Of course you can implement it via Generate Scripts Wizard present in SSMS ( Except Express edition)..
But you can't do it via command line using Generate Scripts approach..

But the tools mentioned above, supports it..
0
 
LVL 6

Expert Comment

by:PJBX
ID: 26135273
IF this is SQL Server, couldn't you simply use the SQL Server Import Export wizard?

http://www.aspfree.com/c/a/MS-SQL-Server/SQL-2000-Data-Transfer-is-a-Snap-with-SQL-2005/

OR

Backup Database1 and Restore the backup file to Database2?
0
 
LVL 6

Author Comment

by:anushahanna
ID: 26135590
backup & restore will not work. Server1's DB has data, but Server2's DB has another set of data, but should have same structure. Hence SSIS also will not work.

If I use Generate Scripts Wizard, it does not help to just choose those 40 tables, even if I can selected them, can I?

so, apart from 3rd party tools, what I need to get done is not possible with inbuilt tools, right?
0
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 2000 total points
ID: 26137047
>> If I use Generate Scripts Wizard, it does not help to just choose those 40 tables, even if I can selected them, can I?

You can select only the required objects but the limitation is that you achieve it using Wizard but can't do it in command line ( as you requested)..

If you prefer command line version, then you need to have third party tools.( Just generation of scripts and no comparison of scripts across databases)
If you don't want to spend on the extra cost, just proceed with the wizard.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Screencast - Getting to Know the Pipeline
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question