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.