|
from syscolumns a, systypes b where object_name(a.id) = @ObjName
and a.usertype = b.usertype order by a.ColID
set nocount on
Select @Script = 'Create table ' + @ObjName + '('
Insert into #spscript values(@Script,0)
/* Get column information */
open Cursor_Column
fetch next from Cursor_Column into @ColName,@ColID,@UserType,@TypeName,@Length,@Prec,@Scale,
@Status,@cDefault,@Const_Key
Select @Script = '
while (@@FETCH_STATUS <> -1)
begin
if (@@FETCH_STATUS <> -2)
begin
Select @Script = @ColName + ' ' + @TypeName
if @UserType in (1,2,3,4)
Select @Script = @Script + '(' + Convert(char(3),@Length) + ') '
else if @UserType in (24)
Select @Script = @Script + '(' + Convert(char(3),@Prec) + ','
+ Convert(char(3),@Scale) + ') '
else
Select @Script = @Script + ' '
if ( @Status & 0x80 ) > 0
Select @Script = @Script + ' IDENTITY(1,1) '
if ( @Status & 0x08 ) > 0
Select @Script = @Script + ' NULL '
else
Select @Script = @Script + ' NOT NULL '
if @cDefault > 0
Select @Script = @Script + ' DEFAULT ' + @Const_Key
end
fetch next from Cursor_Column into @ColName,@ColID,@UserType,@TypeName,@Length,@Prec,@Scale,
@Status,@cDefault,@Const_Key
if @@FETCH_STATUS = 0
begin
Select @Script = @Script + ','
Insert into #spscript values(@Script,0)
end
else
begin
Insert into #spscript values(@Script,1)
Insert into #spscript values(')',0)
end
end
Close Cursor_Column
Deallocate Cursor_Column
/* Get index information */
Declare Cursor_Index INSENSITIVE CURSOR
for Select name,IndID,status,Segment from sysindexes where object_name(id)=@ObjName
and IndID > 0 and IndID<>255 order by IndID
Open Cursor_Index
Fetch Next from Cursor_Index into @ColName, @IndID, @IndStatus, @Segment
while (@@FETCH_STATUS <> -1)
begin
if @@FETCH_STATUS <> -2
begin
declare @i TinyInt
declare @thiskey varchar(50)
declare @IndDesc varchar(68) /* string to build up index desc in */
Select @i = 1
while (@i <= 16)
begin
select @thiskey = index_col(@ObjName, @IndID, @i)
if @thiskey is null
break
if @i = 1
select @Index_Key = index_col(@ObjName, @IndID, @i)
else
select @Index_Key = @Index_Key + ', ' + index_col(@ObjName, @IndID, @i)
select @i = @i + 1
end
if (@IndStatus & 0x02) > 0
Select @Script = 'Create unique '
else
Select @Script = 'Create '
if @IndID = 1
select @Script = @Script + ' clustered '
if (@IndStatus & 0x800) > 0
select @strPri_Key = ' PRIMARY KEY (' + @Index_Key + ')'
else
select @strPri_Key = '
if @IndID > 1
select @Script = @Script + ' nonclustered '
Select @Script = @Script + ' index ' + @ColName + ' ON '+ @ObjName
+ '(' + @Index_Key + ')'
Select @IndDesc = '
/*
** See if the index is ignore_dupkey (0x01).
*/
if @IndStatus & 0x01 = 0x01
Select @IndDesc = @IndDesc + ' IGNORE_DUP_KEY' + ','
/*
** See if the index is ignore_dup_row (0x04).
*/
if @IndStatus & 0x04 = 0x04
Select @IndDesc = @IndDesc + ' IGNORE_DUP_ROW' + ','
/*
** See if the index is allow_dup_row (0x40).
*/
if @IndStatus & 0x40 = 0x40
Select @IndDesc = @IndDesc + ' ALLOW_DUP_ROW' + ','
if @IndDesc <> '
begin
Select @IndDesc = SubString( @IndDesc, 1, DataLength(@IndDesc) - 1 )
Select @Script = @Script + ' WITH ' + @IndDesc
end
/*
** Add the location of the data.
*/
if @Segment <> 1
select @Script = @Script + ' ON ' + name
from syssegments
where segment = @Segment
end
if (@strPri_Key = ')
Insert into #spscript values(@Script,0)
else
update #spscript set Script = Script + @strPri_Key where LastLine = 1
Fetch Next from Cursor_Index into @ColName, @IndID, @IndStatus, @Segment
end
Close Cursor_Index
Deallocate Cursor_Index
Select Script from #spscript order by id
set nocount off
return (0)
上一页 [1] [2]
[Sql Server]MS SQLSERVER 中如何得到表的创建语句
|
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 
