MS SQLSERVER 只能得到存储过程的创建语句,方法如下:
sp_helptext procedureName
但是往往我们需要得到表的创建语句,比如说在数据库升级的时候判断某个表是否已经改变,或者已经有一个表存在,但不知道它的创建语句是什么,字段有没有约束,有没有主键,创建了哪些索引等等.下面我给出一个存储过程,供读者参考.
该存储过程可以得到你想得到的所有的表的创建语句,包括和表有关的索引的创建语句.
SQLSERVER2000 下的代码
1
create procedure SP_GET_TABLE_INFO
2
@ObjName varchar(128) /* The table to generate sql script */
3as
4
5declare @Script varchar(255)
6declare @ColName varchar(30)
7declare @ColID TinyInt
8declare @UserType smallint
9declare @TypeName sysname
10declare @Length TinyInt
11declare @Prec TinyInt
12declare @Scale TinyInt
13declare @Status TinyInt
14declare @cDefault int
15declare @DefaultID TinyInt
16declare @Const_Key varchar(255)
17declare @IndID SmallInt
18declare @IndStatus Int
19declare @Index_Key varchar(255)
20declare @DBName varchar(30)
21declare @strPri_Key varchar (255)
22
23/*
24
** Check to see the the table exists and initialize @objid.
25
*/
26if not Exists(Select name from sysobjects where name = @ObjName)
27begin
28 select @DBName = db_name()
29 raiserror(15009,-1,-1,@ObjName,@DBName)
30 return (1)
31end
32
33create table #spscript
34(
35 id int IDENTITY not null,
36 Script Varchar(255) NOT NULL,
37 LastLine tinyint
38)
39
40declare 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
46set nocount on
47Select @Script = 'Create table ' + @ObjName + '('
48Insert into #spscript values(@Script,0)
49
50/* Get column information */
51open Cursor_Column
52
53fetch next from Cursor_Column into @ColName,@ColID,@UserType,@TypeName,@Length,@Prec,@Scale,
54 @Status,@cDefault,@Const_Key
55
56Select @Script = ''
57while (@@FETCH_STATUS <> -1)
58begin
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
91end
92Close Cursor_Column
93Deallocate Cursor_Column
94
95/* Get index information */
96Declare 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的判断*/
99Open Cursor_Index
100Fetch Next from Cursor_Index into @ColName, @IndID, @IndStatus
101while (@@FETCH_STATUS <> -1)
102begin
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
171end
172Close Cursor_Index
173Deallocate Cursor_Index
174
175Select Script from #spscript
176
177set nocount off
178
179return (0)
180
181
182SQLSERVER6.5下的代码
183
184create procedure SP_GET_TABLE_INFO
185@ObjName varchar(128) /* The table to generate sql script */
186as
187
188declare @Script varchar(255)
189declare @ColName varchar(30)
190declare @ColID TinyInt
191declare @UserType smallint
192declare @TypeName sysname
193declare @Length TinyInt
194declare @Prec TinyInt
195declare @Scale TinyInt
196declare @Status TinyInt
197declare @cDefault int
198declare @DefaultID TinyInt
199declare @Const_Key varchar(255)
200declare @IndID SmallInt
201declare @IndStatus SmallInt
202declare @Index_Key varchar(255)
203declare @Segment SmallInt
204declare @DBName varchar(30)
205declare @strPri_Key varchar (255)
206
207/*
208** Check to see the the table exists and initialize @objid.
209*/
210if not Exists(Select name from sysobjects where name = @ObjName)
211begin
212 select @DBName = db_name()
213 raiserror(15009,-1,-1,@ObjName,@DBName)
214 return (1)
215end
216
217create table #spscript
218(
219 id int IDENTITY not null,
220 Script Varchar(255) NOT NULL,
221 LastLine tinyint
222)
223
224declare 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
231set nocount on
232Select @Script = 'Create table ' + @ObjName + '('
233Insert into #spscript values(@Script,0)
234
235/* Get column information */
236open Cursor_Column
237
238fetch next from Cursor_Column into @ColName,@ColID,@UserType,@TypeName,@Length,@Prec,@Scale,
239 @Status,@cDefault,@Const_Key
240
241Select @Script = ''
242while (@@FETCH_STATUS <> -1)
243begin
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
276end
277Close Cursor_Column
278Deallocate Cursor_Column
279
280/* Get index information */
281Declare 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
284Open Cursor_Index
285Fetch Next from Cursor_Index into @ColName, @IndID, @IndStatus, @Segment
286while (@@FETCH_STATUS <> -1)
287begin
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
360end
361Close Cursor_Index
362Deallocate Cursor_Index
363
364Select Script from #spscript order by id
365
366set nocount off
367
368return (0)
369
370
371
create procedure SP_GET_TABLE_INFO2
@ObjName varchar(128) /* The table to generate sql script */
3
as4
5
declare @Script varchar(255)6
declare @ColName varchar(30)7
declare @ColID TinyInt8
declare @UserType smallint9
declare @TypeName sysname10
declare @Length TinyInt11
declare @Prec TinyInt12
declare @Scale TinyInt13
declare @Status TinyInt14
declare @cDefault int15
declare @DefaultID TinyInt16
declare @Const_Key varchar(255)17
declare @IndID SmallInt 18
declare @IndStatus Int19
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
begin28
select @DBName = db_name()29
raiserror(15009,-1,-1,@ObjName,@DBName)30
return (1)31
end32
33
create table #spscript34
(35
id int IDENTITY not null,36
Script Varchar(255) NOT NULL,37
LastLine tinyint 38
)39
40
declare Cursor_Column INSENSITIVE CURSOR41
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_key43
from syscolumns a, systypes b where object_name(a.id) = @ObjName44
and a.usertype = b.usertype order by a.ColID45
46
set nocount on47
Select @Script = 'Create table ' + @ObjName + '('48
Insert into #spscript values(@Script,0)49
50
/* Get column information */51
open Cursor_Column52
53
fetch next from Cursor_Column into @ColName,@ColID,@UserType,@TypeName,@Length,@Prec,@Scale,54
@Status,@cDefault,@Const_Key55
56
Select @Script = '' 57
while (@@FETCH_STATUS <> -1)58
begin59
if (@@FETCH_STATUS <> -2)60
begin61
Select @Script = @ColName + ' ' + @TypeName62
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
else68
Select @Script = @Script + ' '69
if ( @Status & 0x80 ) > 070
Select @Script = @Script + ' IDENTITY(1,1) '71
72
if ( @Status & 0x08 ) > 073
Select @Script = @Script + ' NULL '74
else75
Select @Script = @Script + ' NOT NULL '76
if @cDefault > 077
Select @Script = @Script + ' DEFAULT ' + @Const_Key78
end79
fetch next from Cursor_Column into @ColName,@ColID,@UserType,@TypeName,@Length,@Prec,@Scale,80
@Status,@cDefault,@Const_Key81
if @@FETCH_STATUS = 082
begin83
Select @Script = @Script + ',' 84
Insert into #spscript values(@Script,0)85
end86
else87
begin88
Insert into #spscript values(@Script,1)89
Insert into #spscript values(')',0)90
end91
end92
Close Cursor_Column93
Deallocate Cursor_Column94
95
/* Get index information */96
Declare Cursor_Index INSENSITIVE CURSOR97
for Select name,IndID,status from sysindexes where object_name(id)=@ObjName98
and IndID > 0 and IndID<>255 order by IndID /*增加了对InDid为255的判断*/99
Open Cursor_Index100
Fetch Next from Cursor_Index into @ColName, @IndID, @IndStatus101
while (@@FETCH_STATUS <> -1)102
begin103
if @@FETCH_STATUS <> -2104
begin105
106
declare @i TinyInt107
declare @thiskey varchar(50)108
declare @IndDesc varchar(68) /* string to build up index desc in */109
110
Select @i = 1111
while (@i <= 16)112
begin113
select @thiskey = index_col(@ObjName, @IndID, @i)114
if @thiskey is null115
break116
117
if @i = 1118
select @Index_Key = index_col(@ObjName, @IndID, @i)119
else120
select @Index_Key = @Index_Key + ', ' + index_col(@ObjName, @IndID, @i)121
select @i = @i + 1122
end123
if (@IndStatus & 0x02) > 0124
Select @Script = 'Create unique '125
else126
Select @Script = 'Create '127
if @IndID = 1128
select @Script = @Script + ' clustered '129
130
131
if (@IndStatus & 0x800) > 0132
select @strPri_Key = ' PRIMARY KEY (' + @Index_Key + ')'133
else134
select @strPri_Key = ''135
136
if @IndID > 1137
select @Script = @Script + ' nonclustered '138
Select @Script = @Script + ' index ' + @ColName + ' ON '+ @ObjName139
+ '(' + @Index_Key + ')'140
Select @IndDesc = '' 141
/*142
** See if the index is ignore_dupkey (0x01).143
*/144
if @IndStatus & 0x01 = 0x01145
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 = 0x40155
Select @IndDesc = @IndDesc + ' ALLOW_DUP_ROW' + ','156
if @IndDesc <> ''157
begin158
Select @IndDesc = SubString( @IndDesc, 1, DataLength(@IndDesc) - 1 )159
Select @Script = @Script + ' WITH ' + @IndDesc160
end161
/*162
** Add the location of the data.163
*/164
end165
if (@strPri_Key = '')166
Insert into #spscript values(@Script,0)167
else 168
update #spscript set Script = Script + @strPri_Key where LastLine = 1169
170
Fetch Next from Cursor_Index into @ColName, @IndID, @IndStatus171
end172
Close Cursor_Index173
Deallocate Cursor_Index174
175
Select Script from #spscript176
177
set nocount off178
179
return (0)180
181
182
SQLSERVER6.5下的代码183
184
create procedure SP_GET_TABLE_INFO185
@ObjName varchar(128) /* The table to generate sql script */186
as187
188
declare @Script varchar(255)189
declare @ColName varchar(30)190
declare @ColID TinyInt191
declare @UserType smallint192
declare @TypeName sysname193
declare @Length TinyInt194
declare @Prec TinyInt195
declare @Scale TinyInt196
declare @Status TinyInt197
declare @cDefault int198
declare @DefaultID TinyInt199
declare @Const_Key varchar(255)200
declare @IndID SmallInt201
declare @IndStatus SmallInt202
declare @Index_Key varchar(255)203
declare @Segment SmallInt204
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
begin212
select @DBName = db_name()213
raiserror(15009,-1,-1,@ObjName,@DBName)214
return (1)215
end216
217
create table #spscript218
(219
id int IDENTITY not null,220
Script Varchar(255) NOT NULL,221
LastLine tinyint 222
)223
224
declare Cursor_Column INSENSITIVE CURSOR225
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_key228
from syscolumns a, systypes b where object_name(a.id) = @ObjName229
and a.usertype = b.usertype order by a.ColID230
231
set nocount on232
Select @Script = 'Create table ' + @ObjName + '('233
Insert into #spscript values(@Script,0)234
235
/* Get column information */236
open Cursor_Column237
238
fetch next from Cursor_Column into @ColName,@ColID,@UserType,@TypeName,@Length,@Prec,@Scale,239
@Status,@cDefault,@Const_Key240
241
Select @Script = '' 242
while (@@FETCH_STATUS <> -1)243
begin244
if (@@FETCH_STATUS <> -2)245
begin246
Select @Script = @ColName + ' ' + @TypeName247
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
else253
Select @Script = @Script + ' '254
if ( @Status & 0x80 ) > 0255
Select @Script = @Script + ' IDENTITY(1,1) '256
257
if ( @Status & 0x08 ) > 0258
Select @Script = @Script + ' NULL '259
else260
Select @Script = @Script + ' NOT NULL '261
if @cDefault > 0262
Select @Script = @Script + ' DEFAULT ' + @Const_Key263
end264
fetch next from Cursor_Column into @ColName,@ColID,@UserType,@TypeName,@Length,@Prec,@Scale,265
@Status,@cDefault,@Const_Key266
if @@FETCH_STATUS = 0267
begin268
Select @Script = @Script + ',' 269
Insert into #spscript values(@Script,0)270
end271
else272
begin273
Insert into #spscript values(@Script,1)274
Insert into #spscript values(')',0)275
end276
end277
Close Cursor_Column278
Deallocate Cursor_Column279
280
/* Get index information */281
Declare Cursor_Index INSENSITIVE CURSOR282
for Select name,IndID,status,Segment from sysindexes where object_name(id)=@ObjName283
and IndID > 0 and IndID<>255 order by IndID284
Open Cursor_Index285
Fetch Next from Cursor_Index into @ColName, @IndID, @IndStatus, @Segment286
while (@@FETCH_STATUS <> -1)287
begin288
if @@FETCH_STATUS <> -2289
begin290
291
declare @i TinyInt292
declare @thiskey varchar(50)293
declare @IndDesc varchar(68) /* string to build up index desc in */294
295
Select @i = 1296
while (@i <= 16)297
begin298
select @thiskey = index_col(@ObjName, @IndID, @i)299
if @thiskey is null300
break301
302
if @i = 1303
select @Index_Key = index_col(@ObjName, @IndID, @i)304
else305
select @Index_Key = @Index_Key + ', ' + index_col(@ObjName, @IndID, @i)306
select @i = @i + 1307
end308
if (@IndStatus & 0x02) > 0309
Select @Script = 'Create unique '310
else311
Select @Script = 'Create '312
if @IndID = 1313
select @Script = @Script + ' clustered '314
315
316
if (@IndStatus & 0x800) > 0317
select @strPri_Key = ' PRIMARY KEY (' + @Index_Key + ')'318
else319
select @strPri_Key = ''320
321
if @IndID > 1322
select @Script = @Script + ' nonclustered '323
Select @Script = @Script + ' index ' + @ColName + ' ON '+ @ObjName324
+ '(' + @Index_Key + ')'325
Select @IndDesc = '' 326
/*327
** See if the index is ignore_dupkey (0x01).328
*/329
if @IndStatus & 0x01 = 0x01330
Select @IndDesc = @IndDesc + ' IGNORE_DUP_KEY' + ','331
/*332
** See if the index is ignore_dup_row (0x04).333
*/334
if @IndStatus & 0x04 = 0x04335
Select @IndDesc = @IndDesc + ' IGNORE_DUP_ROW' + ','336
/*337
** See if the index is allow_dup_row (0x40).338
*/339
if @IndStatus & 0x40 = 0x40340
Select @IndDesc = @IndDesc + ' ALLOW_DUP_ROW' + ','341
if @IndDesc <> ''342
begin343
Select @IndDesc = SubString( @IndDesc, 1, DataLength(@IndDesc) - 1 )344
Select @Script = @Script + ' WITH ' + @IndDesc345
end346
/*347
** Add the location of the data.348
*/349
if @Segment <> 1350
select @Script = @Script + ' ON ' + name351
from syssegments352
where segment = @Segment353
end354
if (@strPri_Key = '')355
Insert into #spscript values(@Script,0)356
else 357
update #spscript set Script = Script + @strPri_Key where LastLine = 1358
359
Fetch Next from Cursor_Index into @ColName, @IndID, @IndStatus, @Segment360
end361
Close Cursor_Index362
Deallocate Cursor_Index363
364
Select Script from #spscript order by id365
366
set nocount off367
368
return (0)369
370
371
