Dynamically generated Create Table Statement SQL Server

— 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				
			
Advertisements