转:导出表结构脚本

转:http://blog.csdn.net/vivianfdlpw/archive/2005/10/17/506598.aspx

 

/**/ /*
    --导出表结构脚本
    --用法:
    --exec sp_ExportTables 'tb'

    --vivianfdlpw 2005.10 引用请保留此信息
     
*/
IF   EXISTS ( SELECT   1   FROM  SYSOBJECTS  WHERE  ID = OBJECT_ID ( ' sp_ExportTables ' )  AND  XTYPE = ' p ' )
DROP   PROCEDURE  sp_ExportTables
GO

CREATE   PROCEDURE  sp_ExportTables 
@table_name   varchar ( 32 )
as  
begin

Create   Table  #CreateStatements (uid  int   identity ( 1 , 1 ),Info  text )

DECLARE   @table_id   int ,
  @CurrColumn   int ,
  @MaxColumn   int ,
  @CreateStatement   varchar ( 8000 ),
  @ColumnTypeName    varchar ( 255 ),
  @uid   int ,
  @i   int ,
  @primary_key_field   varchar ( 50 )

  select   @table_id = id  from  sysobjects  where  xtype = ' U '   and   [ name ]   <>   ' dtproperties '   and   [ name ]   =   @table_name
 
  select   @primary_key_field   =   convert ( varchar ( 32 ),c.name)
   from
   sysindexes i, syscolumns c, sysobjects o, syscolumns c1
   where
   o.id  =   @table_id
    and  o.id  =  c.id
    and  o.id  =  i.id
    and  (i.status  &   0x800 )  =   0x800
    and  c.name  =   index_col  ( @table_name , i.indid, c1.colid)
    and  c1.colid  <=  i.keycnt
    and  c1.id  =   @table_id
  Select   @CreateStatement   =   CHAR ( 13 )  +   ' CREATE TABLE [ '   +   [ name ]   +   ' ] (  '   from  SYSOBJECTS  WHERE  ID = @TABLE_ID
  -- 循环列
  Select   @CurrColumn = Min (colid), @MaxColumn   =   Max (colid)  from  syscolumns  where  id =   @table_id
  -- Select * from syscolumns where id=1511676433

  while   @currColumn   <=   @MaxColumn
   begin
  
   -- print @currColumn
   Declare   @UQIndex   int ,  @DefaultValue   nvarchar ( 4000 )
   set   @DefaultValue   =   null
   select   @DefaultValue = text   from  syscomments  where  id =
   ( select  constid  from  sysconstraints  where  id = @table_id   and  colid = @currColumn )

    -- 处理不同的列类型
    SELECT   @CreateStatement   =   @CreateStatement   +   CHAR ( 13 )  +   ' [ '   +   [ name ]   +   ' ]  '   +  type_name(xtype)  +  
     case     
      -- ie numeric(10,2)
      WHEN   type_name(xtype)  IN  ( ' decimal ' , ' numeric ' )  THEN  
       '  ( ' +   convert ( varchar ,prec)  +   ' , '   +   convert ( varchar ,length)  +   ' ) '  
       +   case   when  autoval  is   null   then   ''   else   '  IDENTITY(1,1) '   end  
       +   CASE   when  isnullable = 0   THEN    '  NOT NULL '   ELSE   '  NULL '   END  
      -- ie float(53)  
      WHEN   type_name(xtype)  IN  ( ' float ' , ' real ' )  THEN  
       '  ( ' +   convert ( varchar ,prec)  +   ' ) '  
       +   case   when  autoval  is   null   then   ''   else   '  IDENTITY(1,1) '   end  
       +   CASE   when  isnullable = 0   THEN    '  NOT NULL '   ELSE   '  NULL '   END  
      -- ie varchar(40)
      WHEN   type_name(xtype)  IN  ( ' char ' , ' varchar ' , ' nchar ' , ' nvarchar ' )  THEN
       '  ( ' +   convert ( varchar ,length)  +   ' ) '   
       +   case   when  autoval  is   null   then   ''   else   '  IDENTITY(1,1) '   end
       +   CASE   when  isnullable = 0   THEN    '  NOT NULL '   ELSE   '  NULL '   END  
      -- ie int
      ELSE
       +   case   when  autoval  is   null   then   ''   else   '  IDENTITY(1,1) '   end
       +   CASE   when  isnullable = 0   THEN    '  NOT NULL '   ELSE   '  NULL '   END  
     end
    -- 检测 'PRIMARY KEY'
    +   CASE   when  syscolumns.name  =   @primary_key_field   THEN   '  PRIMARY KEY '   else   ''   END
    +   CASE   when   @DefaultValue   is   null   then   ''  
    ELSE  
     CASE
     WHEN   type_name(xtype)  IN  ( ' decimal ' , ' numeric ' , ' float ' , ' real ' , ' bigint ' , ' int ' , ' smallint ' , ' tinyint ' , ' money ' , ' smallmoney ' )  THEN
      '  DEFAULT  '   +   convert ( varchar , @DefaultValue ) 
     ELSE
      '  DEFAULT  '   +   convert ( varchar , @DefaultValue )
     END
    END  
     +   ' , '   from  syscolumns  where  id = @table_id   and  colid = @CurrColumn

   Select   @CurrColumn   =   @CurrColumn   +   1
   end
   insert   into  #CreateStatements(Info)  values ( @CreateStatement )
   SELECT   @CreateStatement = ''
   select   @uid = @@IDENTITY

   -- 添加外键关系代码
   declare   @cursorID   int
   declare  c1  cursor   for   SELECT  fkeyid  from  sysforeignkeys  where  fkeyid = @table_id
    open  c1
    fetch   next   from  c1  into   @cursorID
   
    SELECT   @CreateStatement = @CreateStatement   +  
   ( select   +   CHAR ( 13 )  + ' FOREIGN KEY ( '   +     [ syscolumns ] . [ name ]   +   ' ) REFERENCES  '   from  syscolumns  where  id = fkeyid  and  colid  = fkey)  +  
   ( select  ( SELECT   distinct   [ sysobjects ] . [ name ]   from  sysobjects  where  id = rkeyid)  +   ' ( '   +   [ syscolumns ] . [ name ]   +   ' ), '   from  syscolumns  where  id = rkeyid  and  colid  = rkey)
     from  sysforeignkeys  where  fkeyid = @table_id
    
    close  c1
    deallocate  c1
   -- 添加UNIQUE约束代码
   declare  c1  cursor   for   select  id  from  sysobjects  where  xtype = ' UQ '   and  parent_obj = @table_id
   open  c1
   fetch   next   from  c1  into   @cursorID
   
    while   @@fetch_status   >=   0
    begin
     declare   @indid   smallint
     SELECT   @indid   =  indid, @CreateStatement = @CreateStatement   +   CHAR ( 13 )  +   ' CONSTRAINT  '   +   object_name ( @cursorID )  +   '  UNIQUE  '  
      +   case   when  (status  &   16 ) = 16   then   '  CLUSTERED '   else   '  NONCLUSTERED '   end
     from  sysindexes
     where  name  =   object_name ( @cursorID )  and  id  =   @table_ID
      declare   @thiskey   nvarchar ( 131 ),  --  128+3
       @keys    nvarchar ( 2126 )  --  a specific size for MS for whatever reason
  
      select   @keys   =   index_col ( @table_name ,  @indid ,  1 ),  @i   =   2
      if  (indexkey_property( @table_id ,  @indid ,  1 ,  ' isdescending ' )  =   1 )
       select   @keys   =   @keys    +   ' (-) '
  
      select   @thiskey   =   index_col ( @table_name ,  @indid ,  @i )
      if  (( @thiskey   is   not   null )  and  (indexkey_property( @table_ID ,  @indid ,  @i ,  ' isdescending ' )  =   1 ))
       select   @thiskey   =   @thiskey   +   ' (-) '
  
      while  ( @thiskey   is   not   null )
      begin
       select   @keys   =   @keys   +   ' ,  '   +   @thiskey ,  @i   =   @i   +   1
       select   @thiskey   =   index_col ( @table_name ,  @indid ,  @i )
       if  (( @thiskey   is   not   null )  and  (indexkey_property( @table_ID ,  @indid ,  @i ,  ' isdescending ' )  =   1 ))
        select   @thiskey   =   @thiskey   +   ' (-) '
      end
      Select   @CreateStatement = @CreateStatement   +   ' ( '   +   @keys   +   ' ), '
    fetch   next   from  c1  into   @cursorID
    end
   close  c1
   deallocate  c1
    -- 添加check约束代码

    -- 添加索引代码

  
   DECLARE   @ptrval   binary ( 16 ), @txtlen   INT
   if   len ( @CreateStatement )  >   0
   BEGIN
    SELECT   @ptrval   =   TEXTPTR (info) ,
    @txtlen   =   DATALENGTH (info)
       FROM  #CreateStatements
          WHERE  uid = @uid
    UPDATETEXT  #CreateStatements.info  @ptrval   @txtlen   0   @CreateStatement
   END
  
  
   SELECT   @ptrval   =   TEXTPTR (info) ,
   @txtlen   =   DATALENGTH (info)  -   1
      FROM  #CreateStatements
         WHERE  uid = @uid
  
   SELECT   @CreateStatement =   ' ) ' +   CHAR ( 13 )     
   UPDATETEXT  #CreateStatements.info  @ptrval   @txtlen   1   @CreateStatement

Select  info  from  #CreateStatements
drop   table  #CreateStatements
end