T-SQL how to create a table whose structure is in another table?

Question

How can I create a stored procedure to create a table, whose structure is stored in another table, with t-sql of course and sql server?

Answer 1

T-SQL allows to create tables on the fly using SELECT * ... INTO syntax:

SELECT * INTO TargetTable
FROM SourceTable
-- any false returning condition will do
WHERE 1 = 0

This can also be written elegantly (thanks to Deadsheep39):

SELECT TOP 0 * INTO TargetTable
FROM SourceTable

However, this will fail if TargetTable already exists, so you should check for its existence:

IF OBJECT_ID('TheSchema.TargetTable') IS NOT NULL
    DROP TABLE TargetTable

Also, no indexes, constraints or trigger will be created. Check here for more details.

If you want to go dynamic (table names are parameters) you can create and execute a dynamic query:

CREATE PROCEDURE dbo.GenerateTable
(
    @SourceTable VARCHAR(128),
    @TargetTable VARCHAR(128)
)    
AS
BEGIN
    DECLARE @SQL NVARCHAR(4000) = N'
        SELECT * INTO ' + QUOTENAME(@TargetTable) + '
        FROM ' + QUOTENAME(@SourceTable) + '
        -- any false returning condition will do
        WHERE 1 = 0'
    EXEC (@SQL)
END
GO

Answer 2

You can construct query as string with CREATE TABLE statement and execute it by using sp_executesql stored procedure.

For instance in this way:

DECLARE @query nvarchar(max) = N'CREATE TABLE Table(Col nvarchar(50) NULL)'
EXECUTE sp_executesql @query

where in the @query variable you can build based on what kind of table you need.