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

核心提示： SQLSERVER6.5下的代码create procedure SP_GET_TABLE_INFO@ObjName varchar(128) /**//* The table to generate sql script */asdeclare @Script varchar(255)d

SQLSERVER6.5下的代码

create procedure SP_GET_TABLE_INFO
@ObjName varchar(128)　　　 /**//* The table to generate sql script */
as
declare @Script varchar(255)
declare @ColName varchar(30)
declare @ColID　 TinyInt
declare @UserType smallint
declare @TypeName sysname
declare @Length　 TinyInt
declare @Prec　　 TinyInt
declare @Scale　　TinyInt
declare @Status　 TinyInt
declare @cDefault int
declare @DefaultID TinyInt
declare @Const_Key varchar(255)
declare @IndID　　 SmallInt
declare @IndStatus SmallInt
declare @Index_Key varchar(255)
declare @Segment　 SmallInt
declare @DBName　　varchar(30)
declare @strPri_Key varchar (255)
/**//*
**　Check to see the the table exists and initialize @objid.
*/
if not Exists(Select name from sysobjects where name = @ObjName)
begin
　select @DBName = db_name()
　　raiserror(15009,-1,-1,@ObjName,@DBName)
　　return (1)
end
create table #spscript
(
　　id　　 int IDENTITY not null,
　　Script Varchar(255) NOT NULL,
　　LastLine tinyint
)
declare Cursor_Column INSENSITIVE CURSOR
　for Select a.name,a.ColID,a.usertype,b.name,a.length,a.prec,a.scale,a.Status, a.cDefault,
　　　　case a.cdefault when 0 then ' ' else (select case c.text when "(' ')" then "('')" else c.text end
　　　　from syscomments c where a.cdefault = c.id) end const_key
　　　　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)