Cloning a Table without using SELECT TOP (0) * INTO

Question

I need to create a copy of a database table. I require to set the TEXTIMAGE_ON value which can only be created at Creation time and not with ALTER TABLE afterwards.

I am also not allowed to alter any database properties beforehand. So basically NOT:

ALTER DATABASE backoffice MODIFY FILEGROUP filegroup DEFAULT

Thats the reason why i can't just use:

SELECT TOP (0) * INTO [DestinationSchema].[DestinationTable] FROM [SourceSchema].[SourceTable]

to clone my table.

I am looking basically for a script or any other way to which creates the table and than clones the columns step by step.

I am grateful for any help.

You can use sys.columns to get a list of columns. Join that to sys.types to get the datatypes. You will have to build this as dynamic sql but it shouldn't be too bad to put together. — Sean Lange, Jul 18 '14 at 16:02
In SQL Server Management Studio, you can script out the table definition. Is that a possible solution. — Gordon Linoff, Jul 18 '14 at 16:06
@GordonLinoff Unfortunately it has to completely dynamic because i don't know in advance which table will be the source. — rudimenter, Jul 18 '14 at 16:08
Do what @SeanLange suggests. Go through the information views if you want to dynamically build a create query — Allan S. Hansen, Jul 18 '14 at 16:22

score 1 · Answer 1 · answered Jul 18 '14 at 16:33

This will at least get you started. You have some work still to do here but this should give you the information you need to build some dynamic sql for this.

declare @TableName sysname = 'YourTable'

select *
from sys.columns c
join sys.types t on c.user_type_id = t.user_type_id
where c.object_id = object_id(@TableName)

You will need a lot of case expressions to accommodate for everything.

Rob · Answer 2 · 2014-07-18T16:25:03.380

0

Would something like this work?

  Select * 
     INTO NewTable
     FROM table
     WHERE 1=2

edited Jul 18 '14 at 16:25

answered Jul 18 '14 at 16:12

Rob

169
1
3
12

Gouri Shankar Aechoor · Accepted Answer · 2014-07-18T18:59:46.470

This should get you there mostly. You may still have to refine it per requirement. Hope this helps

DECLARE @TableName SYSNAME = 'TableName';

DECLARE @SQL VARCHAR(MAX),
@ColumnList VARCHAR(MAX),
@Schema VARCHAR(250);

SELECT @Schema = QUOTENAME(TABLE_SCHEMA)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @TableName;

SET @SQL = 'CREATE TABLE ' + @Schema + '.' + QUOTENAME(@TableName) + '(' + CHAR(13) + CHAR(10);

WITH cte_columns
    AS (
    SELECT
           CHAR(9) + QUOTENAME(COLUMN_NAME) + ' ' + CASE
                                                    WHEN DOMAIN_SCHEMA IS NULL THEN ''
                                                    ELSE QUOTENAME(DOMAIN_SCHEMA)
                                                    END + '.' + CASE
                                                                WHEN DOMAIN_SCHEMA IS NULL THEN DATA_TYPE + QUOTENAME(CHARACTER_MAXIMUM_LENGTH, '(')
                                                                ELSE QUOTENAME(DOMAIN_NAME)
                                                                END + CASE
                                                                      WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL'
                                                                      ELSE ' NULL'
                                                                      END AS ColumnList
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = @TableName
    )
    SELECT
           @ColumnList = COALESCE(@ColumnList + ',' + CHAR(13) + CHAR(10), '') + ColumnList
    FROM cte_columns;

SET @SQL = @SQL + @ColumnList + ')';

PRINT @SQL;

Is there a reason why you are using "DOMAIN_SCHEMA" and "DOMAIN_NAME". Because in my DB its always NULL. — rudimenter, Jul 21 '14 at 13:55
That piece of code is designed to handle UDT's(User-Defined Data Type) In our database we use UDT's a lot so I had to handle them. However if you believe you do not use it or see any scope of ever using it in the future, you may decide to take that part of the logic out. However having it in there should not cause any issues. Hope that answers your question. — Gouri Shankar Aechoor, Jul 24 '14 at 10:50

score 0 · Answer 4 · answered Jul 18 '14 at 17:56

Using the INFORMATION_SCHEMA.COLUMNS view you can extract the table definition

Something like

SELECT column_name,data_type,is_nullable,--logic for length calculation
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tablename'

This should get you started, you can use a cursor over such a query to build up your schema definition

Cloning a Table without using SELECT TOP (0) * INTO

4 Answers4