SqlServer 查询表、表说明、关联表、字段说明，语句汇总

--查询所有的表 SELECT * FROM SYSOBJECTS WHERE TYPE = ‘ U ‘

 ----根据表名查询所有的字段名及其注释   
SELECT A.NAME,B.VALUE    
FROM SYSCOLUMNS A 
LEFT  JOIN SYS.EXTENDED_PROPERTIES B ON A.ID=B.MAJOR_ID AND A.COLID=B.MINOR_ID 
INNER JOIN SYSOBJECTS    C ON A.ID=C.ID AND UPPER(C.NAME)=‘tb_name‘

 

 --  ----根据表名查询该表所有的关联表 
 SELECT  主键列ID =  b.rkey 
    ,主键列名  = ( SELECT  name  FROM  syscolumns  WHERE  colid = b.rkey  AND  id =  b.rkeyid) 
    ,外键表ID  =  b.fkeyid 
    ,外键表名称  =  object_name  (b.fkeyid) 
    ,外键列ID  =  b.fkey 
    ,外键列名  = ( SELECT  name  FROM  syscolumns  WHERE  colid = b.fkey  AND  id =  b.fkeyid) 
    ,级联更新  =  ObjectProperty (a.id, ‘  CnstIsUpdateCascade  ‘  ) 
    ,级联删除  =  ObjectProperty (a.id, ‘  CnstIsDeleteCascade  ‘  ) 
  FROM   sysobjects a 
      join  sysforeignkeys b  on  a.id =  b.constid 
      join  sysobjects c  on  a.parent_obj =  c.id 
  where  a.xtype =  ‘  f  ‘   AND  c.xtype =  ‘  U  ‘  
     and   object_name (b.rkeyid) =  ‘tb_name  ‘       --  --主表表名 
    

 ----查询关联表的表说明、主键列名、外表表名称、外键列名   
  SELECT
     表说明     =  isnull(d.value,object_name(b.fkeyid)) 
    ,主键列名=(SELECT name FROM syscolumns WHERE colid=b.rkey AND id=b.rkeyid)  
    ,外键表名称=object_name(b.fkeyid) 
    ,外键列名=(SELECT name FROM syscolumns WHERE colid=b.fkey AND id=b.fkeyid) 
FROM sysobjects a 
    join sysforeignkeys b on a.id=b.constid 
    join sysobjects c on a.parent_obj=c.id 
    left join sys.extended_properties d on d.minor_id = 0 and c.id = d.major_id
where a.xtype=‘f‘ AND c.xtype=‘U‘ 
    and object_name(b.rkeyid)=‘tb_name‘     ----主表表名 

　　

 --  ----最全的表信息查询 
    SELECT   
    表名         =   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   
sys.extended_properties   g 
  on   
    a.id  = G.major_id  and  a.colid =  g.minor_id  
  left   join   

sys.extended_properties f
   on   
    d.id  = f.major_id  and  f.minor_id =  0 
  where   
    d.name  =  ‘tb_name  ‘ 
  order   by   
    a.id,a.colorder 

 

　

SqlServer 查询表、表说明、关联表、字段说明，语句汇总

标签：

查看更多关于SqlServer 查询表、表说明、关联表、字段说明，语句汇总的详细内容...