生成建表语句

 

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 smallint
declare @Prec smallint
declare @Scale smallint
declare @Status smallint
declare @cDefault int
declare @DefaultID smallint
declare @Const_Key varchar(255)
declare @IndID SmallInt 
declare @IndStatus Int
declare @Index_Key varchar(255)
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 c.Text 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
  from sysindexes
  where object_name(id)=@ObjName
    and IndID>0
    and IndID<>255
  order by IndID   /*增加了对InDid为255的判断*/
Open Cursor_Index
Fetch Next from Cursor_Index into @ColName,@IndID,@IndStatus
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' + ',' */ /* 2000 不在支持*/
    /*
 **  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.
    */
      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
  end
Close Cursor_Index
Deallocate Cursor_Index

Select Script
  from #spscript

set nocount off

return (0)

 

SP_GET_TABLE_INFO 'student'