SQL系统表的应用

SQL2000:51Testing软件测试网6f;mk:A.d@%];R7nVm&S
51Testing软件测试网V%W2M_$UEw{
----查看Table的栏位信息

SELECT    
;w Q0L
h xa@s0    表名=case when a.colorder=1 then d.name else '' end,51Testing软件测试网%UQ3sN*y!HmmB4E4T
    表说明=case when a.colorder=1 then isnull(f.value,'') else '' end,

AjcMK0~n0    字段序号=a.colorder,51Testing软件测试网~5I B{|:vR}
    字段名=a.name,  
b7K3t"~ S k0    标识=case when COLUMNPROPERTY(a.id,a.name,'IsIdentity')=1 then '√' else '' end,  
{7{0C.l1w%Y nJW0    主键=case when exists(SELECT 1 FROM sysobjects
%{jW4id$s%?'_8E0                          where  xtype='PK'51Testing软件测试网$`p? ob#v,K^
                                 and name in (SELECT name FROM sysindexes51Testing软件测试网#CH-TD%vi3\0h
                                              WHERE indid in(SELECT indid FROM sysindexkeys51Testing软件测试网(yFN#q)Z-H wh
                                                             WHERE id=a.id AND colid=a.colid)))  
8^1X[pQOO-k;gb0              then '√' else '' end,  51Testing软件测试网_)n[{ xTN9T+YJ%N"n
    类型=b.name,
F gUf}0elO0    占用字节数=a.length,  51Testing软件测试网3V)E Bk U?b K
    长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'),  51Testing软件测试网Q}5^/]N|"om
    小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),  51Testing软件测试网 SWm+^3b-w;{!X
    允许空=case when a.isnullable=1 then '√' else '' end,  51Testing软件测试网ck h-CI+f ZP2j c w xRo$j
    默认值=isnull(e.text,''),51Testing软件测试网*f)J;RM t~

    字段说明=isnull(g.[value],'')51Testing软件测试网3]6X4lU#Vr}6_x
FROM   syscolumns a  51Testing软件测试网%`!ybg^3V
left   join   systypes       b   on   a.xusertype=b.xusertype  
/I%ysE-~5LS0inner  join   sysobjects     d   on   a.id=d.id        and   d.xtype='U'       and d.name<>'dtproperties'  
*D)QZp"g)R0left   join   syscomments    e   on   a.cdefault=e.id  
.{3Y)WX5g(v}L,z+h0left   join   sysproperties  g   on   a.id=g.id        and   a.colid=g.smallid      
EUG^tl0left   join   sysproperties  f   on   d.id=f.id        and   f.smallid=0  51Testing软件测试网0pPzhvo t!T!U H:R0_
  --where   d.name='table_name'             --如果要查询特定table,加上这句  51Testing软件测试网E!A!p4Sc5^
order by a.id,a.colorder  

R'bY5J a&yhHK*wxy0--1：获取当前数据库中的所有用户表51Testing软件测试网i4Unc`.K

select Name from sysobjects where xtype='u' and status>=0

--2：获取某一个表的所有字段51Testing软件测试网/n&xLC;_r

/n9ZBYj4\0select name from syscolumns where id=object_id('表名')51Testing软件测试网u/Tj m+N
?q

--3：查看与某一个表相关的视图、存储过程、函数

select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%表名%'51Testing软件测试网c%^w@*g:c(wNH3V?!d

--4：查看当前数据库中所有存储过程51Testing软件测试网r8hk,CB#?-a

!JA'co2D0E`0select name as 存储过程名称 from sysobjects where xtype='P'51Testing软件测试网 m6Z%s hs1@
LT
H

8oN8_;y]J/m0--5：查询用户创建的所有数据库

Z?W dI)R0select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name='sa')51Testing软件测试网UH4CZC;t

)K Ywtz3V$X1`0select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x0151Testing软件测试网Aiz&@,X&}%QWQ

$}qCTM9x7[0--6：查询某一个表的字段和数据类型

select column_name,data_type from information_schema.columns
MeXm:x'b1Uk6J0where table_name = '表名'

--7:查询某张表的主键（参考SqlServer系统存储过程sp_pkeys）

select COLUMN_NAME = convert(sysname,c.name)              51Testing软件测试网K wVR;`u!_
from   sysindexes i, syscolumns c, sysobjects o                  
2S.F:x6bALv8q0where o.id = object_id('[YourTableName]')                 
^s$n.Au+xf3U[?0      and o.id = c.id                                           51Testing软件测试网y l^jCI;TJ&XW\
      and o.id = i.id                                           51Testing软件测试网,G:Yp3ctd9Z
      and (i.status & 0x800) = 0x800                            
F-VG0`.a]4?0      and (c.name = index_col ('[YourTableName]', i.indid,  1) or    51Testing软件测试网#ZWuh!~1w o f'z
           c.name = index_col ('[YourTableName]', i.indid,  2) or    51Testing软件测试网d5GqT^)q k:q!S
         c.name = index_col ('[YourTableName]', i.indid,  3) or    
Z0V&t7w8t.p0           c.name = index_col ('[YourTableName]', i.indid,  4) or    
e(C-P"KMQ\/e.U[0           c.name = index_col ('[YourTableName]', i.indid,  5) or    51Testing软件测试网
h)A%S ^1m)s@u
           c.name = index_col ('[YourTableName]', i.indid,  6) or    
OSu B Iij`w0           c.name = index_col ('[YourTableName]', i.indid,  7) or    51Testing软件测试网E~;GF/gaND4?
           c.name = index_col ('[YourTableName]', i.indid,  8) or    51Testing软件测试网ee@-H ?;n
           c.name = index_col ('[YourTableName]', i.indid,  9) or    
)k(ydI-R#G-tnH9F0           c.name = index_col ('[YourTableName]', i.indid, 10) or    
S)mh Z*^$IS0           c.name = index_col ('[YourTableName]', i.indid, 11) or    51Testing软件测试网I*[/|jO
           c.name = index_col ('[YourTableName]', i.indid, 12) or    
)em]Z)l f%b0           c.name = index_col ('[YourTableName]', i.indid, 13) or    
qJW*R {kXFr1p)L;A0           c.name = index_col ('[YourTableName]', i.indid, 14) or    
'nsWDZ9D*t0           c.name = index_col ('[YourTableName]', i.indid, 15) or    51Testing软件测试网/S^M4b
{
           c.name = index_col ('[YourTableName]', i.indid, 16))51Testing软件测试网J%CmE~Qh C

4MC GD)~-g5@"I$Y0--7:查询某张表的外键
:B{(]2X
RVt0select t1.name,t2.rtableName,t2.name51Testing软件测试网1HXaS5{*]Ww
from  (select col.name, f.constid as temp
-h@*?ab{R7H0       from syscolumns col,sysforeignkeys f51Testing软件测试网:z]5tb4?V+Rb
       where f.fkeyid=col.id
T:v$g6F#Ji0             and f.fkey=col.colid
4S&e6}MiU6WTk0             and f.constid in (select distinct(id) 51Testing软件测试网/sTzz8\&ss
                               from sysobjects51Testing软件测试网GH4y1ik,Tf-q
                               where OBJECT_NAME(parent_obj)='YourTableName'51Testing软件测试网-C:JUx
n?(elK
                                     and xtype='F')51Testing软件测试网9CK^.e.l"J!l
       )as t1 ,51Testing软件测试网"}(?yPER+o1N
      (select OBJECT_NAME(f.rkeyid) as rtableName,col.name, f.constid as temp51Testing软件测试网!n,@Q7b C+]{
       from syscolumns col,sysforeignkeys f
z@P$H P'\0^D'i&y0       where f.rkeyid=col.id
wG6O`7k6Jn0             and f.rkey=col.colid51Testing软件测试网\9Tm*E'b\
             and f.constid in (select distinct(id)51Testing软件测试网b|hgm4q
                               from sysobjects
)I0}JOedk V!J0                               where OBJECT_NAME(parent_obj)='YourTableName'
eM)~U!|iQ0                                     and xtype='F')
F nfG2F0J
{,~0        ) as t251Testing软件测试网cg%Oi|a`u
where t1.temp=t2.temp
1jLIEp?051Testing软件测试网8h N'X
O0v1C
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■51Testing软件测试网9K'`mg7i?1vX

`L
W.a$mx0SQL2005:
U x6Kz\
F X"_v0
:k-K)b[cCl,L,p.p0SELECT TOP 100 PERCENT --a.id,
;W!JO @T;N d7W0CASE WHEN a.colorder = 1 THEN d.name ELSE '' END AS 表名,51Testing软件测试网6Xxcr5_3]&UW$nWX
CASE WHEN a.colorder = 1 THEN isnull(f.value, '') ELSE '' END AS 表说明,
8k$k9a$AG1YH0a.colorder AS 字段序号,51Testing软件测试网i S3e0N.|C&_ H
a.name AS 字段名,51Testing软件测试网q z5g#Vsu
CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 THEN '√' ELSE '' END AS 标识,51Testing软件测试网MLA Ii.Uj l T
CASE WHEN EXISTS (SELECT 1 FROM dbo.sysindexes si INNER JOIN51Testing软件测试网8f&K W k.d$G3t
dbo.sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid INNER JOIN51Testing软件测试网5~} {[5Q!prc
dbo.syscolumns sc ON sc.id = sik.id AND sc.colid = sik.colid INNER JOIN51Testing软件测试网 l9O
B Y B5X3K3cL!I9f$Y
dbo.sysobjects so ON so.name = so.name AND so.xtype = 'PK'51Testing软件测试网X7Vfl!S\2sxz&?
WHERE sc.id = a.id AND sc.colid = a.colid) THEN '√' ELSE '' END AS 主键,
qtXOi0S0b.name AS 类型,
vp!x7wbLv?T0a.length AS 长度,
q1Hn,uQ1Q}+R0COLUMNPROPERTY(a.id, a.name, 'PRECISION') AS 精度,
+c Sj
NR3n+Z0ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS 小数位数,
)Hbg.h:y4Ut0CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END AS 允许空, ISNULL(e.text, '') AS 默认值,51Testing软件测试网M.NV3j[Tp%h
ISNULL(g.[value], '') AS 字段说明,51Testing软件测试网6n@yp)[,o/JT U+Ygi
d.crdate AS 创建时间,
2Q9i^%t0bu \0CASE WHEN a.colorder = 1 THEN d.refdate ELSE NULL END AS 更改时间
#s"c
Y-E#v;H+Ef0FROM dbo.syscolumns a LEFT OUTER JOIN
:S;V1W!|g"q0lA
qu0dbo.systypes b ON a.xtype = b.xusertype INNER JOIN51Testing软件测试网7FvqL$lg2si
dbo.sysobjects d ON a.id = d.id AND d.xtype = 'U' AND51Testing软件测试网z'G/n+F/_5Rx0}1qY
d.status >= 0 LEFT OUTER JOIN51Testing软件测试网B},fKOyZu
dbo.syscomments e ON a.cdefault = e.id LEFT OUTER JOIN51Testing软件测试网%[x9y,FnHMD
sys.extended_properties g ON a.id = g.major_id AND a.colid = g.minor_id LEFT OUTER JOIN51Testing软件测试网v1W,y'q-\r!]Vn
sys.extended_properties f ON d.id = f.major_id AND f.minor_id = 051Testing软件测试网f J
K-N(YB(Gx+^a+@
ORDER BY d.name, a.colorder

备注：SQL2005用sys.extended_properties替换了sysproperties ，其中字段上major_id代替了id，minor_id替换了smallid.51Testing软件测试网:^Wt$owl!w1zB