>데이터 베이스 >MySQL 튜토리얼 >查询SQLServer2005中某个数据库中的表结构、索引、视图、存储过

查询SQLServer2005中某个数据库中的表结构、索引、视图、存储过

WBOY
WBOY원래의
2016-06-07 15:52:011020검색

原地址:http://www.360doc.com/content/10/0926/15/3393452_56532327.shtml (1)查询SQLServer2005中某个数据库中的表结构 SELECT 表名 = CASE WHEN A . COLORDER = 1 THEN D . NAME ELSE '' END , 表說明 = CASE WHEN A . COLORDER = 1 THEN ISNULL ( F .

原地址:http://www.360doc.com/content/10/0926/15/3393452_56532327.shtml


(1)查询SQLServer2005中某个数据库中的表结构

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 FROMSYSOBJECTS WHERE XTYPE='PK' AND PARENT_OBJ=A.ID AND NAME IN (

SELECT NAME FROM SYSINDEXES WHEREINDID 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

ORDER BY

A.ID,A.COLORDER

 

PS:如若有的数据库中不存在dbo.sysproperties表,那么只需要将与之相关的注释掉即可。

最简单的查询用户表的SQL如下:

 

select * from sys.tables

 

(2)查询SQLServer2005中某个数据库中的索引

 

SELECT TOP 100 PERCENT --a.id,

       CASE

            WHENb.keyno = 1 THEN c.name

            ELSE''

       END               AS表名,

       CASE

            WHENb.keyno = 1 THEN a.name

            ELSE''

       END               AS索引名称,

       d.name           AS 列名,

       b.keyno           AS 索引顺序,

       CASE INDEXKEY_PROPERTY(c.id, b.indid, b.keyno, 'isdescending')

            WHEN1 THEN '降序'

            WHEN0 THEN '升序'

       END               AS排序,

       CASE

            WHENp.id IS NULL THEN ''

            ELSE'√'

       END               AS主键,

       CASE INDEXPROPERTY(c.id, a.name, 'IsClustered')

            WHEN1 THEN '√'

            WHEN0 THEN ''

       END               AS聚集,

       CASE INDEXPROPERTY(c.id, a.name, 'IsUnique')

            WHEN1 THEN '√'

            WHEN0 THEN ''

       END               AS唯一,

       CASE

            WHENe.id IS NULL THEN ''

            ELSE'√'

       END               AS唯一约束,

       a.OrigFillFactor  AS 填充因子,

       c.crdate          AS 创建时间,

       c.refdate         AS 更改时间

FROM   dbo.sysindexes a

       INNER JOIN dbo.sysindexkeysb

            ON  a.id = b.id

            ANDa.indid = b.indid

       INNER JOIN dbo.syscolumns d

            ON  b.id = d.id

            ANDb.colid = d.colid

       INNER JOIN dbo.sysobjects c

            ON  a.id = c.id

            ANDc.xtype = 'U'

       LEFT OUTER JOIN dbo.sysobjects e

            ON  e.name = a.name

            ANDe.xtype = 'UQ'

       LEFT OUTER JOIN dbo.sysobjects p

            ON  p.name = a.name

            ANDp.xtype = 'PK'

WHERE  (OBJECTPROPERTY(a.id, N'IsUserTable') = 1)

       AND (OBJECTPROPERTY(a.id, N'IsMSShipped') = 0)

       AND (INDEXPROPERTY(a.id, a.name, 'IsAutoStatistics') = 0)

ORDER BY

       c.name,

       a.name,

      b.keyno

 

(3)查询SQLServer2005中某个数据库中的主键、外键、约束、视图、存储过程、触发器以及自定义函数

 

SELECT DISTINCT

TOP 100 PERCENT o.xtype,

CASE o.xtype

     WHEN'X'  THEN '扩展存储过程'

     WHEN'TR' THEN '触发器'

     WHEN'PK' THEN '主键'

     WHEN'F'  THEN '外键'

     WHEN'C'  THEN '约束'

     WHEN'V'  THEN '视图'

     WHEN'FN' THEN '函数-标量'

     WHEN'IF' THEN '函数-内嵌'

     WHEN'TF' THEN '函数-表值'

     ELSE'存储过程'

END AS 类型,

o.name AS 对象名,

o.crdateAS 创建时间,

o.refdateAS 更改时间,

c.text AS 声明语句

FROM dbo.sysobjects o

LEFT OUTER JOIN

dbo.syscomments c ON o.id = c.id

WHERE (o.xtype IN ('X', 'TR', 'C', 'V', 'F', 'IF', 'TF', 'FN', 'P', 'PK')) AND

(OBJECTPROPERTY(o.id, N'IsMSShipped') = 0)

ORDER BY

CASE o.xtype

WHEN 'X'   THEN '扩展存储过程'

WHEN 'TR'  THEN '触发器'

WHEN 'PK'  THEN '主键'

WHEN 'F'   THEN '外键'

WHEN 'C'   THEN '约束'

WHEN 'V'   THEN '视图'

WHEN 'FN'  THEN '函数-标量'

WHEN 'IF'  THEN '函数-内嵌'

WHEN 'TF'  THEN '函数-表值'

ELSE '存储过程' END DESC

 

(4)查询SQLServer2005中某个数据库中的所有关系图

select * from dbo.sysdiagrams

 

(5)查询SQLServer2005中某个数据库中的所有对象

select * from sys.sysobjects

 

不同的对象用xtype来标识。

C = CHECK 约束

D = 默认值或 DEFAULT 约束

F = FOREIGN KEY 约束

L = 日志

FN = 标量函数

IF = 内嵌表函数

P = 存储过程

PK = PRIMARY KEY 约束(类型是 K

RF = 复制筛选存储过程

S = 系统表

TF = 表函数

TR = 触发器

U = 用户表

UQ = UNIQUE 约束(类型是 K

V = 视图

X = 扩展存储过程

这些信息可以通过联机丛书->索引->sysobjects->xtype来查看。
성명:
본 글의 내용은 네티즌들의 자발적인 기여로 작성되었으며, 저작권은 원저작자에게 있습니다. 본 사이트는 이에 상응하는 법적 책임을 지지 않습니다. 표절이나 침해가 의심되는 콘텐츠를 발견한 경우 admin@php.cn으로 문의하세요.