locked
Update table definitions every night T-SQL RRS feed

  • Question

  • Hi Friends,

    I have small issue ..... I have a Live database and have around 30+ tables in this ...

    so what I have done is I have create same copy in other database (only table definitions) ...

    Process :  Live database --- > Task --> Generate Scripts

    so It has created one script I have copied whole script I ran this one on another database (new database ) .... So tables has created (only table definitions)

    So my Question is I need to update these tables every night (only table definitions) based on Live database table definitions....

    How to update them every night all the table definitions based one Live database....

    Could any one tell me how to proceed... we need to write any Query for this ????

    Thanks for your help friends.

    Thanks,

    Rama

    Monday, July 2, 2012 9:48 AM

Answers

  • This is very easy to do by using SSIS transfer server object task. Just one task can solve your proble.

    create an SSIS package by using the Transfer Server Object Task as below. (change your database name):

    Can't show the image, sorry.

    The key thing, is in Destination section, you need to set the dropObjectFirst to True, which will recreate all your table again. Remember to set the Copy Data to False.

    In objectstoCopy section: set CopyAllTables to True.

    Schedule a job to run the SSIS package. That's it.


    View Steven Wang's profile on LinkedIn | Blog: MSBICOE.com | MCITP - BI, SQL Developer & DBA

    Hate to mislead others, if I'm wrong slap me. Thanks!




    Monday, July 2, 2012 9:59 AM

All replies

  • This is very easy to do by using SSIS transfer server object task. Just one task can solve your proble.

    create an SSIS package by using the Transfer Server Object Task as below. (change your database name):

    Can't show the image, sorry.

    The key thing, is in Destination section, you need to set the dropObjectFirst to True, which will recreate all your table again. Remember to set the Copy Data to False.

    In objectstoCopy section: set CopyAllTables to True.

    Schedule a job to run the SSIS package. That's it.


    View Steven Wang's profile on LinkedIn | Blog: MSBICOE.com | MCITP - BI, SQL Developer & DBA

    Hate to mislead others, if I'm wrong slap me. Thanks!




    Monday, July 2, 2012 9:59 AM
  • create a Job and Schedule a Job for every night .(Example create the store procedure and schedule the Stored procedure to run every night).

    Find the link for how to Schedule a Job

    http://msdn.microsoft.com/en-us/library/ms191439.aspx


    With Thanks and Regards
    Sambath Raj.C
    click "Proposed As Answer by" if this post solves your problem or "Vote As Helpful" if a post has been useful to you
    Happy Programming!

    Monday, July 2, 2012 10:06 AM
  • Hi Thanks for your reply.... Could create a job once I have a stored procedure in my hand.

    but how to write the code inside the stored procedure ..... to update all the table definitons based on live database...

    if you send one sample code that would be great

    Thanks,

    Rama

    Monday, July 2, 2012 10:22 AM
  • Hi,

    This can be done by refreshing database every night using scheduled tasks. plz see the following link.

    http://weblogs.sqlteam.com/tarad/archive/2009/02/25/How-to-refresh-a-SQL-Server-database-automatically.aspx


    ESHANI. Please click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

    Monday, July 2, 2012 10:29 AM
  • Thanks for your reply Eshani...

    But your Query brings everything I mean data also.....

    I need to refresh only table definitions I dont want data ..... is that possible ???

    Thanks for your reply...

    Thanks,

    Rama


    • Edited by BandSr Monday, July 2, 2012 11:10 AM
    Monday, July 2, 2012 11:10 AM
  • I think you best option is to use SSIS as suggested by Steven. There are not really other "build in" options for doing this automatically.

    Alternatively you can look at some of the 3rd. party tools like SQLcompare, but they are of course not for free.

    You could most likely also write your own code to do the job, but that depends on you programming skills (...I'm sure I can't do it..:-) ).


    Steen Schlüter Persson (DK)

    Monday, July 2, 2012 1:27 PM
  •  Hi All,

    I have fallowed steve process ... that is working perfect ......... Thanks for your responce all

    Monday, July 2, 2012 4:29 PM