Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.

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

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)

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

create procedure scriptTable
@sourceSchema varchar(100),
@sourceTable varchar(100),
@destSchema varchar(100) = null,
@destTable varchar(100) = null,
@withDrop bit = 1,
@xmlOutput bit = 1
@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 + '

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
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)

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.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

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


© Copyright 2020 SQLTraining Ltd.