MS SQLSERVER 中如何得到表的创建语句

MS SQLSERVER 只能得到存储过程的创建语句,方法如下:

sp_helptext procedureName

但是往往我们需要得到表的创建语句,比如说在数据库升级的时候判断某个表是否已经改变,或者已经有一个表存在,但不知道它的创建语句是什么,字段有没有约束,有没有主键,创建了哪些索引等等.下面我给出一个存储过程,供读者参考.

该存储过程可以得到你想得到的所有的表的创建语句,包括和表有关的索引的创建语句.

SQLSERVER2000 下的代码

  1 create   procedure  SP_GET_TABLE_INFO
  2 @ObjName   varchar ( 128 )        /**/ /* The table to generate sql script */
  3 as
  4
  5 declare   @Script   varchar ( 255 )
  6 declare   @ColName   varchar ( 30 )
  7 declare   @ColID     TinyInt
  8 declare   @UserType   smallint
  9 declare   @TypeName  sysname
 10 declare   @Length     TinyInt
 11 declare   @Prec       TinyInt
 12 declare   @Scale      TinyInt
 13 declare   @Status     TinyInt
 14 declare   @cDefault   int
 15 declare   @DefaultID   TinyInt
 16 declare   @Const_Key   varchar ( 255 )
 17 declare   @IndID       SmallInt   
 18 declare   @IndStatus   Int
 19 declare   @Index_Key   varchar ( 255 )
 20 declare   @DBName      varchar ( 30 )
 21 declare   @strPri_Key   varchar  ( 255 )
 22
 23 /**/ /*
 24**  Check to see the the table exists and initialize @objid.
 25*/
 26 if   not   Exists ( Select  name  from  sysobjects  where  name  =   @ObjName )
 27 begin
 28    select   @DBName   =   db_name ()
 29      raiserror ( 15009 , - 1 , - 1 , @ObjName , @DBName )
 30      return  ( 1 )
 31 end
 32
 33 create   table  #spscript
 34 (
 35     id      int   IDENTITY   not   null ,
 36     Script  Varchar ( 255 )  NOT   NULL ,
 37     LastLine  tinyint  
 38 )
 39
 40 declare  Cursor_Column INSENSITIVE  CURSOR
 41    for   Select  a.name,a.ColID,a.usertype,b.name,a.length,a.prec,a.scale,a.Status, a.cDefault,
 42          case  a.cdefault  when   0   then   '   '   else  ( select  c. Text   from  syscomments c  where  a.cdefault  =  c.id)  end  const_key
 43          from  syscolumns a, systypes b  where   object_name (a.id)  =   @ObjName
 44          and  a.usertype  =  b.usertype  order   by  a.ColID
 45
 46 set  nocount  on
 47 Select   @Script   =   ' Create table  '   +   @ObjName   +   ' ( '
 48 Insert   into  #spscript  values ( @Script , 0 )
 49
 50 /**/ /* Get column information */
 51 open  Cursor_Column
 52
 53 fetch   next   from  Cursor_Column  into   @ColName , @ColID , @UserType , @TypeName , @Length , @Prec , @Scale ,
 54        @Status , @cDefault , @Const_Key
 55
 56 Select   @Script   =   ''  
 57 while  ( @@FETCH_STATUS   <>   - 1 )
 58 begin
 59    if  ( @@FETCH_STATUS   <>   - 2 )
 60    begin
 61      Select   @Script   =   @ColName   +   '   '   +   @TypeName
 62      if   @UserType   in  ( 1 , 2 , 3 , 4 )
 63        Select   @Script   =   @Script   +   ' ( '   +   Convert ( char ( 3 ), @Length )  +   ' )  '
 64      else   if   @UserType   in  ( 24 )
 65        Select   @Script   =   @Script   +   ' ( '   +   Convert ( char ( 3 ), @Prec )  +   ' , '
 66                        +   Convert ( char ( 3 ), @Scale )  +   ' )  '
 67      else
 68        Select   @Script   =   @Script   +   '   '
 69      if  (  @Status   &   0x80  )  >   0
 70        Select   @Script   =   @Script   +   '  IDENTITY(1,1)  '
 71
 72      if  (  @Status   &   0x08  )  >   0
 73        Select   @Script   =   @Script   +   '  NULL  '
 74      else
 75        Select   @Script   =   @Script   +   '  NOT NULL  '
 76      if   @cDefault   >   0
 77        Select   @Script   =   @Script   +   '  DEFAULT  '   +   @Const_Key
 78    end
 79    fetch   next   from  Cursor_Column  into   @ColName , @ColID , @UserType , @TypeName , @Length , @Prec , @Scale ,
 80        @Status , @cDefault , @Const_Key
 81    if   @@FETCH_STATUS   =   0
 82    begin
 83      Select   @Script   =   @Script   +   ' , '  
 84      Insert   into  #spscript  values ( @Script , 0 )
 85    end
 86    else
 87    begin
 88      Insert   into  #spscript  values ( @Script , 1 )
 89      Insert   into  #spscript  values ( ' ) ' , 0 )
 90    end
 91 end
 92 Close  Cursor_Column
 93 Deallocate  Cursor_Column
 94
 95 /**/ /* Get index information */
 96 Declare  Cursor_Index INSENSITIVE  CURSOR
 97    for   Select  name,IndID,status  from  sysindexes  where   object_name (id) = @ObjName
 98                and  IndID  >   0   and  IndID <> 255    order   by  IndID    /**/ /*增加了对InDid为255的判断*/
 99 Open  Cursor_Index
100 Fetch   Next   from  Cursor_Index  into   @ColName ,  @IndID ,  @IndStatus
101 while  ( @@FETCH_STATUS   <>   - 1 )
102 begin
103    if   @@FETCH_STATUS   <>   - 2
104    begin
105
106      declare   @i   TinyInt
107      declare   @thiskey   varchar ( 50 )
108      declare   @IndDesc   varchar ( 68 )  /**/ /* string to build up index desc in */
109
110      Select    @i   =   1
111      while  ( @i   <=   16 )
112      begin
113        select   @thiskey   =   index_col ( @ObjName ,  @IndID ,  @i )
114        if   @thiskey   is   null
115          break
116
117        if   @i   =   1
118          select   @Index_Key   =   index_col ( @ObjName ,  @IndID ,  @i )
119        else
120          select   @Index_Key   =   @Index_Key   +   ' ,  '   +   index_col ( @ObjName ,  @IndID ,  @i )
121        select   @i   =   @i   +   1
122      end
123      if  ( @IndStatus   &   0x02 )  >   0
124        Select   @Script   =   ' Create unique  '
125      else
126        Select   @Script   =   ' Create  '
127      if   @IndID   =   1
128        select   @Script   =   @Script   +   '  clustered  '
129
130
131      if  ( @IndStatus   &   0x800 )  >   0
132       select   @strPri_Key   =   '  PRIMARY KEY ( '   +   @Index_Key   +   ' ) '
133      else
134       select   @strPri_Key   =   ''
135      
136      if   @IndID   >   1
137        select   @Script   =   @Script   +   '  nonclustered  '
138      Select   @Script   =   @Script   +   '  index  '   +   @ColName   +   '  ON  ' +   @ObjName
139             +   ' ( '   +   @Index_Key   +   ' ) '
140      Select   @IndDesc   =   ''  
141      /**/ /*
142 **  See if the index is ignore_dupkey (0x01).
143    */
144      if   @IndStatus   &   0x01   =   0x01
145        Select   @IndDesc   =   @IndDesc   +   '  IGNORE_DUP_KEY '   +   ' , '
146      /**/ /*
147     **  See if the index is ignore_dup_row (0x04).
148    */
149     /**/ /* if @IndStatus & 0x04 = 0x04 */
150     /**/ /*   Select @IndDesc = @IndDesc + ' IGNORE_DUP_ROW' + ',' */   /**/ /* 2000 不在支持*/  
151      /**/ /*
152 **  See if the index is allow_dup_row (0x40).
153    */
154      if   @IndStatus   &   0x40   =   0x40
155        Select   @IndDesc   =   @IndDesc   +   '  ALLOW_DUP_ROW '   +   ' , '
156      if   @IndDesc   <>   ''
157      begin
158        Select   @IndDesc   =   SubString (  @IndDesc ,  1 ,  DataLength ( @IndDesc )  -   1  )
159        Select   @Script   =   @Script   +   '  WITH  '   +   @IndDesc
160      end
161      /**/ /*
162 **  Add the location of the data.
163    */
164    end
165    if  ( @strPri_Key   =   '' )
166      Insert   into  #spscript  values ( @Script , 0 )
167    else  
168      update  #spscript  set  Script  =  Script  +   @strPri_Key   where  LastLine  =   1
169   
170    Fetch   Next   from  Cursor_Index  into   @ColName ,  @IndID ,  @IndStatus
171 end
172 Close  Cursor_Index
173 Deallocate  Cursor_Index
174
175 Select  Script  from  #spscript
176
177 set  nocount  off
178
179 return  ( 0 )
180
181
182 SQLSERVER6.5下的代码
183
184 create   procedure  SP_GET_TABLE_INFO
185 @ObjName   varchar ( 128 )        /**/ /* The table to generate sql script */
186 as
187
188 declare   @Script   varchar ( 255 )
189 declare   @ColName   varchar ( 30 )
190 declare   @ColID     TinyInt
191 declare   @UserType   smallint
192 declare   @TypeName  sysname
193 declare   @Length     TinyInt
194 declare   @Prec       TinyInt
195 declare   @Scale      TinyInt
196 declare   @Status     TinyInt
197 declare   @cDefault   int
198 declare   @DefaultID   TinyInt
199 declare   @Const_Key   varchar ( 255 )
200 declare   @IndID       SmallInt
201 declare   @IndStatus   SmallInt
202 declare   @Index_Key   varchar ( 255 )
203 declare   @Segment     SmallInt
204 declare   @DBName      varchar ( 30 )
205 declare   @strPri_Key   varchar  ( 255 )
206
207 /**/ /*
208**  Check to see the the table exists and initialize @objid.
209*/
210 if   not   Exists ( Select  name  from  sysobjects  where  name  =   @ObjName )
211 begin
212    select   @DBName   =   db_name ()
213      raiserror ( 15009 , - 1 , - 1 , @ObjName , @DBName )
214      return  ( 1 )
215 end
216
217 create   table  #spscript
218 (
219     id      int   IDENTITY   not   null ,
220     Script  Varchar ( 255 )  NOT   NULL ,
221     LastLine  tinyint  
222 )
223
224 declare  Cursor_Column INSENSITIVE  CURSOR
225    for   Select  a.name,a.ColID,a.usertype,b.name,a.length,a.prec,a.scale,a.Status, a.cDefault,
226          case  a.cdefault  when   0   then   '   '   else  ( select   case  c. text   when  "( '   ' )"  then  "( '' )"  else  c. text   end  
227          from  syscomments c  where  a.cdefault  =  c.id)  end  const_key
228          from  syscolumns a, systypes b  where   object_name (a.id)  =   @ObjName
229          and  a.usertype  =  b.usertype  order   by  a.ColID
230
231 set  nocount  on
232 Select   @Script   =   ' Create table  '   +   @ObjName   +   ' ( '
233 Insert   into  #spscript  values ( @Script , 0 )
234
235 /**/ /* Get column information */
236 open  Cursor_Column
237
238 fetch   next   from  Cursor_Column  into   @ColName , @ColID , @UserType , @TypeName , @Length , @Prec , @Scale ,
239        @Status , @cDefault , @Const_Key
240
241 Select   @Script   =   ''  
242 while  ( @@FETCH_STATUS   <>   - 1 )
243 begin
244    if  ( @@FETCH_STATUS   <>   - 2 )
245    begin
246      Select   @Script   =   @ColName   +   '   '   +   @TypeName
247      if   @UserType   in  ( 1 , 2 , 3 , 4 )
248        Select   @Script   =   @Script   +   ' ( '   +   Convert ( char ( 3 ), @Length )  +   ' )  '
249      else   if   @UserType   in  ( 24 )
250        Select   @Script   =   @Script   +   ' ( '   +   Convert ( char ( 3 ), @Prec )  +   ' , '
251                        +   Convert ( char ( 3 ), @Scale )  +   ' )  '
252      else
253        Select   @Script   =   @Script   +   '   '
254      if  (  @Status   &   0x80  )  >   0
255        Select   @Script   =   @Script   +   '  IDENTITY(1,1)  '
256
257      if  (  @Status   &   0x08  )  >   0
258        Select   @Script   =   @Script   +   '  NULL  '
259      else
260        Select   @Script   =   @Script   +   '  NOT NULL  '
261      if   @cDefault   >   0
262        Select   @Script   =   @Script   +   '  DEFAULT  '   +   @Const_Key
263    end
264    fetch   next   from  Cursor_Column  into   @ColName , @ColID , @UserType , @TypeName , @Length , @Prec , @Scale ,
265        @Status , @cDefault , @Const_Key
266    if   @@FETCH_STATUS   =   0
267    begin
268      Select   @Script   =   @Script   +   ' , '  
269      Insert   into  #spscript  values ( @Script , 0 )
270    end
271    else
272    begin
273      Insert   into  #spscript  values ( @Script , 1 )
274      Insert   into  #spscript  values ( ' ) ' , 0 )
275    end
276 end
277 Close  Cursor_Column
278 Deallocate  Cursor_Column
279
280 /**/ /* Get index information */
281 Declare  Cursor_Index INSENSITIVE  CURSOR
282    for   Select  name,IndID,status,Segment  from  sysindexes  where   object_name (id) = @ObjName
283                and  IndID  >   0   and  IndID <> 255   order   by  IndID
284 Open  Cursor_Index
285 Fetch   Next   from  Cursor_Index  into   @ColName ,  @IndID ,  @IndStatus ,  @Segment
286 while  ( @@FETCH_STATUS   <>   - 1 )
287 begin
288    if   @@FETCH_STATUS   <>   - 2
289    begin
290
291      declare   @i   TinyInt
292      declare   @thiskey   varchar ( 50 )
293      declare   @IndDesc   varchar ( 68 )  /**/ /* string to build up index desc in */
294
295      Select    @i   =   1
296      while  ( @i   <=   16 )
297      begin
298        select   @thiskey   =   index_col ( @ObjName ,  @IndID ,  @i )
299        if   @thiskey   is   null
300          break
301
302        if   @i   =   1
303          select   @Index_Key   =   index_col ( @ObjName ,  @IndID ,  @i )
304        else
305          select   @Index_Key   =   @Index_Key   +   ' ,  '   +   index_col ( @ObjName ,  @IndID ,  @i )
306        select   @i   =   @i   +   1
307      end
308      if  ( @IndStatus   &   0x02 )  >   0
309        Select   @Script   =   ' Create unique  '
310      else
311        Select   @Script   =   ' Create  '
312      if   @IndID   =   1
313        select   @Script   =   @Script   +   '  clustered  '
314
315
316      if  ( @IndStatus   &   0x800 )  >   0
317       select   @strPri_Key   =   '  PRIMARY KEY ( '   +   @Index_Key   +   ' ) '
318      else
319       select   @strPri_Key   =   ''
320      
321      if   @IndID   >   1
322        select   @Script   =   @Script   +   '  nonclustered  '
323      Select   @Script   =   @Script   +   '  index  '   +   @ColName   +   '  ON  ' +   @ObjName
324             +   ' ( '   +   @Index_Key   +   ' ) '
325      Select   @IndDesc   =   ''  
326      /**/ /*
327 **  See if the index is ignore_dupkey (0x01).
328    */
329      if   @IndStatus   &   0x01   =   0x01
330        Select   @IndDesc   =   @IndDesc   +   '  IGNORE_DUP_KEY '   +   ' , '
331      /**/ /*
332     **  See if the index is ignore_dup_row (0x04).
333    */
334      if   @IndStatus   &   0x04   =   0x04
335        Select   @IndDesc   =   @IndDesc   +   '  IGNORE_DUP_ROW '   +   ' , '
336      /**/ /*
337 **  See if the index is allow_dup_row (0x40).
338    */
339      if   @IndStatus   &   0x40   =   0x40
340        Select   @IndDesc   =   @IndDesc   +   '  ALLOW_DUP_ROW '   +   ' , '
341      if   @IndDesc   <>   ''
342      begin
343        Select   @IndDesc   =   SubString (  @IndDesc ,  1 ,  DataLength ( @IndDesc )  -   1  )
344        Select   @Script   =   @Script   +   '  WITH  '   +   @IndDesc
345      end
346      /**/ /*
347 **  Add the location of the data.
348    */
349      if   @Segment   <>   1
350        select   @Script   =   @Script   +   '  ON  '   +  name
351    from  syssegments
352    where  segment  =   @Segment
353    end
354    if  ( @strPri_Key   =   '' )
355      Insert   into  #spscript  values ( @Script , 0 )
356    else  
357      update  #spscript  set  Script  =  Script  +   @strPri_Key   where  LastLine  =   1
358   
359    Fetch   Next   from  Cursor_Index  into   @ColName ,  @IndID ,  @IndStatus ,  @Segment
360 end
361 Close  Cursor_Index
362 Deallocate  Cursor_Index
363
364 Select  Script  from  #spscript  order   by  id
365
366 set  nocount  off
367
368 return  ( 0 )
369
370
371

转载于:https://www.cnblogs.com/rengy/archive/2005/10/12/253111.html