-
--CREATE PROCEDURE gen_createtbl @tbl_name varchar(100)
-
--AS
-
set nocount on
-
--声明游标需要的变量
-
declare @tblname varchar(100),
-
@colno int,
-
@colname varchar(100),
-
@IsIdt bit,
-
@ispk bit,
-
@type varchar(100),
-
@length int,
-
@decim int,
-
@isnull bit,
-
@default varchar(100),
-
@sql varchar(2000),--for create table
-
@sql2 varchar(1000),--for create pk
-
@sql3 varchar(1000), --for create CONSTRAINT
-
@sql4 varchar(1000),-- for default value
-
@sql5 varchar(1000),--for col comments
-
@sql6 varchar(1000),--for table comments
-
@tbl varchar(100),
-
@idx varchar(100),
-
@idxp varchar(100),
-
@colname2 varchar(100),
-
@comments varchar(100),--注释
-
@tbcomments varchar(100),
-
@col_id int,--索引中该字段的排列位置
-
@col_num int,--索引包含的总列数
-
@idx_type_desc varchar(100), --索引类型描述
-
@is_unique bit --是否唯一
-
set @tblname='sbj_retail_store_info'
-
set @sql4=''
-
--声明一个游标mycursor，select语句中参数的个数必须要和从游标取出的变量名相同
-
declare mycursor cursor for
-
SELECT 表名 = case when a.colorder=1 then d.name else '' end,
-
表说明 = cast((case when a.colorder=1 then isnull(f.value,'') else '' end) as varchar(100)),
-
字段序号 = a.colorder,
-
字段名 = a.name,
-
标识 = case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '1'else '' end, --IDENTITY(1,1)
-
主键 = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
-
SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then 1 else 0 end,
-
类型 = b.name,
-
--占用字节数 = a.length,
-
长度 = COLUMNPROPERTY(a.id,a.name,'PRECISION'),
-
小数位数 = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
-
允许空 = case when a.isnullable=1 then '1'else '' end,
-
默认值 = isnull(e.text,''),
-
字段说明 = cast(isnull(g.[value],'') as varchar(100))
-
FROM syscolumns a
-
left join systypes b on a.xusertype=b.xusertype
-
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
-
left join syscomments e on a.cdefault=e.id
-
left join sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id and g.name='MS_Description'
-
left join sys.extended_properties f on d.id=f.major_id and f.minor_id=0 and f.name='MS_Description'
-
where d.name =@tblname --如果只查询指定表,加上此条件,表名
-
order by a.id,a.colorder
-
/*create temp table to get the comments*/
-
create table #comtmp(
-
[sql] varchar(3000)
-
)
-
/*create temp table to get index info and order*/
-
create table #idxtmp(
-
[tb_name] varchar(100),
-
[idx_name] varchar(100),
-
[col_name] varchar(100),
-
[col_id] int,
-
[idx_type_desc] varchar(100),
-
[is_unique] bit)
-
-
insert into #idxtmp
-
SELECT
-
tab.name AS [tb_name],--[表名],
-
idx.name AS [idx_name],--[约束名称],
-
col.name AS [col_name],--[约束列名],
-
idxCol.key_ordinal AS [col_id],--[索引列顺序]
-
idx.type_desc as[idx_type_desc], --[索引类型描述]
-
idx.is_unique AS [is_unique] --[是否唯一]
-
FROM
-
sys.indexes idx
-
JOIN sys.index_columns idxCol
-
ON (idx.object_id = idxCol.object_id
-
AND idx.index_id = idxCol.index_id
-
AND idx.is_unique_constraint = 1)
-
JOIN sys.tables tab
-
ON (idx.object_id = tab.object_id)
-
JOIN sys.columns col
-
ON (idx.object_id = col.object_id
-
AND idxCol.column_id = col.column_id)
-
where tab.name=@tblname
-
-
-
-
declare mycursor2 cursor for
-
select a.[tb_name],a.[idx_name],a.[col_name],a.[col_id],b.[col_num],a.[idx_type_desc],a.[is_unique]
-
from #idxtmp a
-
left join (select [tb_name], [idx_name],count(1) col_num from #idxtmp group by [tb_name], [idx_name]) b
-
on a.tb_name=b.tb_name
-
and a.idx_name=b.[idx_name]
-
-
--打开游标
-
open mycursor
-
--从游标里取出数据赋值到我们刚才声明的2个变量中
-
fetch next from mycursor into @tblname,@tbcomments,@colno,@colname,@IsIdt,@ispk,@type,@length,@decim,@isnull,@default,@comments
-
-
--判断游标的状态
-
-- 0 fetch语句成功
-
---1 fetch语句失败或此行不在结果集中
-
---2 被提取的行不存在
-
while (@@fetch_status=0)
-
begin
-
--显示出我们每次用游标取出的值
-
--print '游标成功取出一条数据'
-
if @colno=1
-
begin
-
set @tbl=@tblname
-
set @sql='CREATE TABLE [dbo].['+@tblname+'](
-
['+@colname+'] ['+@type+'] '+(case @isnull when 0 then 'NOT NULL,' else 'NULL,'end)
-
set @sql6='EXEC sys.sp_addextendedproperty @name=N'+''''+'MS_Description'+''',@value=N'+''''+@tbcomments+''',@level0type=N'+'''SCHEMA'+''',@level0name=N'+'''dbo'
-
+''', @level1type=N'+'''TABLE'+''',@level1name=N'+''''+@tbl+''''
-
insert into #comtmp([sql]) values (@sql6)
-
-- print @sql6
-
set @sql5='EXEC sys.sp_addextendedproperty @name=N'+'''MS_Description'+''', @value=N'+''''+@comments+''',@level0type=N'+''''+'SCHEMA'+''',@level0name=N'
-
+'''dbo'+''',@level1type=N'+''''+'TABLE'+''''+',@level1name=N'+''''+@tbl+''', @level2type=N'+''''+'COLUMN'+''','+'@level2name=N'+''''+@colname+''''
-
insert into #comtmp([sql]) values (@sql5)
-
-- print @sql5
-
-
-
end
-
else
-
begin
-
set @tbl=@tbl+''
-
--去掉ETL_CRC QA_RULE_CHK_FLG QA_MANUAL_FLG CREATE_BY UPDATE_BY 这五个字段
-
if @colname in('ETL_CRC','QA_RULE_CHK_FLG','QA_MANUAL_FLG','CREATE_BY','UPDATE_BY')
-
begin
-
set @sql=@sql+''
-
end
-
else
-
begin
-
set @sql=@sql+'
-
'+'['+@colname+'] ['+(case @type
-
when 'timestamp' then 'bigint'+']'
-
when 'varchar' then @type +']' +'('+cast(@length as varchar(10))+')'
-
when 'nvarchar' then @type +']' +'('+cast(@length as varchar(10))+')'
-
when 'char' then @type +']' +'('+cast(@length as varchar(10))+')'
-
when 'decimal' then @type +']'+'('+cast(@length as varchar(10))+','+cast(@decim as varchar(3))+')'
-
else @type+']'end )+
-
(case @isnull when 0 then ' NOT NULL,' else ' NULL,'end)
-
set @sql5='EXEC sys.sp_addextendedproperty @name=N'+'''MS_Description'+''', @value=N'+''''+@comments+''',@level0type=N'+''''+'SCHEMA'+''',@level0name=N'
-
+'''dbo'+''',@level1type=N'+''''+'TABLE'+''''+',@level1name=N'+''''+@tbl+''', @level2type=N'+''''+'COLUMN'+''','+'@level2name=N'+''''+@colname+''''
-
-- print @sql5
-
insert into #comtmp([sql]) values (@sql5)
-
end
-
end
-
-
If @ispk=1
-
begin
-
set @sql2='PRIMARY KEY CLUSTERED
-
(
-
['+@colname+'] ASC
-
))
-
GO'
-
end
-
else
-
begin
-
set @sql2=@sql2+''
-
end
-
-
If @default <>'' and @colname not in('ETL_CRC','QA_RULE_CHK_FLG','QA_MANUAL_FLG','CREATE_BY','UPDATE_BY')
-
begin
-
set @sql4=@sql4+'
-
ALTER TABLE [dbo].['+@tbl+'] ADD DEFAULT '+@default+' FOR ['+@colname+']
-
GO'
-
-
end
-
else
-
begin
-
set @sql4=@sql4+''
-
end
-
-
-
-
-
--用游标去取下一条记录 -
-
fetch next from mycursor into @tblname,@tbcomments,@colno,@colname,@IsIdt,@ispk,@type,@length,@decim,@isnull,@default,@comments
-
end
-
--关闭游标
-
close mycursor
-
--撤销游标
-
DEALLOCATE mycursor
-
-
print @sql
-
print @sql2
-
print @sql4
-
--声明一个游标mycursor，select语句中参数的个数必须要和从游标取出的变量名相同
-
-
-
--打开游标
-
set @idx=0
-
open mycursor2
-
--从游标里取出数据赋值到我们刚才声明的2个变量中
-
fetch next from mycursor2 into @tblname,@idx,@colname2,@col_id,@col_num,@idx_type_desc,@is_unique
-
-
--判断游标的状态
-
-- 0 fetch语句成功
-
---1 fetch语句失败或此行不在结果集中
-
---2 被提取的行不存在
-
while (@@fetch_status=0)
-
begin
-
--显示出我们每次用游标取出的值
-
-
if @idx<>isnull(@idxp,'')
-
begin
-
set @sql3='ALTER TABLE [dbo].['+@tblname+'] ADD CONSTRAINT ['+@idx+'] '+(case when @is_unique=1 then'UNIQUE 'else '' end) +@idx_type_desc+'
-
( ['+@colname2+'] ASC'
-
end
-
else
-
begin
-
set @sql3=@sql3+'
-
['+@colname2+'] ASC'
-
end
-
-
if @col_id< @col_num
-
begin
-
set @sql3=@sql3+' ,'
-
end
-
else
-
begin
-
set @sql3=@sql3+')'
-
print @sql3
-
end
-
-
-
-
--用游标去取下一条记录 -
-
set @idxp=@idx
-
fetch next from mycursor2 into @tblname,@idx,@colname2,@col_id,@col_num,@idx_type_desc,@is_unique
-
-
end
-
--关闭游标
-
close mycursor2
-
--撤销游标
-
DEALLOCATE mycursor2
-
if object_id('tempdb..#idxtmp') is not null
-
begin
-
--select * from #idxtmp
-
drop table #idxtmp
-
end
-
select * from #comtmp
-
drop table #comtmp
-
set nocount on
-
GO
分享标题：automaticgeneratecreatetablesql
当前网址：
http://bzwzjz.com/article/pdicch.html
