点击(此处)折叠或打开
- --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
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16131092/viewspace-2138359/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/16131092/viewspace-2138359/