Select Script from #spscript

set nocount off

return (0)

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, Da

上一页  [1] [2] [3]  下一页