SQL Server: Creating table from stored schema?

Question

I'd like to create a stored procedure that takes the name of table as a parameter, and then creates that table, if it doesn't exist, according to a schema stored somewhere.

Is that possible--can I store a schema and use is as a template to create tables from? Or must I hard-code the CREATE TABLE and its scheme in the stored procedure itself?

The idea here is to be able to change the schema without changing the stored procedure (separation of concerns), because I'm probably going to expand the procedure to do more.

This is as far as I got without knowing whether to just hard-code the schema for the table in the sproc, or whether I can store and pull that schema somehow.

CREATE PROCEDURE GenerateTable 
    @tableName as nvarchar(128)
AS
BEGIN
    IF OBJECT_ID(@tableName,'U') IS NULL
     CREATE TABLE @tableName ????????
    ENDIF
END 
GO

Answer 1

If you want to store schema and generate tables on demand, I would recommend you review the schema for Information_Schema.Columns it stores enough information that you could generate the create statement for a given table (structure but not constraints). Personally I think it is less overhead to script/rescript the stored procedures based on the schema of the table they will be interacting with, similar to what is described in this post

Answer 2

I'll start out with a warning - Changing your database schema programmatically is usually going to be a bad idea.

That said, all you need now is to create a string and EXEC it; eg:

DECLARE @CreateString VARCHAR(200)
SELECT @CreateString = 'CREATE TABLE ' + @tableName + ''
EXEC (@CreateString)

Answer 3

Yes, this sort of thing can be done; our company has developed a tool to do this which has been phenomenally valuable for us. Unfortunately I can't share it with you, but it works very much along the lines you describe in your question.

Answer 4

Yes, you can do this. In fact, Microsoft do this in its system stored procedures. You can look for them and see that code. Also, I did something like this in the past, but to create an stored procedure, not a table. The idea is basically the same, you can guide from my code.

CREATE procedure [dbo].[ScriptAbm]
@tabla varchar(50)
AS

set nocount on

if object_id(@tabla) is null begin
    select 'no existe la tabla'
    return
end

select identity(int, 1,1) as idcolumna, c.name AS Columna, t.name AS Tipo, 
    case when t.name in ('varchar', 'char') then cast(c.length as varchar)
    else null end as Longitud, ',' as coma
into #t
from syscolumns c
inner join systypes t on c.xtype = t.xtype
where c.id = object_id(@tabla)

declare @max int
select @max = max(idcolumna) from #t

update #t set coma = '' where idcolumna = @max

select  texto from (

    select 0 as orden, 1 idcolumna, 'CREATE PROCEDURE Actualizar' + @tabla texto

    union

    select 1, idcolumna, '@' + columna + ' ' + tipo
     + case when longitud is not null then ' (' + longitud + ')' else '' end 
     + coma
    from #t

    union

    select 2, 1, 'AS'

    union 

    select 3, 1, 
     CHAR(13) + CHAR(10) + 
     '-- =================================================================' + 
     CHAR(13) + CHAR(10) + 
     '-- Fecha: ' + convert(varchar, getdate(), 103) + 
     CHAR(13) + CHAR(10) + 
     '-- Autor: Leonardo Arias Paz ' +
     CHAR(13) + CHAR(10) + 
     '-- Descripción: Alta y modificación de la tabla ' + @tabla +
     CHAR(13) + CHAR(10) + 
     '-- =================================================================' +
     CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)

    union

    select 3 as orden, 1, 'SET NOCOUNT ON' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)

    union 

    select 3, 2, 'IF @' + (SELECT columna from #t where idcolumna = 1) + ' = 0 BEGIN'

    union

    select 4, 1, char(9) + char(9) + 'INSERT INTO ' + @tabla + ' ('

    union 

    select 5, idcolumna, char(9) + char(9) + char(9) + columna + coma
    from #t

    union 

    select 6, 1, char(9) + char(9) + ')'

    union

    select 7, 1, char(9) + char(9) + 'VALUES ('

    union

    select 8, idcolumna, char(9) + char(9) + char(9) + '@' + columna + coma
    from #t

    union

    select 9, 1, char(9) + char(9) + ')'

    union

    select 9, 2, 'END ELSE BEGIN'

    union

    select 9, 3, char(9) + char(9) + 'UPDATE ' + @tabla + ' SET '

    union

    select 10, idcolumna, char(9) + char(9) + char(9) + columna + char(9) + ' = @' + columna + coma
    from #t
    where idcolumna > 1

    union

    select 11, idcolumna, char(9) + char(9) + 'WHERE ' + columna + ' = ' + '@' + columna
    from #t
    where idcolumna = 1

    union

    select 12, 1, 'END' 

    union

    select 13, 1, CHAR(13) + CHAR(10) + 'SET NOCOUNT OFF' 

    union

    select 13, 2, CHAR(13) + CHAR(10) + 'GO'

) q
order by orden, idcolumna

set nocount off

NOTE:

When I have all the code I need I print it out, you should use

EXEC sp_executesql @code

where @code is an nvarchar variable that store your generated code.