automatic generate create table sql

发布时间：2020-08-06 22:21:45 作者：o0yuki0o
来源：ITPUB博客阅读：144

点击(此处)折叠或打开

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

automatic generate create table sql

相关阅读