快上网专注成都网站设计 成都网站制作 成都网站建设
成都网站建设公司服务热线:028-86922220

网站建设知识

十年网站开发经验 + 多家企业客户 + 靠谱的建站团队

量身定制 + 运营维护+专业推广+无忧售后,网站问题一站解决

automaticgeneratecreatetablesql

点击(此处)折叠或打开

创新互联建站专注于企业营销型网站、网站重做改版、汕城网站定制设计、自适应品牌网站建设、H5技术商城网站开发、集团公司官网建设、成都外贸网站建设公司、高端网站制作、响应式网页设计等建站业务,价格优惠性价比高,为汕城等各大城市提供网站开发制作服务。

  1. --CREATE PROCEDURE gen_createtbl @tbl_name varchar(100)
  2. --AS
  3. set nocount on
  4.     --声明游标需要的变量
  5.     declare @tblname varchar(100),
  6.             @colno int,
  7.             @colname varchar(100),
  8.             @IsIdt bit,
  9.             @ispk bit,
  10.             @type varchar(100),
  11.             @length int,
  12.             @decim int,
  13.             @isnull bit,
  14.             @default varchar(100),
  15.             @sql varchar(2000),--for create table
  16.             @sql2 varchar(1000),--for create pk
  17.             @sql3 varchar(1000), --for create CONSTRAINT
  18.             @sql4 varchar(1000),-- for default value
  19.             @sql5 varchar(1000),--for col comments
  20.             @sql6 varchar(1000),--for table comments
  21.             @tbl varchar(100),
  22.             @idx varchar(100),
  23.             @idxp varchar(100),
  24.             @colname2 varchar(100),
  25.             @comments varchar(100),--注释
  26.             @tbcomments varchar(100),
  27.             @col_id int,--索引中该字段的排列位置
  28.             @col_num int,--索引包含的总列数
  29.             @idx_type_desc varchar(100), --索引类型描述
  30.             @is_unique bit --是否唯一
  31.             set @tblname='sbj_retail_store_info'
  32.             set @sql4=''
  33.      --声明一个游标mycursor,select语句中参数的个数必须要和从游标取出的变量名相同
  34.     declare mycursor cursor for     
  35.                 SELECT 表名 = case when a.colorder=1 then d.name else '' end,
  36.                 表说明 = cast((case when a.colorder=1 then isnull(f.value,'') else '' end) as varchar(100)),
  37.                 字段序号 = a.colorder,
  38.                 字段名 = a.name,
  39.                 标识 = case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '1'else '' end, --IDENTITY(1,1)
  40.                 主键 = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
  41.                  SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then 1 else 0 end,
  42.                 类型 = b.name,
  43.                 --占用字节数 = a.length,
  44.                 长度 = COLUMNPROPERTY(a.id,a.name,'PRECISION'),
  45.                 小数位数 = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
  46.                 允许空 = case when a.isnullable=1 then '1'else '' end,
  47.                 默认值 = isnull(e.text,''),
  48.                 字段说明 = cast(isnull(g.[value],'') as varchar(100))
  49.                 FROM syscolumns a
  50.                 left join systypes b on a.xusertype=b.xusertype
  51.                 inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
  52.                 left join syscomments e on a.cdefault=e.id
  53.                 left join sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id and g.name='MS_Description'
  54.                 left join sys.extended_properties f on d.id=f.major_id and f.minor_id=0 and f.name='MS_Description'
  55.                  where d.name =@tblname --如果只查询指定表,加上此条件,表名
  56.                 order by a.id,a.colorder
  57.                 /*create temp table to get the comments*/
  58.                 create table #comtmp(
  59.                 [sql] varchar(3000)
  60.                 )
  61.                 /*create temp table to get index info and order*/
  62.                 create table #idxtmp(
  63.                     [tb_name] varchar(100),
  64.                     [idx_name] varchar(100),
  65.                     [col_name] varchar(100),
  66.                     [col_id] int,
  67.                     [idx_type_desc] varchar(100),
  68.                     [is_unique] bit)    
  69.                                     
  70.                     insert into #idxtmp
  71.                     SELECT
  72.                      tab.name AS [tb_name],--[表名],
  73.                      idx.name AS [idx_name],--[约束名称],
  74.                      col.name AS [col_name],--[约束列名],
  75.                      idxCol.key_ordinal AS [col_id],--[索引列顺序]
  76.                      idx.type_desc as[idx_type_desc], --[索引类型描述]
  77.                      idx.is_unique AS [is_unique] --[是否唯一]            
  78.                     FROM
  79.                      sys.indexes idx
  80.                         JOIN sys.index_columns idxCol
  81.                          ON (idx.object_id = idxCol.object_id
  82.                              AND idx.index_id = idxCol.index_id
  83.                              AND idx.is_unique_constraint = 1)
  84.                         JOIN sys.tables tab
  85.                          ON (idx.object_id = tab.object_id)
  86.                         JOIN sys.columns col
  87.                          ON (idx.object_id = col.object_id
  88.                              AND idxCol.column_id = col.column_id)
  89.                              where tab.name=@tblname



  90.      declare mycursor2 cursor for     
  91.                     select a.[tb_name],a.[idx_name],a.[col_name],a.[col_id],b.[col_num],a.[idx_type_desc],a.[is_unique]
  92.                      from #idxtmp a
  93.                     left join (select [tb_name], [idx_name],count(1) col_num from #idxtmp group by [tb_name], [idx_name]) b
  94.                     on a.tb_name=b.tb_name
  95.                     and a.idx_name=b.[idx_name]

  96.     --打开游标
  97.     open mycursor
  98.     --从游标里取出数据赋值到我们刚才声明的2个变量中
  99.     fetch next from mycursor into @tblname,@tbcomments,@colno,@colname,@IsIdt,@ispk,@type,@length,@decim,@isnull,@default,@comments

  100.     --判断游标的状态
  101.     -- 0 fetch语句成功
  102.     ---1 fetch语句失败或此行不在结果集中
  103.     ---2 被提取的行不存在
  104.     while (@@fetch_status=0)
  105.     begin
  106.     --显示出我们每次用游标取出的值
  107.        --print '游标成功取出一条数据'
  108.      if @colno=1
  109.      begin
  110.      set @tbl=@tblname
  111.         set @sql='CREATE TABLE [dbo].['+@tblname+'](
  112.          ['+@colname+'] ['+@type+'] '+(case @isnull when 0 then 'NOT NULL,' else 'NULL,'end)
  113.         set @sql6='EXEC sys.sp_addextendedproperty @name=N'+''''+'MS_Description'+''',@value=N'+''''+@tbcomments+''',@level0type=N'+'''SCHEMA'+''',@level0name=N'+'''dbo'
  114.             +''', @level1type=N'+'''TABLE'+''',@level1name=N'+''''+@tbl+''''
  115.         insert into #comtmp([sql]) values (@sql6)
  116.         -- print @sql6
  117.         set @sql5='EXEC sys.sp_addextendedproperty @name=N'+'''MS_Description'+''', @value=N'+''''+@comments+''',@level0type=N'+''''+'SCHEMA'+''',@level0name=N'
  118.          +'''dbo'+''',@level1type=N'+''''+'TABLE'+''''+',@level1name=N'+''''+@tbl+''', @level2type=N'+''''+'COLUMN'+''','+'@level2name=N'+''''+@colname+''''
  119.          insert into #comtmp([sql]) values (@sql5)
  120.         -- print @sql5


  121.      end
  122.      else
  123.      begin
  124.          set @tbl=@tbl+''
  125.          --去掉ETL_CRC QA_RULE_CHK_FLG QA_MANUAL_FLG CREATE_BY UPDATE_BY 这五个字段
  126.          if @colname in('ETL_CRC','QA_RULE_CHK_FLG','QA_MANUAL_FLG','CREATE_BY','UPDATE_BY')
  127.              begin
  128.              set @sql=@sql+''
  129.              end
  130.          else
  131.              begin
  132.              set @sql=@sql+'
  133.              '+'['+@colname+'] ['+(case @type
  134.                                         when 'timestamp' then 'bigint'+']'
  135.                                         when 'varchar' then @type +']' +'('+cast(@length as varchar(10))+')'
  136.                                         when 'nvarchar' then @type +']' +'('+cast(@length as varchar(10))+')'
  137.                                         when 'char' then @type +']' +'('+cast(@length as varchar(10))+')'
  138.                                         when 'decimal' then @type +']'+'('+cast(@length as varchar(10))+','+cast(@decim as varchar(3))+')'
  139.                                         else @type+']'end )+
  140.              (case @isnull when 0 then ' NOT NULL,' else ' NULL,'end)
  141.              set @sql5='EXEC sys.sp_addextendedproperty @name=N'+'''MS_Description'+''', @value=N'+''''+@comments+''',@level0type=N'+''''+'SCHEMA'+''',@level0name=N'
  142.                 +'''dbo'+''',@level1type=N'+''''+'TABLE'+''''+',@level1name=N'+''''+@tbl+''', @level2type=N'+''''+'COLUMN'+''','+'@level2name=N'+''''+@colname+''''
  143.             -- print @sql5
  144.              insert into #comtmp([sql]) values (@sql5)
  145.              end
  146.          end

  147.          If @ispk=1
  148.          begin
  149.             set @sql2='PRIMARY KEY CLUSTERED
  150.         (
  151.             ['+@colname+'] ASC
  152.             ))
  153.             GO'
  154.          end
  155.          else
  156.          begin
  157.             set @sql2=@sql2+''
  158.          end

  159.          If @default <>'' and @colname not in('ETL_CRC','QA_RULE_CHK_FLG','QA_MANUAL_FLG','CREATE_BY','UPDATE_BY')
  160.              begin
  161.              set @sql4=@sql4+'
  162.              ALTER TABLE [dbo].['+@tbl+'] ADD DEFAULT '+@default+' FOR ['+@colname+']
  163.              GO'

  164.              end
  165.          else
  166.              begin
  167.              set @sql4=@sql4+''
  168.              end




  169.     --用游标去取下一条记录 -
  170.        fetch next from mycursor into @tblname,@tbcomments,@colno,@colname,@IsIdt,@ispk,@type,@length,@decim,@isnull,@default,@comments
  171.     end
  172.     --关闭游标
  173.     close mycursor        
  174.     --撤销游标
  175.     DEALLOCATE mycursor

  176.      print @sql
  177.      print @sql2
  178.      print @sql4
  179.      --声明一个游标mycursor,select语句中参数的个数必须要和从游标取出的变量名相同
  180.  
  181.     
  182.     --打开游标
  183.     set @idx=0
  184.     open mycursor2
  185.     --从游标里取出数据赋值到我们刚才声明的2个变量中
  186.     fetch next from mycursor2 into @tblname,@idx,@colname2,@col_id,@col_num,@idx_type_desc,@is_unique

  187.     --判断游标的状态
  188.     -- 0 fetch语句成功
  189.     ---1 fetch语句失败或此行不在结果集中
  190.     ---2 被提取的行不存在
  191.     while (@@fetch_status=0)
  192.     begin
  193.     --显示出我们每次用游标取出的值

  194.      if @idx<>isnull(@idxp,'')
  195.          begin
  196.          set @sql3='ALTER TABLE [dbo].['+@tblname+'] ADD CONSTRAINT ['+@idx+'] '+(case when @is_unique=1 then'UNIQUE 'else '' end) +@idx_type_desc+'
  197.                 ( ['+@colname2+'] ASC'
  198.          end
  199.      else
  200.          begin
  201.          set @sql3=@sql3+'
  202.                  ['+@colname2+'] ASC'
  203.          end

  204.          if @col_id< @col_num
  205.             begin
  206.             set @sql3=@sql3+' ,'
  207.             end
  208.          else
  209.             begin
  210.             set @sql3=@sql3+')'
  211.             print @sql3
  212.             end
  213.         


  214.     --用游标去取下一条记录 -
  215.          set @idxp=@idx
  216.        fetch next from mycursor2 into @tblname,@idx,@colname2,@col_id,@col_num,@idx_type_desc,@is_unique

  217.     end
  218.     --关闭游标
  219.     close mycursor2        
  220.     --撤销游标
  221.     DEALLOCATE mycursor2
  222.     if object_id('tempdb..#idxtmp') is not null
  223.     begin
  224.     --select * from #idxtmp
  225.         drop table #idxtmp
  226.     end
  227.     select * from #comtmp
  228.     drop table #comtmp
  229.     set nocount on
  230. GO


网站栏目:automaticgeneratecreatetablesql
URL分享:http://6mz.cn/article/pdicch.html

其他资讯