automatic generate create table sql

来源：这里教程网时间：2026-03-02 11:12:07 作者：

点击(此处)折叠或打开

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

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

编辑推荐：

monitor temp db usage for MSSQL

automatic generate create table sql

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推