copy whole table structure from one table to another in sql server

Question

I wrote a query to copy the table's structure from one table to another table in sql server.

SELECT * INTO Database.Schema.TableNew 
FROM Database.Schema.OldTable 
WHERE 1=2

But through that query, i am able to copy table's structure, but it is not copying checksum's function value.

Solution 1

A work around would be right click on the table -> Script Table as -> Create script -> New query window.

You have all the constraints / keys as the original table. Run the script in your new db.

Of course, this is a solution only when dealing with few tables as it gets clumsy if you had to repeat it manually for multiple tables.

Solution 2

I wrote a SP to "clone" a table. But it is not finished because I haven't need more.

The script will copy the primary key but not the other constraints, if you want to finish the script you just have to follow the same logical. You can use sys.sp_fkeys, sys.sp_indexes (...) to do it.

I don't know if there is a best way, but this one works.

Also you need to know one thing before. In SQL Server, the constraint name must be unique in your db, so you can't clone a table with the same constraint names.

If someone want to finish it and share it again, he is welcome! You have to change DATABASE_NAME and YOUR_SCHEMA with your own value.

If you want only the structure without data you can add "WHERE 1 = 2" at the end of the copy structure request.

Don't hesitate to ask if you don't succeed with the other constraints.

USE [DATABASE_NAME]
GO
/****** Object:  StoredProcedure [YOUR_SCHEMA].[sp_CloneTable]    Script Date: 29/04/2016 12:46:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [YOUR_SCHEMA].[sp_CloneTable]
    @new_table_name varchar (100),
    @old_table_name varchar(100),
    @table_owner varchar (100),
    @table_qualifier varchar (100)
AS

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

BEGIN TRANSACTION;

    DECLARE 
        @SQL varchar(200),
        @pkey_name varchar (100),
        @tmp_column_name varchar (150),
        @tmp_pk_name varchar (150);


    -- COPY STRUCTURE AND DATAS (except keys, constraint... etc)
    SET @SQL = 'SELECT * INTO ' + @table_owner + '.' + @new_table_name + ' FROM ' + @table_owner + '.' + @old_table_name;
    EXEC (@SQL);


    -- PRIMARY KEYS TABLE
    DECLARE @table_primary_keys TABLE (
        TABLE_QULIFER varchar(150),
        TABLE_OWNER varchar(150),
        TABLE_NAME varchar(150),
        COLUMN_NAME varchar(150),
        KEY_SEQ INT,
        PK_NAME varchar(150)
    )

    INSERT INTO @table_primary_keys EXEC sp_pkeys @old_table_name, @table_owner, @table_qualifier;

    -- Contrainst name
    SELECT @pkey_name = PK_NAME FROM @table_primary_keys GROUP BY PK_NAME;

    DECLARE cursor_primary_key CURSOR FOR
        SELECT COLUMN_NAME, PK_NAME FROM @table_primary_keys;

    OPEN cursor_primary_key;
    FETCH NEXT FROM cursor_primary_key INTO @tmp_column_name, @tmp_pk_name;

    SET @SQL = 'ALTER TABLE ' + @table_owner + '.' + @new_table_name + ' ADD CONSTRAINT pk_' + @new_table_name + ' PRIMARY KEY CLUSTERED ('; 
    WHILE @@FETCH_STATUS = 0
    BEGIN 
        IF @pkey_name <> @tmp_pk_name
        BEGIN;
            THROW 50000, 'Two primary keys differents.', 1;
        END;
        SET @SQL = @SQL + @tmp_column_name + ', ';

        FETCH NEXT FROM cursor_primary_key INTO @tmp_column_name, @tmp_pk_name;
    END
    SET @SQL = SUBSTRING(@SQL, 1, LEN(@SQL) - 1) + ')';

    EXEC (@SQL);

COMMIT TRANSACTION;
END

To start the sp:

 EXEC sp_CloneTable 'new_table_name', 'old_table_name', 'your_schema - dbo by default', 'your_db'

Comments

[MakDeveloper]

But i want to create query because i have to do this for multiple table

[Chendur]

This might help, dba.stackexchange.com/questions/18059/…