某数据库中各表有哪些字段

 

詳細信息參照
SQL2005

SQL code

    
    

     
     
     
     go
     
     

     
     --
     
     2005實現數據庫表字段屬性統計(2000里的系統表sysproperties描述表不存在，2005里用sys.extended_properties視圖替代)
     
     

     
     select
     
      
    
     
     [
     
     表名
     
     ]
     
     =
     
     c.Name,
    
     
     [
     
     表说明
     
     ]
     
     =
     
     isnull
     
     (f.
     
     [
     
     value
     
     ]
     
     ,
     
     ''
     
     ),
    
     
     [
     
     列名
     
     ]
     
     =
     
     a.Name,
    
     
     [
     
     列序號
     
     ]
     
     =
     
     a.Column_id,
    
     
     [
     
     標識
     
     ]
     
     =
     
     case
     
      
     
     when
     
      is_identity
     
     =
     
     1
     
      
     
     then
     
      
     
     '
     
     √
     
     '
     
      
     
     else
     
      
     
     ''
     
      
     
     end
     
     ,
    
     
     [
     
     主鍵
     
     ]
     
     =
     
     case
     
      
     
     when
     
      
     
     exists
     
     (
     
     select
     
      
     
     1
     
      
     
     from
     
      sys.objects x 
     
     join
     
      sys.indexes y 
     
     on
     
      x.Type
     
     =
     
     N
     
     '
     
     PK
     
     '
     
      
     
     and
     
      x.Name
     
     =
     
     y.Name 
                        
     
     join
     
      sysindexkeys z 
     
     on
     
      z.ID
     
     =
     
     a.
     
     Object_id
     
      
     
     and
     
      z.indid
     
     =
     
     y.index_id 
     
     and
     
      z.Colid
     
     =
     
     a.Column_id)
                    
     
     then
     
      
     
     '
     
     √
     
     '
     
      
     
     else
     
      
     
     ''
     
      
     
     end
     
     ,
    
     
     [
     
     類型
     
     ]
     
     =
     
     b.Name,
    
     
     [
     
     字節數
     
     ]
     
     =
     
     case
     
      
     
     when
     
      a.
     
     [
     
     max_length
     
     ]
     
     =-
     
     1
     
      
     
     and
     
      b.Name
     
     !=
     
     '
     
     xml
     
     '
     
      
     
     then
     
      
     
     '
     
     max/2G
     
     '
     
      
            
     
     when
     
      b.Name
     
     =
     
     '
     
     xml
     
     '
     
      
     
     then
     
      
     
     '
     
     2^31-1字節/2G
     
     '
     
     
            
     
     else
     
      
     
     rtrim
     
     (a.
     
     [
     
     max_length
     
     ]
     
     ) 
     
     end
     
     ,
    
     
     [
     
     長度
     
     ]
     
     =
     
     case
     
      
     
     when
     
      
     
     ColumnProperty
     
     (a.
     
     object_id
     
     ,a.Name,
     
     '
     
     Precision
     
     '
     
     )
     
     =-
     
     1
     
      
     
     then
     
      
     
     '
     
     2^31-1
     
     '
     
     
                
     
     else
     
      
     
     rtrim
     
     (
     
     ColumnProperty
     
     (a.
     
     object_id
     
     ,a.Name,
     
     '
     
     Precision
     
     '
     
     )) 
     
     end
     
     ,
    
     
     [
     
     小數
     
     ]
     
     =
     
     isnull
     
     (
     
     ColumnProperty
     
     (a.
     
     object_id
     
     ,a.Name,
     
     '
     
     Scale
     
     '
     
     ),
     
     0
     
     ),
    
     
     [
     
     是否為空
     
     ]
     
     =
     
     case
     
      
     
     when
     
      a.is_nullable
     
     =
     
     1
     
      
     
     then
     
      
     
     '
     
     √
     
     '
     
      
     
     else
     
      
     
     ''
     
      
     
     end
     
     ,
    
     
     [
     
     列说明
     
     ]
     
     =
     
     isnull
     
     (e.
     
     [
     
     value
     
     ]
     
     ,
     
     ''
     
     ),
    
     
     [
     
     默認值
     
     ]
     
     =
     
     isnull
     
     (d.
     
     text
     
     ,
     
     ''
     
     )    

     
     from
     
      
    sys.columns a

     
     left
     
      
     
     join
     
     
    sys.types b 
     
     on
     
      a.user_type_id
     
     =
     
     b.user_type_id

     
     inner
     
      
     
     join
     
     
    sys.objects c 
     
     on
     
      a.
     
     object_id
     
     =
     
     c.
     
     object_id
     
      
     
     and
     
      c.Type
     
     =
     
     '
     
     U
     
     '
     
     

     
     left
     
      
     
     join
     
     
    syscomments d 
     
     on
     
      a.default_object_id
     
     =
     
     d.ID

     
     left
     
      
     
     join
     
     
    sys.extended_properties e 
     
     on
     
      e.major_id
     
     =
     
     c.
     
     object_id
     
      
     
     and
     
      e.minor_id
     
     =
     
     a.Column_id 
     
     and
     
      e.class
     
     =
     
     1
     
      

     
     left
     
      
     
     join
     
     
    sys.extended_properties f 
     
     on
     
      f.major_id
     
     =
     
     c.
     
     object_id
     
      
     
     and
     
      f.minor_id
     
     =
     
     0
     
      
     
     and
     
      f.class
     
     =
     
     1
     
     

    
    

SQL2000:

SQL code

    
    

     
     
     
     ELECT 
    表名       
     
     =
     
      
     
     case
     
      
     
     when
     
      a.colorder
     
     =
     
     1
     
      
     
     then
     
      d.name 
     
     else
     
      
     
     ''
     
      
     
     end
     
     ,
    表说明     
     
     =
     
      
     
     case
     
      
     
     when
     
      a.colorder
     
     =
     
     1
     
      
     
     then
     
      
     
     isnull
     
     (f.value,
     
     ''
     
     ) 
     
     else
     
      
     
     ''
     
      
     
     end
     
     ,
    字段序号   
     
     =
     
      a.colorder,
    字段名     
     
     =
     
      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
     
      parent_obj
     
     =
     
     a.id 
     
     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.xusertype
     
     =
     
     b.xusertype

     
     inner
     
      
     
     join
     
      
    sysobjects d 

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

     
     left
     
      
     
     join
     
      
    syscomments e 

     
     on
     
      
    a.cdefault
     
     =
     
     e.id

     
     left
     
      
     
     join
     
      
    sysproperties g 

     
     on
     
      
    a.id
     
     =
     
     g.id 
     
     and
     
      a.colid
     
     =
     
     g.smallid  

     
     left
     
      
     
     join
     
      
    sysproperties f 

     
     on
     
      
    d.id
     
     =
     
     f.id 
     
     and
     
      f.smallid
     
     =
     
     0
     
     

     
     where
     
      
    d.name
     
     =
     
     '
     
     要查询的表
     
     '
     
         
     
     --
     
     如果只查询指定表,加上此条件
     
     

     
     order
     
      
     
     by
     
      
    a.id,a.colorder