automatic generate create table sql

点击(此处)折叠或打开

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
93.      declare mycursor2 cursor for     
94.                     select a.[tb_name],a.[idx_name],a.[col_name],a.[col_id],b.[col_num],a.[idx_type_desc],a.[is_unique]
95.                      from #idxtmp a
96.                     left join (select [tb_name], [idx_name],count(1) col_num from #idxtmp group by [tb_name], [idx_name]) b
97.                     on a.tb_name=b.tb_name
98.                     and a.idx_name=b.[idx_name]
100.     –打开游标
101.     open mycursor
102.     –从游标里取出数据赋值到我们刚才声明的2个变量中
103.     fetch next from mycursor into @tblname,@tbcomments,@colno,@colname,@IsIdt,@ispk,@type,@length,@decim,@isnull,@default,@comments
105.     –判断游标的状态
106.     — 0 fetch语句成功
107.     —1 fetch语句失败或此行不在结果集中
108.     —2 被提取的行不存在
109.     while (@@fetch_status=0)
110.     begin
111.     –显示出我们每次用游标取出的值
112.        –print ‘游标成功取出一条数据’
113.      if @colno=1
114.      begin
115.      set @tbl=@tblname
116.         set @sql=‘CREATE TABLE [dbo].[‘+@tblname+‘](
     
117.          [‘+@colname+‘] [‘+@type+‘] ‘+(case @isnull when 0 then ‘NOT NULL,’ else ‘NULL,’end)
118.         set @sql6=‘EXEC sys.sp_addextendedproperty @name=N’+””+‘MS_Description’+”‘,@value=N’+””+@tbcomments+”‘,@level0type=N’+”‘SCHEMA’+”‘,@level0name=N’+”‘dbo’
119.             +”‘, @level1type=N’+”‘TABLE’+”‘,@level1name=N’+””+@tbl+””
120.         insert into #comtmp([sql]) values (@sql6)
121.         — print @sql6
122.         set @sql5=‘EXEC sys.sp_addextendedproperty @name=N’+”‘MS_Description’+”‘, @value=N’+””+@comments+”‘,@level0type=N’+””+‘SCHEMA’+”‘,@level0name=N’
123.          +”‘dbo’+”‘,@level1type=N’+””+‘TABLE’+””+‘,@level1name=N’+””+@tbl+”‘, @level2type=N’+””+‘COLUMN’+”‘,’+‘@level2name=N’+””+@colname+””
124.          insert into #comtmp([sql]) values (@sql5)
125.         — print @sql5
128.      end
129.      else
130.      begin
131.          set @tbl=@tbl+”
132.          –去掉ETL_CRC QA_RULE_CHK_FLG QA_MANUAL_FLG CREATE_BY UPDATE_BY 这五个字段
133.          if @colname in(‘ETL_CRC’,‘QA_RULE_CHK_FLG’,‘QA_MANUAL_FLG’,‘CREATE_BY’,‘UPDATE_BY’)
134.              begin
135.              set @sql=@sql+”
136.              end
137.          else
138.              begin
139.              set @sql=@sql+‘
     
140.              ‘+‘[‘+@colname+‘] [‘+(case @type
141.                                         when ‘timestamp’ then ‘bigint’+‘]’
142.                                         when ‘varchar’ then @type +‘]’ +‘(‘+cast(@length as varchar(10))+‘)’
143.                                         when ‘nvarchar’ then @type +‘]’ +‘(‘+cast(@length as varchar(10))+‘)’
144.                                         when ‘char’ then @type +‘]’ +‘(‘+cast(@length as varchar(10))+‘)’
145.                                         when ‘decimal’ then @type +‘]’+‘(‘+cast(@length as varchar(10))+‘,’+cast(@decim as varchar(3))+‘)’
146.                                         else @type+‘]’end )+
147.              (case @isnull when 0 then ‘ NOT NULL,’ else ‘ NULL,’end)
148.              set @sql5=‘EXEC sys.sp_addextendedproperty @name=N’+”‘MS_Description’+”‘, @value=N’+””+@comments+”‘,@level0type=N’+””+‘SCHEMA’+”‘,@level0name=N’
149.                 +”‘dbo’+”‘,@level1type=N’+””+‘TABLE’+””+‘,@level1name=N’+””+@tbl+”‘, @level2type=N’+””+‘COLUMN’+”‘,’+‘@level2name=N’+””+@colname+””
150.             — print @sql5
151.              insert into #comtmp([sql]) values (@sql5)
152.              end
153.          end
155.          If @ispk=1
156.          begin
157.             set @sql2=‘PRIMARY KEY CLUSTERED
     
158.         (
159.             [‘+@colname+‘] ASC
     
160.             ))
161.             GO’
162.          end
163.          else
164.          begin
165.             set @sql2=@sql2+”
166.          end
168.          If @default <>” and @colname not in(‘ETL_CRC’,‘QA_RULE_CHK_FLG’,‘QA_MANUAL_FLG’,‘CREATE_BY’,‘UPDATE_BY’)
169.              begin
170.              set @sql4=@sql4+‘
     
171.              ALTER TABLE [dbo].[‘+@tbl+‘] ADD DEFAULT ‘+@default+‘ FOR [‘+@colname+‘]
     
172.              GO’
174.              end
175.          else
176.              begin
177.              set @sql4=@sql4+”
178.              end
183.     –用游标去取下一条记录 –
184.        fetch next from mycursor into @tblname,@tbcomments,@colno,@colname,@IsIdt,@ispk,@type,@length,@decim,@isnull,@default,@comments
185.     end
186.     –关闭游标
187.     close mycursor        
188.     –撤销游标
189.     DEALLOCATE mycursor
191.      print @sql
192.      print @sql2
193.      print @sql4
194.      –声明一个游标mycursor，select语句中参数的个数必须要和从游标取出的变量名相同
195.  
196.     
197.     –打开游标
198.     set @idx=0
199.     open mycursor2
200.     –从游标里取出数据赋值到我们刚才声明的2个变量中
201.     fetch next from mycursor2 into @tblname,@idx,@colname2,@col_id,@col_num,@idx_type_desc,@is_unique
203.     –判断游标的状态
204.     — 0 fetch语句成功
205.     —1 fetch语句失败或此行不在结果集中
206.     —2 被提取的行不存在
207.     while (@@fetch_status=0)
208.     begin
209.     –显示出我们每次用游标取出的值
211.      if @idx<>isnull(@idxp,”)
212.          begin
213.          set @sql3=‘ALTER TABLE [dbo].[‘+@tblname+‘] ADD CONSTRAINT [‘+@idx+‘] ‘+(case when @is_unique=1 then‘UNIQUE ‘else ” end) +@idx_type_desc+‘
     
214.                 ( [‘+@colname2+‘] ASC’
215.          end
216.      else
217.          begin
218.          set @sql3=@sql3+‘
     
219.                  [‘+@colname2+‘] ASC’
220.          end
222.          if @col_id< @col_num
223.             begin
224.             set @sql3=@sql3+‘ ,’
225.             end
226.          else
227.             begin
228.             set @sql3=@sql3+‘)’
229.             print @sql3
230.             end
231.         
234.     –用游标去取下一条记录 –
235.          set @idxp=@idx
236.        fetch next from mycursor2 into @tblname,@idx,@colname2,@col_id,@col_num,@idx_type_desc,@is_unique
238.     end
239.     –关闭游标
240.     close mycursor2        
241.     –撤销游标
242.     DEALLOCATE mycursor2
243.     if object_id(‘tempdb..#idxtmp’) is not null
244.     begin
245.     –select * from #idxtmp
246.         drop table #idxtmp
247.     end
248.     select * from #comtmp
249.     drop table #comtmp
250.     set nocount on
251. GO