Determining what indexes that DB tables have in an exiting database.

Question

Post reply

Determining what indexes that DB tables have in an exiting database.

John Connell

SSC Veteran

Points: 256
More actions
November 7, 2006 at 12:03 pm

#115037

Is there a tool for listing all the tables and their indexes (including fields) in an existing SQL Server 2000 database, short of manually going table by table through Enterprise Manager? I've already found some discrepancies between the paper 'design' and actuality!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ram Ram Ten Centuries Points: 1079 More actions · Answer 1

Hi

Just try this query and you get everything you wanted.

USE Pubs

GO

SELECT OBJECT_NAME(X.[Id]) AS TableName,

X.[Name] AS IndexName,

INDEXPROPERTY (X.[Id],X.[Name],'IndexFillFactor') AS IndexFillFactor,

REPLACE(((CASE WHEN INDEX_COL(OBJECT_NAME(X.[Id]),X.IndId,1) IS NOT NULL THEN ''+INDEX_COL(OBJECT_NAME(X.[Id]),X.IndId,1)+',' ELSE '' END +

CASE WHEN INDEX_COL(OBJECT_NAME(X.[Id]),X.IndId,2) IS NOT NULL THEN ''+INDEX_COL(OBJECT_NAME(X.[Id]),X.IndId,2)+',' ELSE '' END +

CASE WHEN INDEX_COL(OBJECT_NAME(X.[Id]),X.IndId,3) IS NOT NULL THEN ''+INDEX_COL(OBJECT_NAME(X.[Id]),X.IndId,3)+',' ELSE '' END +

CASE WHEN INDEX_COL(OBJECT_NAME(X.[Id]),X.IndId,4) IS NOT NULL THEN ''+INDEX_COL(OBJECT_NAME(X.[Id]),X.IndId,4)+',' ELSE '' END +

CASE WHEN INDEX_COL(OBJECT_NAME(X.[Id]),X.IndId,5) IS NOT NULL THEN ''+INDEX_COL(OBJECT_NAME(X.[Id]),X.IndId,5)+',' ELSE '' END +

CASE WHEN INDEX_COL(OBJECT_NAME(X.[Id]),X.IndId,6) IS NOT NULL THEN ''+INDEX_COL(OBJECT_NAME(X.[Id]),X.IndId,6)+',' ELSE '' END +

CASE WHEN INDEX_COL(OBJECT_NAME(X.[Id]),X.IndId,7) IS NOT NULL THEN ''+INDEX_COL(OBJECT_NAME(X.[Id]),X.IndId,7)+',' ELSE '' END +

CASE WHEN INDEX_COL(OBJECT_NAME(X.[Id]),X.IndId,8) IS NOT NULL THEN ''+INDEX_COL(OBJECT_NAME(X.[Id]),X.IndId,8)+',' ELSE '' END +

CASE WHEN INDEX_COL(OBJECT_NAME(X.[Id]),X.IndId,9) IS NOT NULL THEN ''+INDEX_COL(OBJECT_NAME(X.[Id]),X.IndId,9)+',' ELSE '' END +

CASE WHEN INDEX_COL(OBJECT_NAME(X.[Id]),X.IndId,10) IS NOT NULL THEN ''+INDEX_COL(OBJECT_NAME(X.[Id]),X.IndId,10)+',' ELSE '' END +

CASE WHEN INDEX_COL(OBJECT_NAME(X.[Id]),X.IndId,11) IS NOT NULL THEN ''+INDEX_COL(OBJECT_NAME(X.[Id]),X.IndId,11)+',' ELSE '' END +

CASE WHEN INDEX_COL(OBJECT_NAME(X.[Id]),X.IndId,12) IS NOT NULL THEN ''+INDEX_COL(OBJECT_NAME(X.[Id]),X.IndId,12)+',' ELSE '' END +

CASE WHEN INDEX_COL(OBJECT_NAME(X.[Id]),X.IndId,13) IS NOT NULL THEN ''+INDEX_COL(OBJECT_NAME(X.[Id]),X.IndId,13)+',' ELSE '' END +

CASE WHEN INDEX_COL(OBJECT_NAME(X.[Id]),X.IndId,14) IS NOT NULL THEN ''+INDEX_COL(OBJECT_NAME(X.[Id]),X.IndId,14)+',' ELSE '' END +

CASE WHEN INDEX_COL(OBJECT_NAME(X.[Id]),X.IndId,15) IS NOT NULL THEN ''+INDEX_COL(OBJECT_NAME(X.[Id]),X.IndId,15)+',' ELSE '' END +

CASE WHEN INDEX_COL(OBJECT_NAME(X.[Id]),X.IndId,16) IS NOT NULL THEN ''+INDEX_COL(OBJECT_NAME(X.[Id]),X.IndId,16)+',' ELSE '' END)+'~'),',~','') AS IndexColumns,

X.[Id] AS ObjectId,

X.IndId AS IndexId,

STATS_DATE(X.[Id],X.IndId)AS LastUpdatedDate

FROM SysIndexes AS X

LEFT JOIN sysobjects SO ON X.[id] = SO.[id]

WHERE xtype = 'U'

AND X.INDID > 0

AND X.INDID < 255

AND (X.STATUS & 64)=0 -- Exclude Statistics

ORDER BY SO.[Name]

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 2

How about this one:

SELECT so.name TblName, sc.name ColName, si.indid

FROM (sysobjects so INNER JOIN syscolumns sc ON so.id = sc.id AND so.type = 'U')

INNER JOIN sysindexes si ON si.id = so.id ORDER BY 1, 2

John Connell SSC Veteran Points: 256 More actions · Answer 3

Thanks, Sriram. That gives me just what I was looking for. The indexes I thought were 'missing'' (I didn't see them in Enterprise Mgr) are there after all!

Thanks