none
How to export an entire schema (all objects inside it) in SQL Server 2016 RRS feed

  • Question

  • Hello

    Can one of you help me tell the best possible way to export an entire schema (all objects inside it) in SQL Server 2016. For example, i just to export the AdventureWorks2016CTP3.HumanResources schema in SQL Server 2016.  I see 2 options.

    a) Export and Import wizard - it does not have a single-click method. I have to scroll down and choose all tables and views. Also it does not guarantee and does not provide all finer details such as exporting indexes, constraints, stored procedures etc.

    b) Right click in SSMS => Tasks => Generate Scripts. The wizard launches. Now choose "Select specific database objects" => Schemas => HumanResources.

    Then in the Advanced Scripting Options, i choose "Schema and data" in "Types of data to script". But the output file just has the two lines thats all. It does not have actual object creation statements and rows within HumanResources schema.

    USE [AdventureWorks2016CTP3]
    GO
    /****** Object:  Schema [HumanResources]    Script Date: 10/20/2016 4:19:52 AM ******/
    CREATE SCHEMA [HumanResources]
    GO
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Contains objects related to employees and departments.' , @level0type=N'SCHEMA',@level0name=N'HumanResources'
    GO


    Thursday, October 20, 2016 4:42 AM

All replies

  • I can say methods explored by are more than enough. There is no single click method available by default.

    You can try some trird party tools for more enhanced requirements.


    Regards,
    Rohit Garg
    (My Blog)
    This posting is provided with no warranties and confers no rights.
    Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

    Thursday, October 20, 2016 10:28 AM
  • Rohit

    Actually the methods that i noted above (Export import wizard, Generate Scripts wizard) are less than impressive. They both are not "enterprise class", meaning they cannot export all objects in a schema. For example the Human Resources schema in AdventureWorks2016CTP3. I need all objects viz. tables, views, indexes, constraints, functions,stored procedures (everything in a schema).

    I don't mind using multiple clicks or multiple checkboxes, but these two tools just do not do the job.

    a) The export import GUI wizard only has tables, views (2 types of objects only).  

    b) The generate scripts wizard, as noted in the initial post in this thread, despite choosing ""Schema and data" in the Advanced Scripting Options does not have actual object creation statements and rows within HumanResources schema. It just has the create schema command (1 page . thats all)

    In Oracle database, we have the super easy export data pump, which exports all objects in the entire schema (all types of objects) with a single command.

    Hope to hear from you and other experts soon.

    -Srini

    Friday, October 21, 2016 3:37 AM
  • Please check below link to know.

    https://technet.microsoft.com/en-us/library/ms178078%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396

    Each technology has its own limitation and beauty, we can't compare. There are things which SQL Server is best from any solution and Oracle has its own region, lets not compare that.

    Lets try to  be part of solution than problem, as here in this forum we can only find and suggest solution, actually features and solutions are getting developed some where in lab of respective org's labs and development unit.

    Now also as Rohit says there are so many solutions or if you are interested so many solutions available over net to get you all or might be more than what you need.

    Please check below approach too.

    https://gallery.technet.microsoft.com/SCRIPTING-DB-DB-OBJECTS-DB-81bba072

    Generating script option gives us flexibility to  script what is required not all.

    Some more links:

    http://blogs.perficient.com/delivery/blog/2016/10/18/generate-scripts-for-data-present-in-the-tables-of-sql-server/

    http://blog.sqlauthority.com/2012/07/18/sql-server-generate-script-for-schema-and-data-sql-in-sixty-seconds-021-video/

    http://stackoverflow.com/questions/1162339/script-entire-database-sql-server


    Santosh Singh

    Friday, October 21, 2016 7:02 PM