How to copy the structure of a database?

  • I mean I do not need any data, but all objects in a database.

    Many thanks for all inputs in advance.

  • In Object Explorer, right click on the database, select Tasks, then Generate Scripts, then follow along with the wizard.

  • probably the easiest way is to use the Scripting wizard in SSMS;

    right click on the database in question, Choose "Tasks", then "Generate Scripts"

    follow the wizard prompts;

    you might need to go to Tools>>Options and make some changes there under Scripting options to make sure all teh elements you want appear.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • What is the lowest level of permissions that a user has to have to use the script wizard?

    Have not been able to find that. I thought if the user is a ddladmin it would work, but it does not.

  • According to BOL it should be db_ddladmin.

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

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • I think the issue is SSMS itself tends to require higher level permissions than you'd think would be required. if you access SMO directly(like from powershell) , ddl_admin or even VIEW DATABASE/VIEW ANY DATABASE is sufficient, but SSMS, since it's using a wizard that plans on the ability to script everything possible , wants to assume you have dbo access so that it doesn't need to check permissions.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Ellen-477471 (12/21/2012)


    What is the lowest level of permissions that a user has to have to use the script wizard?

    Have not been able to find that. I thought if the user is a ddladmin it would work, but it does not.

    Oooo... bad news. First, I'd ask the question, why would you allow a user to do this?

    Second, be very aware of the incredible power hidden in the ddladmin role. It can drop tables, create tables, and do all sorts of other asty stuff. I would't let a user have these privs unless it were on a Development box.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply