Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is there a way to generate a create script from an existing table purely in T-SQL (that is without using SMO, since T-SQL does not have access to SMO). Let's say a stored procedure that receives a table name and returns a string that contains the create script for the given table?

Now let me describe the situation I'm facing, as there may be a different way to approach this. I have an instance with several dozen databases. These database all have the same schema, all the same tables, index and so on. They were created as a part of a third party software installation. I need to have a way to work with them so that I can aggregate data from them in ad-hoc manner. Nice people at dba.se have already helped me here How to create a trigger in a different database?

Currently I need to find a way to make a select from a table across all the databases. I have recorded all the database names into a table called Databasees and I wrote the following script to execute a select statement on all of them:

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
DROP TABLE #tmp

select * into #tmp from Database1.dbo.Table1 where 1=0
DECLARE @statement nvarchar(max) = 
  N'insert into #tmp select * from Table1 where Column1=0 and Cloumn2 =1'

DECLARE @LastDatabaseID INT
SET @LastDatabaseID = 0

DECLARE @DatabaseNameToHandle varchar(60)
DECLARE @DatabaseIDToHandle int

SELECT TOP 1 @DatabaseNameToHandle = Name,
@DatabaseIDToHandle = Database_Ref_No
FROM Databasees
WHERE Database_Ref_No > @LastDatabaseID
ORDER BY Database_Ref_No

WHILE @DatabaseIDToHandle IS NOT NULL
BEGIN

  DECLARE @sql NVARCHAR(MAX) = QUOTENAME(@DatabaseNameToHandle) + '.dbo.sp_executesql'
  EXEC @sql @statement

  SET @LastDatabaseID = @DatabaseIDToHandle
  SET @DatabaseIDToHandle = NULL

  SELECT TOP 1 @DatabaseNameToHandle = Name,
  @DatabaseIDToHandle = Database_Ref_No
  FROM Databasees
  WHERE Database_Ref_No > @LastDatabaseID
  ORDER BY Database_Ref_No
END

select * from #tmp
DROP TABLE #tmp

However the script above fails with the following message:

An explicit value for the identity column in table '#tmp' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Adding this:

SET IDENTITY_INSERT #tmp ON

does not help, since, I can't specify the column list and keep it generic.

In SQL there is no way to switch the identity on a given table off. You can only drop a column and add a column, which, obviously changes the column order. And if the column order changes, you, again, need to specify the column list, that would be different depending on the table you query.

So I was thinking if I could get the create table scrip in my T-SQL code, I could manipulate it with string manipulation expressions to remove the identity column and also add a column for the Database name to the result set.

Can anyone think of a relatively easy way to achieve what I want?

share|improve this question

3 Answers 3

up vote 8 down vote accepted

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.)

share|improve this answer

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.

share|improve this answer
1  
You can create a #temp table and then reference it from dynamic SQL just fine. It's only if you create it in that scope that it is not visible after the dynamic SQL has executed. –  Aaron Bertrand Nov 11 '13 at 0:56
    
Also I disagree with your opening statement, that it is not possible to generate a CREATE TABLE script in T-SQL. It's not easy, but it's not impossible - especially in SQL Server 2012. –  Aaron Bertrand Nov 11 '13 at 1:09
1  
I changed the post to clarify your concerns, @AaronBertrand. You have an interesting use case of the new sys.dm_exec_describe_first_result_set in your answer but you still have to do most of the grunt work manually, so it does not really change the point I am trying to make. –  Sebastian Meine Nov 11 '13 at 3:56

You can generate a rough CREATE TABLE using dynamic SQL from the data in INFORMATION_SCHEMA.COLUMNS.

If you need to add constraints etc you will need to add information from some of the other INFORMATION_SCHEMA views.

Microsoft documentation

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.