— Modified the script to capture and include column default values
It has been a while since I last added to the googleverse, been taking more than I’ve been giving lately, time to give a little back.
In this short post you will find a query that builds a CREATE TABLE statement given a valid table name. The script only handles a single Primary Key Constraint and does not handle foriegn key contraints, but even with it’s limitations I hope this helps someone out there.
declare @SqlStart nvarchar(max) = ''
declare @sql nvarchar(max) = ''
declare @sqlEnd nvarchar(max) = ''
declare @table varchar(max) = 'T_ESB_Submission_Template'
set @SqlStart = 'CREATE TABLE [' + @table + '] (' + Char(13)
Select @sql = @sql + a.Column_Name + ' '
+ Data_Type
+ CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NULL or DATA_TYPE = 'xml' THEN ''
ELSE '(' +
CASE WHEN CHARACTER_MAXIMUM_LENGTH>0 THEN
CAST(CHARACTER_MAXIMUM_LENGTH as varchar(10))
ELSE 'max' END +')' END
+ CASE WHEN NUMERIC_PRECISION IS NULL OR DATA_TYPE in ('Int', 'tinyint', 'bigint', 'smallint') THEN ''
ELSE '(' + CAST(NUMERIC_PRECISION as varchar(10)) +','+CAST(NUMERIC_SCALE as varchar(10)) +')' END
+ CASE when exists ( select id from syscolumns where object_name(id)=@table
and name=a.column_name
and columnproperty(id,name,'IsIdentity') = 1 )
THEN
' IDENTITY(' +
cast(ident_seed(@table) as varchar) + ',' +
cast(ident_incr(@table) as varchar) + ')' ELSE '' END
+ CASE WHEN b.default_value is not null THEN ' DEFAULT ' +
substring(b.default_value, 2, Len(b.default_value)-2) + ' ' ELSE '' END
+ CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE ' NULL ' END + CHAR(13) + ','
from Information_Schema.COLUMNS a
join
( SELECT so.name AS table_name,
sc.name AS column_name,
sm.text AS default_value
FROM sys.sysobjects so
JOIN sys.syscolumns sc ON sc.id = so.id
left JOIN sys.syscomments SM ON sm.id = sc.cdefault
WHERE so.xtype = 'U'
AND SO.name = @table) b on b.column_name = a.COLUMN_NAME and b.table_name = a.TABLE_NAME
where a.Table_Name = @table
if((select count(1) from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where TABLE_NAME = @table) >0)
BEGIN
select @sqlEnd =char(13) + 'CONSTRAINT [PK_' + @table + '_1] PRIMARY KEY NONCLUSTERED' +char(13)
+'(' +char(13) + Column_Name + ' ASC ' + char(13)+')WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]'
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = @table
set @Sql = @SqlStart + SUBSTRING(@sql, 0, len(@sql)-1) + @sqlEnd
END
else
BEGIN
set @Sql = @SqlStart + SUBSTRING(@sql, 0, len(@sql)-1) + ')'
END
print @sql
Richard Mintz's BI Blog 