How to generate scripts to recreate table using SQL Server Management Studio [Schema and data]?

Question

I have a table in a local SQL server database. I want to recreate this table in a hosted database.

What I want to do is to have a script that when run against the hosted database, this table is recreated with all the data, etc.

How do I create this script using SQL Server Management Studio? Thanks.

Mohammed Swillam · Accepted Answer · 2012-11-23T22:12:24.017

1- Open SQL server Management Studio.

2- Right click on the DB that contains your desired table.

3- Select "Tasks => Generate Scripts...".

4- Follow on the wizard, and choose the objects that you want to generate scripts for (Tables, Views, Stored Procedures, etc... ).

5- From the next step, click on "Advanced", and for the node that is labeled "Types of data to script" choose "Schema and data".

enter image description here

6- Save your script and smile :)

score 5 · Answer 2 · answered Mar 12 '15 at 14:33

select  'create table [' + so.name + '] (' + o.list + ')' + CASE WHEN tc.Constraint_Name IS NULL THEN '' ELSE 'ALTER TABLE ' + so.Name + ' ADD CONSTRAINT ' + tc.Constraint_Name  + ' PRIMARY KEY ' + ' (' + LEFT(j.List, Len(j.List)-1) + ')' END
from    sysobjects so
cross apply
    (SELECT 
        '  ['+column_name+'] ' + 
        data_type + case data_type
            when 'sql_variant' then ''
            when 'text' then ''
            when 'ntext' then ''
            when 'xml' then ''
            when 'decimal' then '(' + cast(numeric_precision as varchar) + ', ' + cast(numeric_scale as varchar) + ')'
            else coalesce('('+case when character_maximum_length = -1 then 'MAX' else cast(character_maximum_length as varchar) end +')','') end + ' ' +
        case when exists ( 
        select id from syscolumns
        where object_name(id)=so.name
        and name=column_name
        and columnproperty(id,name,'IsIdentity') = 1 
        ) then
        'IDENTITY(' + 
        cast(ident_seed(so.name) as varchar) + ',' + 
        cast(ident_incr(so.name) as varchar) + ')'
        else ''
        end + ' ' +
         (case when IS_NULLABLE = 'No' then 'NOT ' else '' end ) + 'NULL ' + 
          case when information_schema.columns.COLUMN_DEFAULT IS NOT NULL THEN 'DEFAULT '+ information_schema.columns.COLUMN_DEFAULT ELSE '' END + ', ' 

     from information_schema.columns where table_name = so.name
     order by ordinal_position
    FOR XML PATH('')) o (list)
left join
    information_schema.table_constraints tc
on  tc.Table_name       = so.Name
AND tc.Constraint_Type  = 'PRIMARY KEY'
cross apply
    (select '[' + Column_Name + '], '
     FROM   information_schema.key_column_usage kcu
     WHERE  kcu.Constraint_Name = tc.Constraint_Name
     ORDER BY
        ORDINAL_POSITION
     FOR XML PATH('')) j (list)
where   xtype = 'U'
AND name    NOT IN ('dtproperties')

Pretty Neat script - Need to account for TABLE SCHEMA. And not sure how well it does with comments embedded within a table definition (still testing that last one) — Bill Roberts, Jul 22 '21 at 20:39

score 3 · Answer 3 · answered Nov 22 '12 at 23:14

You can do this using Generate Scripts database task. Right-click the database > Tasks > Generate Scripts... Choose "Select specific database objects" and the table you want.

On the Set Scripting Options page, click Advanced. There is an option "Types of data to script", choose "Schema and data". Choose where to save it. Next. Next. Finish.

That is the answer however, if the table contains a large amount of data I recommend using bcp out or another method to export the data. If the new server is on the same network, you could also select it as a linked server.

The script method will generate individual insert statements.

How to generate scripts to recreate table using SQL Server Management Studio [Schema and data]?

3 Answers3

Linked