if OBJECT_ID('sp_create_table_sql','P') is not null
drop proc sp_create_table_sql
go
create proc sp_create_table_sql ( @tablename varchar(255) )
as
begin
-- exec sp_create_table_sql 'Ad_AdGroup'
-- 0. 弘恩
-- 1. 不支持非主键类的索引
-- 2. 不支持主分键的非默认排序
-- 3. 不支持DEFAULT
-- 4. 不支持计算列
-- 5. 待完整
declare @sql_create varchar(max) = '';
declare @sql_column varchar(max);
declare @sql_primary varchar(max);
with cte as
(
select QUOTENAME( c.name )+' '+
TYPE_NAME(c.system_type_id)+' '+
case when TYPE_NAME( c.system_type_id) in ('char','varchar','decimal') then ' ( ' else '' end +
case when TYPE_NAME( c.system_type_id) in ('char','varchar','nvarchar' ) then cast(max_length as varchar) else '' end+
case when TYPE_NAME( c.system_type_id) in ('decimal' ) then cast(c.precision as varchar)+','+cast(c.scale AS varchar) else '' end+
case when TYPE_NAME( c.system_type_id) in ('char','varchar','decimal') then ' ) ' else '' end +
case when c.is_nullable = 1 then ' null ' else ' not null ' end +
case when c.is_identity = 0 then ' ' else ' identity ' end sqlstr ,
column_id
from sys.objects as o
join sys.columns as c on o.object_id = c.object_id
where o.name = @tablename and o.type = 'U'
)
select @sql_column = stuff(
(select ',' + sqlstr + CHAR(10)
from cte
order by column_id asc
for xml path('') ),1,1,'')
;
select @sql_primary = stuff((
select ',' + c.name
from sys.index_columns as i
join sys.indexes as ix on i.object_id = ix.object_id and i.index_id = ix.index_id
join sys.columns as c on i.object_id = c.object_id and i.column_id = c.column_id
where OBJECT_NAME(i.object_id) = @tablename
and ix.is_primary_key = 1
order by i.key_ordinal
for xml path('')
),1,1,'')
set @sql_create = ' create table ' + @tablename + '( '
+ @sql_column
+ case when len(@sql_primary) >= 1 then (', primary key ( ' + @sql_primary + ')') else '' end
+ ' ) '
print ' -- @sql_create -- '
print @sql_create
end
