Is there a way to generate table create script in TSQL?

Back in 2007, I asked for an easy way to generate a CREATE TABLE script via T-SQL rather than using the UI or SMO. I was summarily rejected.

However, SQL Server 2012 makes this very easy. Let's pretend we have a table with the same schema across multiple databases, e.g. dbo.whatcha:

CREATE TABLE dbo.whatcha
(
  id INT IDENTITY(1,1), 
  x VARCHAR(MAX), 
  b DECIMAL(10,2), 
  y SYSNAME
);

The following script uses the new sys.dm_exec_describe_first_results_set dynamic management function to retrieve the proper data types for each of the columns (and ignoring the IDENTITY property). It builds the #tmp table you need, inserts from each of the databases in your list, and then selects from #tmp, all within a single dynamic SQL batch and without using a WHILE loop (that doesn't make it better, just simpler to look at and allows you to ignore Database_Ref_No entirely :-)).

SET NOCOUNT ON;

DECLARE @sql NVARCHAR(MAX), @cols NVARCHAR(MAX) = N'';

SELECT @cols += N',' + name + ' ' + system_type_name
  FROM sys.dm_exec_describe_first_result_set(N'SELECT * FROM dbo.whatcha', NULL, 1);

SET @cols = STUFF(@cols, 1, 1, N'');

SET @sql = N'CREATE TABLE #tmp(' + @cols + ');'

DECLARE @dbs TABLE(db SYSNAME);

INSERT @dbs VALUES(N'db1'),(N'db2');
  -- SELECT whatever FROM dbo.databases

SELECT @sql += N'
  INSERT #tmp SELECT ' + @cols + ' FROM ' + QUOTENAME(db) + '.dbo.tablename;'
  FROM @dbs;

SET @sql += N'
  SELECT ' + @cols + ' FROM #tmp;';

PRINT @sql;
-- EXEC sp_executesql @sql;

The resulting PRINT output:

CREATE TABLE #tmp(id int,x varchar(max),b decimal(10,2),y nvarchar(128));
  INSERT #tmp SELECT id,x,b,y FROM [db1].dbo.tablename;
  INSERT #tmp SELECT id,x,b,y FROM [db2].dbo.tablename;
  SELECT id,x,b,y FROM #tmp;

When you are confident it's doing what you expect, just uncomment the EXEC.

(This trusts you that the schema is the same; it does not validate that one or more of the tables has since been changed, and may fail as a result.)


It is not possible int T-SQL to generate a full create script of a table. At least there is no build in way. you could always write your own "generator" going through the information sys.columns.

But in your case you don't need to get the full create script. All you need is the prevent the SELECT INTO from copying the identity property. The easiest way to do that is to add a calculation to that column. So instead of

select * into #tmp from Database1.dbo.Table1 where 1=0

you need to write

select id*0 as id, other, column, names into #tmp from Database1.dbo.Table1 where 1=0

To generate this statement you can again use sys.columns as in this SQL Fiddle

MS SQL Server 2008 Schema Setup:

CREATE TABLE dbo.testtbl(
    id INT IDENTITY(1,1),
    other NVARCHAR(MAX),
    [column] INT,
    [name] INT
);

The two columns we need are name and is_identity: Query 1:

SELECT name,is_identity
  FROM sys.columns
 WHERE object_id = OBJECT_ID('dbo.testtbl');

Results:

|   NAME | IS_IDENTITY |
|--------|-------------|
|     id |           1 |
|  other |           0 |
| column |           0 |
|   name |           0 |

With that we can use a CASE statement to generate each column for the column list:

Query 2:

SELECT ','+ 
    CASE is_identity
    WHEN 1 THEN QUOTENAME(name)+'*0 AS '+QUOTENAME(name)
    ELSE QUOTENAME(name)
    END
  FROM sys.columns
 WHERE object_id = OBJECT_ID('dbo.testtbl');

Results:

|        COLUMN_0 |
|-----------------|
| ,[id]*0 AS [id] |
|        ,[other] |
|       ,[column] |
|         ,[name] |

With a little XML trickery we can concatenate all of this together to get the full column list:

Query 3:

SELECT STUFF((
  SELECT ','+ 
      CASE is_identity
      WHEN 1 THEN QUOTENAME(name)+'*0 AS '+QUOTENAME(name)
      ELSE QUOTENAME(name)
      END
    FROM sys.columns
   WHERE object_id = OBJECT_ID('dbo.testtbl')
   ORDER BY column_id
     FOR XML PATH(''),TYPE
  ).value('.','NVARCHAR(MAX)'),1,1,'')

Results:

|                               COLUMN_0 |
|----------------------------------------|
| [id]*0 AS [id],[other],[column],[name] |

Keep in mind, that you cannot create a #temp table using dynamic SQL and use it outside of that statement as the #temp table goes out of scope once your dynamic sql statement finishes. So you have to either squeeze all your code into the same dynamic SQL string or use a real table. If you need to be able to execute multiple of these scripts/procedures at the same time, you need to us a random table name, otherwise they will step on each other. Something like QUOTENAME(N'temp_'+CAST(NEWID() AS NVARCHAR(40)) should make a good enough name.

Instead of copying all the data around, you could also use a similar technique to just auto generate a view for each table that unions all the incarnations of that table across all databases. Depending on the table size however that might be faster or slower, so you should test it. If you go this route, I would put those views into a separate database.


There is a good script to achieve this in the SQLServerCentral article:

  • Get DDL for any SQL 2005 table (registration required) by Lowell Izaguirre.

The current latest version of the script is also available as text here (stormrage.com).

I wish there was a way to include all the script here, because it works for me. The script is just too long to paste here.

Copyright notice:

--#################################################################################################
-- copyright 2004-2013 by Lowell Izaguirre scripts*at*stormrage.com all rights reserved.
-- http://www.stormrage.com/SQLStuff/sp_GetDDL_Latest.txt
--Purpose: Script Any Table, Temp Table or Object
--
-- see the thread here for lots of details: http://www.sqlservercentral.com/Forums/Topic751783-566-7.aspx

-- You can use this however you like...this script is not rocket science, but it took a bit of work to create.
-- the only thing that I ask
-- is that if you adapt my procedure or make it better, to simply send me a copy of it,
-- so I can learn from the things you've enhanced.The feedback you give will be what makes
-- it worthwhile to me, and will be fed back to the SQL community.
-- add this to your toolbox of helpful scripts.
--#################################################################################################

Tags:

Sql Server

Dynamic Sql

Sql Server 2012

T Sql

Recent Posts