Dynamically Script a Table
Published: Oct 08, 2017
This is a useful script that will let you dynamically script a table… yes, I realise that SSMS offers a point and click function which does the same, but if you’re building a script that you wish to run over and over on a changing architecture then you can’t afford to spend your time scripting from SSMS and maintaining all changes to the scripts over time.

Therefore this script will allow you to automate the process and therefore have a piece of code which will you can run months, maybe years, apart and it will automatically cater for changes to the underlying architecture.

Note that I’ve made this script so that it will create an exact copy of the table only. This includes null settings, computed columns etc. but it does not include indexes (therefore compression etc). Those I’ll cover separately.

Also, this may not be the most efficient method, I’m not claiming that, just that it’ll work where needed and hopefully help others out in the same way it’s helped me. I also suspect it’ll not be perfect, but if anyone finds any issues then just let me know and I’ll bug fix it accordingly.

Firstly we’ll need a table to work with…

if object_id('dbo.scriptTable') is not null drop table dbo.scriptTable
go

create table scriptTable
(
  
id int identity,
  
fName varchar(25) not null,
  
lName varchar(50) not null,
  
miscValue bit default(0) null,
  
myFloat decimal(17, 9) null,
  
fullName as (fName + ' ' + lName)
)
go


Now we just need a script which will output the table create… I’ve left a few options at the end of the script that you can use in order to utilize the output (either show in the Messages Tab of SSMS or select as clickable XML).

if object_id('scriptTable') is not null drop procedure scriptTable
go

create procedure scriptTable
  
@sourceSchema varchar(100),
  
@sourceTable varchar(100),
  
@destSchema varchar(100) = null,
  
@destTable varchar(100) = null,
  
@withDrop bit = 1,
  
@xmlOutput bit = 1
as
   declare
@dropSQL varchar(max),
          
@createSQL varchar(max),
          
@colList varchar(max),
          
@fullScript varchar(max)

  
if @withDrop = 1
      
select @dropSQL = 'if object_id(''' + @sourceSchema + '.' + @sourceTable + ''') is not null
drop table '
+ @sourceSchema + '.' + @sourceTable + '
go'

  
select @createSQL = 'create table ' + coalesce(@destSchema, @sourceSchema) + '.' + coalesce(@destTable, @sourceTable) + '
('

  
select @colList = isnull(@colList + ',' + char(10), '') + (char(9) + c.name + ' ' +
              
coalesce('as ' + cc.definition,
                  
t.name +
                  
case when t.name in ('int', 'bigint', 'date', 'datetime', 'time', 'uniqueidentifier', 'image', 'text', 'datetime2', 'datetimeoffset', 'tinyint', 'smallint', 'smalldatetime', 'float', 'sql_variant', 'bit', 'timestamp', 'xml', 'sysname') then ''
                          
when t.name in ('char', 'nchar', 'varchar', 'binary', 'varbinary') then '(' + convert(varchar(5), c.max_length) + ')'
                          
when t.name in ('decimal', 'numeric') then '(' + convert(varchar(5), c.precision) + ', ' + convert(varchar(5), c.scale) + ')'
                          
else ''
                  
end +
                  
case c.is_identity
                          
when 1 then ' identity(' + convert(varchar(10), seed_value) + ', ' + convert(varchar(10), increment_value) + ')'
                          
else ''
                  
end +
                  
case c.is_nullable
                          
when 0 then ' not null'
                          
else ' null'
                  
end +
                  
case when d.definition is null then '' else ' default' + d.definition end))
  
from sys.schemas s
  
join sys.objects o
  
on s.schema_id = o.schema_id
  
join sys.columns c
  
on o.object_id = c.object_id
  
join sys.types t
  
on c.user_type_id = t.user_type_id
  
and c.system_type_id = t.system_type_id
  
left join sys.computed_columns cc
  
on o.object_id = cc.object_id
  
and c.column_id = cc.column_id
  
left join sys.default_constraints d
  
on o.object_id = d.parent_object_id
  
and c.default_object_id = d.object_id
   left
join sys.identity_columns ic
  
on o.object_id = ic.object_id
  
and c.column_id = ic.column_id
  
where s.name = @sourceSchema
  
and o.name = @sourceTable
  
order by c.column_id

  
select @fullScript = case when @dropSQL is null then '' else @dropSQL + char(10) + char(10) end + @createSQL + char(10) + @colList + char(10) + ')'

  
if @xmlOutput = 1
      
select convert(xml, @fullScript)
  
else
       print
@fullScript
go


Hopefully the proc variables themselves are pretty self-explanatory as to what the code does when called (I've tried to name them accordingly). But if not then I would simply suggest giving it a go and seeing what happens. It's harmless and, as we all know, sometimes the best way to learn is through playing.

Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron