Sql 查询数据库中Table 索引，主键，外键

查询Table中主键，外键，索引个数

with Tab1 as(
SELECT a.name, count(b.name) AS comCounbt
FROM sysobjects AS a INNER JOIN syscolumns AS b ON a.id = b.id
WHERE (a.type = 'u')
Group BY  a.name),
Tab2 as(
select ftable.name ptablename,COunt(fk.name) AS fkCount 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
GROUP BY ftable.name
),
Tab3 As(
select rtable.name ftablename,COunt(fk.name) AS FromFkCount 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
GROUP BY rtable.name
),
TAB4 AS (
SELECT a.name,Count(b.name) AS IndexCount
FROM
sysobjects AS a
INNER JOIN sysindexes AS b ON a.id = b.id
WHERE (a.type = 'u')
Group BY  a.name
)
SELECT Tab1.name,Tab1.comCounbt,isnull(TAB4.IndexCount,0) AS TAB4,isnull(Tab2.fkCount,0) AS refersto,ISNULL(Tab3.FromFkCount,0) AS FromFkCount
FROM Tab1
LEFT JOIN Tab2
ON Tab1.name=Tab2.ptablename
LEFT JOIN Tab3
ON Tab1.name=Tab3.ftablename
LEFT JOIn TAB4
ON Tab1.name=TAB4.name
ORDER BY Tab1.name

查询Table中每一个Columns名字 和对应类型

SELECT a.name,b.name,c.name
FROM sysobjects AS a INNER JOIN syscolumns AS b ON a.id = b.id
inner join systypes c
On b.xtype=c.xtype
WHERE (a.type = 'u')
AND c.name !='sysname'
ORDER BY A.name