June 12, 2002 at 12:17 pm
I need to write a query against the system tables that will provide a list of tables in a database with indexes AND which columns are indexed.
-Kevin
June 12, 2002 at 1:08 pm
OK, I figured that one out:
select c.name "Column Name",
o.name "Table Name"
--i.name "Index Name sometimes"
from sysindexes i,syscolumns c,sysobjects o
where c.id=i.id
and i.id=o.id
and o.xtype<>'S'
BUT HOW ABOUT GETTING THE SIZE OF EACH TABLE IN A DATABASE???
June 12, 2002 at 1:09 pm
See if the following works for you:
SELECT so.name [Table],
si.name [Index], sc.name [Column]
FROM sysindexes si
JOIN sysobjects so ON si.id = so.id
JOIN sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid
JOIN syscolumns sc ON sik.id = sc.id AND sik.colid = sc.colid
ORDER BY [Table], [Index], sik.keyno
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply