Create SQL script that create database and tables

Question

I have a SQL database and tables that I would like to replicate in another SQL Server   I would like to create a SQL script that creates the database and tables in a single script   I can create  Create  script using the SQL Management Studio for each case  Database and Tables   but I would like to know if combining the both  Create  scripts into single script would be enough   Thanks

User · Answer

Although Clayton s answer will get you there  eventually   in SQL2005 2008 R2 2012 you have a far easier option   Right-click on the Database  select Tasks and then Generate Scripts  which will launch the Script Wizard  This allows you to generate a single script that can recreate the full database including table indexes  amp  constraints stored procedures functions users etc  There are a multitude of options that you can configure to customise the output  but most of it is self explanatory   If you are happy with the default options  you can do the whole job in a matter of seconds   If you want to recreate the data in the database  as a series of INSERTS  I d also recommend SSMS Tools Pack  Free for SQL 2008 version  Paid for SQL 2012 version

User · Answer

An excellent explanation can be found here  Generate script in SQL Server Management Studio  Courtesy Ali Issa Here s what you have to do    Right click the database  not the table  and select tasks --  generate scripts Next --  select the requested table tables  from select specific database objects  Next --  click advanced --  types of data to script   schema and data   If you want to create a script that just generates the tables  no data  you can skip the advanced part of the instructions

User · Answer

In SQL Server Management Studio you can right click on the database you want to replicate  and select  Script Database as  to have the tool create the appropriate SQL file to replicate that database on another server  You can repeat this process for each table you want to create  and then merge the files into a single SQL file  Don t forget to add a using statement after you create your Database but prior to any table creation   In more recent versions of SQL Server you can get this in one file in SSMS    Right click a database  Tasks Generate Scripts   This will launch a wizard where you can script the entire database or just portions   There does not appear to be a T-SQL way of doing this

User · Answer

Yes  you can add as many SQL statements into a single script as you wish   Just one thing to note  the order matters   You can t INSERT into a table until you CREATE it  you can t set a foreign key until the primary key is inserted

User · Answer

Not sure why SSMS doesn   t take into account execution order but it just doesn   t  This is not an issue for small databases but what if your database has 200 objects  In that case order of execution does matter because it   s not really easy to go through all of these   For unordered scripts generated by SSMS you can go following  a   Execute script  some objects will be inserted some wont  there will be some errors   b   Remove all objects from the script that have been added to database   c   Go back to a  until everything is eventually executed  Alternative option is to use third party tool such as ApexSQL Script or any other tools already mentioned in this thread  SSMS toolpack  Red Gate and others     All of these will take care of the dependencies for you and save you even more time

[sql] Create SQL script that create database and tables

Examples related to sql

Examples related to sql-server