SQL语句获取数据库中的表主键，自增列，所有列

  
  
  

   
   
   获取表主键
  
  
  
  
  
  

   
   
   1
   
   
   :
   
   
   SELECT
   
   
    TABLE_NAME,COLUMN_NAME 
   
   
   FROM
   
   
    INFORMATION_SCHEMA.KEY_COLUMN_USAGEWHERE TABLE_NAME
   
   
   <>
   
   
   '
   
   
   dtproperties
   
   
   '
   
   
   

   
   
   2
   
   
   :
   
   
   EXEC
   
   
    sp_pkeys 
   
   
   @table_name
   
   
   =
   
   
   '
   
   
   表名
   
   
   '
   
   
   

   
   
   3
   
   
   :
  
  
  
  
  
  

   
   
   
   
   
   select
   
   
    o.name 
   
   
   as
   
   
    表名,c.name 
   
   
   as
   
   
    字段名,k.colid 
   
   
   as
   
   
    字段序号,k.keyno 
   
   
   as
   
   
    索引顺序,t.name 
   
   
   as
   
   
    类型 
  
  
  
  
  
  

   
   
   
   
   
   from
   
   
    sysindexes i 
  
  
  
  
  
  

   
   
   
   
   
   join
   
   
    sysindexkeys k 
   
   
   on
   
   
    i.id 
   
   
   =
   
   
    k.id 
   
   
   and
   
   
    i.indid 
   
   
   =
   
   
    k.indid 
  
  
  
  
  
  

   
   
   join sysobjects o 
   
   
   on
   
   
    i.id 
   
   
   =
   
   
    o.id 
  
  
  
  
  
  

   
   
   join syscolumns c 
   
   
   on
   
   
    i.id
   
   
   =
   
   
   c.id 
   
   
   and
   
   
    k.colid 
   
   
   =
   
   
    c.colid 
  
  
  
  
  
  

   
   
   join systypes t 
   
   
   on
   
   
    c.xusertype
   
   
   =
   
   
   t.xusertype 
  
  
  
  
  
  

   
   
   where o.xtype 
   
   
   =
   
   
    
   
   
   '
   
   
   U
   
   
   '
   
   
    
   
   
   and
   
   
    o.name
   
   
   =
   
   
   '
   
   
   要查询的表名
   
   
   ' 
  
  
  
  
  
  

   
   
   
   
   
   and
   
   
    
   
   
   exists
   
   
   (
   
   
   select
   
   
    
   
   
   1
   
   
    
   
   
   from
   
   
    sysobjects 
   
   
   where
   
   
    xtype 
   
   
   =
   
   
    
   
   
   '
   
   
   PK
   
   
   '
   
   
    
   
   
   and
   
   
    parent_obj
   
   
   =
   
   
   i.id 
   
   
   and
   
   
    name 
   
   
   =
   
   
    i.name) 
  
  
  
  
  
  

   
   
   
   
   
   order
   
   
    
   
   
   by
   
   
    o.name,k.colid 
  
  
  

  
  
  

   
   
   获取所有
  
  
  
  
  
  

   
   
   
   
   
   SELECT
   
   
    
 表名
   
   
   =
   
   
   case
   
   
    
   
   
   when
   
   
    a.colorder
   
   
   =
   
   
   1
   
   
    
   
   
   then
   
   
    d.name 
   
   
   else
   
   
    
   
   
   ''
   
   
    
   
   
   end
   
   
   , 
 字段名
   
   
   =
   
   
   a.name, 
 标识
   
   
   =
   
   
   case
   
   
    
   
   
   when
   
   
    
   
   
   COLUMNPROPERTY
   
   
   (a.id,a.name,
   
   
   '
   
   
   IsIdentity
   
   
   '
   
   
   )
   
   
   =
   
   
   1
   
   
    
   
   
   then
   
   
    
   
   
   '
   
   
   √
   
   
   '
   
   
    
   
   
   else
   
   
    
   
   
   ''
   
   
    
   
   
   end
   
   
   , 
 主键
   
   
   =
   
   
   case
   
   
    
   
   
   when
   
   
    
   
   
   exists
   
   
   (
   
   
   SELECT
   
   
    
   
   
   1
   
   
    
   
   
   FROM
   
   
    sysobjects 
   
   
   where
   
   
    xtype
   
   
   =
   
   
    
   
   
   '
   
   
   PK
   
   
   '
   
   
    
   
   
   and
   
   
    name 
   
   
   in
   
   
    ( 

   
   
   SELECT
   
   
    name 
   
   
   FROM
   
   
    sysindexes 
   
   
   WHERE
   
   
    indid 
   
   
   in
   
   
   ( 

   
   
   SELECT
   
   
    indid 
   
   
   FROM
   
   
    sysindexkeys 
   
   
   WHERE
   
   
    id 
   
   
   =
   
   
    a.id 
   
   
   AND
   
   
    colid
   
   
   =
   
   
   a.colid 
))) 
   
   
   then
   
   
    
   
   
   '
   
   
   √
   
   
   '
   
   
    
   
   
   else
   
   
    
   
   
   ''
   
   
    
   
   
   end
   
   
   , 
 类型
   
   
   =
   
   
   b.name, 
 占用字节数
   
   
   =
   
   
   a.length, 
 长度
   
   
   =
   
   
   COLUMNPROPERTY
   
   
   (a.id,a.name, 
   
   
   '
   
   
   PRECISION
   
   
   '
   
   
   ), 
 小数位数
   
   
   =
   
   
   isnull
   
   
   (
   
   
   COLUMNPROPERTY
   
   
   (a.id,a.name, 
   
   
   '
   
   
   Scale
   
   
   '
   
   
   ),
   
   
   0
   
   
   ), 
 允许空
   
   
   =
   
   
   case
   
   
    
   
   
   when
   
   
    a.isnullable
   
   
   =
   
   
   1
   
   
    
   
   
   then
   
   
    
   
   
   '
   
   
   √
   
   
   '
   
   
   else
   
   
    
   
   
   ''
   
   
    
   
   
   end
   
   
   , 
 默认值
   
   
   =
   
   
   isnull
   
   
   (e.
   
   
   text
   
   
   , 
   
   
   ''
   
   
   ), 
 字段说明
   
   
   =
   
   
   isnull
   
   
   (g.
   
   
   [
   
   
   value
   
   
   ]
   
   
   , 
   
   
   ''
   
   
   ) 

   
   
   FROM
   
   
    syscolumns a 

   
   
   left
   
   
    
   
   
   join
   
   
    systypes b 
   
   
   on
   
   
    a.xtype
   
   
   =
   
   
   b.xusertype 

   
   
   inner
   
   
    
   
   
   join
   
   
    sysobjects d 
   
   
   on
   
   
    a.id
   
   
   =
   
   
   d.id 
   
   
   and
   
   
    d.xtype
   
   
   =
   
   
    
   
   
   '
   
   
   U
   
   
   '
   
   
    
   
   
   and
   
   
    d.name 
   
   
   <>
   
   
    
   
   
   '
   
   
   dtproperties
   
   
   '
   
   
    
   
   
   and
   
   
    d.name 
   
   
   =
   
   
    
   
   
   '
   
   
   要查询的表名
   
   
   '
   
   
   

   
   
   left
   
   
    
   
   
   join
   
   
    syscomments e 
   
   
   on
   
   
    a.cdefault
   
   
   =
   
   
   e.id 

   
   
   left
   
   
    
   
   
   join
   
   
    sys.extended_properties g 
   
   
   on
   
   
    a.id
   
   
   =
   
   
   g.major_id 
   
   
   and
   
   
    a.colid
   
   
   =
   
   
   g.minor_id 
   
   
   and
   
   
    g.name
   
   
   =
   
   
   '
   
   
   MS_Description
   
   
   '
   
   
    

   
   
   order
   
   
    
   
   
   by
   
   
    a.id,a.colorder 
  
  
  

获取表自增列名称 
SELECT COLUMN_NAME as 标识 FROM INFORMATION_SCHEMA.columns 
  
  
  

   
   
   
   
   
   WHERE
   
   
    TABLE_NAME
   
   
   =
   
   
   '
   
   
   表名
   
   
   '
   
   
    
   
   
   AND
   
   
    
   
   
   COLUMNPROPERTY
   
   
   (
   
   
   OBJECT_ID
   
   
   (
   
   
   '
   
   
   表名
   
   
   '
   
   
   ),COLUMN_NAME,
   
   
   '
   
   
   IsIdentity
   
   
   '
   
   
   )
   
   
   =
   
   
   1 
  
  
  
  
  
  

   
   
   
    
    
    

     
     
     SELECT
     
     
      标识 
     
     
     =
     
     
      
     
     
     case
     
     
      
     
     
     when
     
     
      
     
     
     COLUMNPROPERTY
     
     
     ( a.id,a.name,
     
     
     '
     
     
     IsIdentity
     
     
     '
     
     
     )
     
     
     =
     
     
     1
     
     
      
     
     
     then
     
     
      a.name 
     
     
     else
     
     
      
     
     
     '
     
     
     NULL
     
     
     '
     
     
      
     
     
     end
     
     
      

     
     
     FROM
     
     
      syscolumns a 

     
     
     inner
     
     
      
     
     
     join
     
     
      sysobjects d 
     
     
     on
     
     
      a.id
     
     
     =
     
     
     d.id 
     
     
     and
     
     
      d.xtype
     
     
     =
     
     
     '
     
     
     U
     
     
     '
     
     
      
     
     
     and
     
     
      d.name
     
     
     <>
     
     
     '
     
     
     dtproperties
     
     
     '
     
     
      

     
     
     where
     
     
      d.name
     
     
     =
     
     
     '
     
     
     TestType
     
     
     '
     
     
      
     
     
     order
     
     
      
     
     
     by
     
     
      a.id,a.colorder
    
    
    
  
  
  

获取字段默认值
  
  
  

   
   
   select
   
   
    b.
   
   
   text
   
   
    
   
   
   as
   
   
    字段默认值 

   
   
   from
   
   
    syscolumns a 
   
   
   left
   
   
    
   
   
   join
   
   
    syscomments b 
   
   
   on
   
   
    a.cdefault 
   
   
   =
   
   
    b.id 

   
   
   where
   
   
    a.id 
   
   
   =
   
   
    
   
   
   object_id
   
   
   (
   
   
   '
   
   
   表名
   
   
   '
   
   
   ) 
   
   
   and
   
   
    a.name 
   
   
   =
   
   
    
   
   
   '
   
   
   字段名称
   
   
   '
  
  
  

移植表： SELECT * INTO 库2.dbo.表2 FROM 库1.dbo.表1