sqlserver操作数据库,DBA级别的一些SQL-优快云博客

本文链接：https://blog.youkuaiyun.com/pyzheng/article/details/84459711

本文提供了SQL Server中查询用户表、外键约束、主键名称及列详细信息的方法。通过几个实用的SQL语句，可以快速获取表结构的相关信息。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

获得所有dbo的用户表

Select * FROM dev_cpm.dbo.SysObjects Where XType='U' orDER BY Name

从左到右分别是：外键约束名，子表名，外键列名，父表名
[url]http://chenjianjx.iteye.com/blog/222267[/url]

select fk.name '外键约束名' , ftable.name '子表名', cn.name '外键列名', rtable.name '父表名' from sysforeignkeys 
  join sysobjects fk 
    on sysforeignkeys.constid = fk.id 
  join sysobjects ftable 
    on sysforeignkeys.fkeyid = ftable.id 
  join sysobjects rtable 
    on sysforeignkeys.rkeyid = rtable.id 
  join syscolumns cn 
    on sysforeignkeys.fkeyid = cn.id and sysforeignkeys.fkey = cn.colid

获得表的主键名称
[url]http://www.cnblogs.com/aspxphpjsprb/archive/2008/01/05/1026974.html[/url]

select   主键=a.name   
  FROM   syscolumns   a   
  join   sysobjects   b   
  on   a.id=b.id       and   b.xtype='U'   
  and     b.name<>'dtproperties'   
  where  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   
          )
       )
   )     
  and     b.name='表名'

sql server 查询列名、列数据类型、列长度
[url]http://bbs.youkuaiyun.com/topics/310217801[/url]

select
    o.name as tbname,
    c.name as columnname,
    t.name as typename,
    c.max_length
from sys.tables as o
    join sys.columns as c
on o.object_id=c.object_id
    join sys.types as t
on c.system_type_id=t.system_type_id