Skip to content

Instantly share code, notes, and snippets.

@smoothdeveloper

smoothdeveloper/create.table.based.on.existing.schema.sql

Created July 26, 2016 19:37
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save smoothdeveloper/88d6d2eb4e1bd7d4db61e0ab45a41a2e to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save smoothdeveloper/88d6d2eb4e1bd7d4db61e0ab45a41a2e to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. smoothdeveloper created this gist Jul 26, 2016.
    75 changes: 75 additions & 0 deletions create.table.based.on.existing.schema.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,75 @@
    declare
    @templateSchemaName nvarchar(max)
    , @templateTableName nvarchar(max)
    , @newSchemaName nvarchar(max)
    , @newTableName nvarchar(max)

    with columninfo as (
    select
    t.name table_name
    , c.name column_name
    , c.is_nullable
    , c.precision
    , c.scale
    , ty.name data_type
    , c.max_length
    , c.is_computed
    , cc.is_persisted
    , cc.definition
    from
    sys.schemas s
    inner join sys.tables t on t.schema_id = s.schema_id
    inner join sys.columns c on c.object_id = t.object_id
    inner join sys.types ty on ty.user_type_id= c.user_type_id
    left join sys.computed_columns cc on c.column_id = cc.column_id and c.object_id = cc.object_id
    where
    s.name = @templateSchemaName
    and t.name = @templateTableName
    )
    , complete_column as (
    select
    quotename(column_name) + ' ' +
    case
    when is_computed = 1 then
    ' as ' + definition
    + case
    when is_persisted = 1 then
    ' persisted'
    + case when is_nullable = 1 then ' null' else ' not null' end
    else '' end
    else
    case
    when data_type like '%char%' then data_type + '(' + cast(max_length as nvarchar(max)) + ')'
    when data_type like 'decimal%' then data_type + '(' + cast(precision as nvarchar(max)) +','+cast(scale as nvarchar(max)) + ')'
    else data_type
    end
    + case
    when is_nullable = 1 then ' null'
    else ' not null'
    end
    end
    complete_column_expr
    , table_name
    from
    columninfo
    )
    select
    'create table ' + quotename(@newSchemaName) + '.' + quotename(@newTableName) + ' ('
    +
    stuff(
    (
    select
    ',
    '
    + complete_column_expr
    from
    complete_column innerc
    where innerc.table_name = outerc.table_name
    for xml path(''), type
    ).value('.[1]', 'nvarchar(max)')
    ,1,2, '')
    + ')'
    from
    complete_column outerc
    group by
    table_name