--查询表名，字段名，描述
SELECT
A.name AS table_name,
B.name AS column_name,
C.value AS column_description
FROM sys.tables A
INNER JOIN sys.columns B
ON B.object_id = A.object_id
LEFT JOIN sys.extended_properties C
ON C.major_id = B.object_id
AND C.minor_id = B.column_id
WHERE A.name = "MuMu"
--查询表的字段名，字段类型等
SELECT c.name,t.name,c.xprec,c.xscale,c.isnullable
FROM systypes t,syscolumns c
WHERE t.xtype=c.xtype
AND c.id = (SELECT id FROM sysobjects WHERE name="c_bill")
ORDER BY c.colid;
--后面这两个没试验，不知道对不对也不知道什么用
--主键（参考SqlServer系统存储过程sp_pkeys）：
select COLUMN_NAME = convert(sysname,c.name)
from
sysindexes i, syscolumns c, sysobjects o
where o.id = object_id("[YourTableName]")
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
and (c.name = index_col ("[YourTableName]", i.indid, 1) or
c.name = index_col ("[YourTableName]", i.indid, 2) or
c.name = index_col ("[YourTableName]", i.indid, 3) or
c.name = index_col ("[YourTableName]", i.indid, 4) or
c.name = index_col ("[YourTableName]", i.indid, 5) or
c.name = index_col ("[YourTableName]", i.indid, 6) or
c.name = index_col ("[YourTableName]", i.indid, 7) or
c.name = index_col ("[YourTableName]", i.indid, 8) or
c.name = index_col ("[YourTableName]", i.indid, 9) or
c.name = index_col ("[YourTableName]", i.indid, 10) or
c.name = index_col ("[YourTableName]", i.indid, 11) or
c.name = index_col ("[YourTableName]", i.indid, 12) or
c.name = index_col ("[YourTableName]", i.indid, 13) or
c.name = index_col ("[YourTableName]", i.indid, 14) or
c.name = index_col ("[YourTableName]", i.indid, 15) or
c.name = index_col ("[YourTableName]", i.indid, 16)
)
--外键：
select t1.name,t2.rtableName,t2.name
from
(select col.name, f.constid as temp
from syscolumns col,sysforeignkeys f
where f.fkeyid=col.id
and f.fkey=col.colid
and f.constid in
( select distinct(id)
from sysobjects
where OBJECT_NAME(parent_obj)="YourTableName"
and xtype="F"
)
) as t1 ,
(select OBJECT_NAME(f.rkeyid) as rtableName,col.name, f.constid as temp
from syscolumns col,sysforeignkeys f
where f.rkeyid=col.id
and f.rkey=col.colid
and f.constid in
( select distinct(id)
from sysobjects
where OBJECT_NAME(parent_obj)="YourTableName"
and xtype="F"
)
) as t2
where t1.temp=t2.temp
