sqlserver操作数据库,DBA级别的一些SQL

最新推荐文章于 2023-12-18 10:56:15 发布

原创最新推荐文章于 2023-12-18 10:56:15 发布 · 182 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

Database 专栏收录该内容

104 篇文章

订阅专栏

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

获得所有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