Copy complete structure of a table

Question

Using some methods, when you create a copy of a table you lose indexes, PK, FK, etc. For example in SQL Server I can say:

select * into dbo.table2 from dbo.table1;

This is just a simple copy of the table; all of the indexes / constraints are missing. How can I copy a table structure (without using a backup)?

Answer 1

You can script a table relatively easy using the UI of course:

This will output a CREATE TABLE script and you only have to search and replace the old name with the new name (and verify that an object with the new name doesn't already exist).

But if you're trying to automate this (e.g. generate the create table script in code), it is a little more cumbersome. The above scripting option doesn't just pull the entire CREATE TABLE DDL from a single location in the metadata; it does a whole bunch of magic behind the scenes in the code to generate the eventual CREATE TABLE script (you can use Profiler to see where it gets its data, but you can't see how it assembles it). I suggested an option for this:

http://connect.microsoft.com/SQLServer/feedback/details/273934

However this was met with very few votes and was quickly shot down by Microsoft. You may find it much more worthwhile to use a 3rd party tool for generating schema (I've blogged about this).

In SQL Server 2012 there are new metadata functions that allow you to get much closer than the work you have to do in 2005, 2008 and 2008 R2, piecing together column information from the metadata (which has a lot of caveats, for example if it's decimal you have to add the precision/scale, if [n[var[char]] you have to add the length specification, if n[var]char you have to cut the max_length in half, if it is a MAX you have to change -1 to MAX, etc etc). In SQL Server 2012 this part is a little easier:

SELECT name, system_type_name, is_nullable FROM
  sys.dm_exec_describe_first_result_set('select * from sys.objects', NULL, 0)

Results:

name                   system_type_name  is_nullable
--------------------   ----------------  -----------
name                   nvarchar(128)     0
object_id              int               0
principal_id           int               1
schema_id              int               0
parent_object_id       int               0
type                   char(2)           0
type_desc              nvarchar(60)      1
create_date            datetime          0
modify_date            datetime          0
is_ms_shipped          bit               0
is_published           bit               0
is_schema_published    bit               0

I've blogged about this, too.

Arguably this is much closer to your targeted CREATE TABLE statement than a convoluted approach using sys.columns, but there is still a lot of work to do. Keys, constraints, text in row options, filegroup information, compression settings, indexes, etc. It's a very long list and I'll once again suggest you look at a 3rd party tool for this instead of, at the risk of repeating an over-used analogy, re-inventing the wheel.

That all said, if you need to do this through code but you can do it outside of SQL Server, you can consider SMO/PowerShell. See this tip and the Scripter.Script() method.