Index information - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Index information

Author

Topic

NeilG
Aged Yak Warrior

530 Posts

Posted - 2009-03-10 : 05:31:36

Hi Guys,

I'm trying to use the system view to get all of the information on indexes for a particular databases, but i can see how the sysindexes are linked to sysobjects etc.

Im looking for something like

DBTableName - Column - Index - IndexType

Can anyone help or point me in the right direction

Cheers

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-10 : 05:42:05

[code]
SELECT t.name AS DBTableName ,c.name AS Column ,i.name AS Index,i.type AS IndexType
FROM sys.indexes i
JOIN sys.columns c ON c.column_id=i.object_id
JOIN sys.tables t ON t.object_id=c.object_id
[/code]

NeilG
Aged Yak Warrior

530 Posts

Posted - 2009-03-10 : 05:45:09

Thanks i'll have a look at that

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-10 : 05:48:47

welcome

NeilG
Aged Yak Warrior

530 Posts

Posted - 2009-03-10 : 06:20:55

is there a reason why it wouldn't be able to find these table on a 2000 box, i've run the queries of a 2005 databases and they run correctly but then trying to run on 2000 databases it won't find the tables - but i can find the

sysindexes, sysobjects etc tables

is this correct

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-10 : 06:31:13

sys.indexes,sys.columns etc views are available only from sql 2005 onwards, on sql 2000, you need to use system tables like syscolumns,systables,sysindexes instead...

NeilG
Aged Yak Warrior

530 Posts

Posted - 2009-03-10 : 06:47:02

right so is it just a case of changing the object_id to id then like below

SELECT o.name AS DBTableName ,c.name AS Column ,i.name AS Index,i.type AS IndexType
FROM sysindexes i
JOIN syscolumns c ON c.id=i.id
JOIN objects o ON o.id=c.id
WHERE
o.type = U

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-10 : 06:49:27

you need to check in books online if names of columns are same in system tables

NeilG
Aged Yak Warrior

530 Posts

Posted - 2009-03-10 : 06:54:33

Thanks

NeilG
Aged Yak Warrior

530 Posts

Posted - 2009-03-10 : 07:12:14

Just another question, on the other tables there doesn't appear to have a column which states what type of index it is, so i've decided to look at the indid value am i correct in thinking that 1 = Clustered index and 2-255 is a non-clustered index

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-03-10 : 07:58:26

quote:

Originally posted by visakh16


SELECT t.name AS DBTableName ,c.name AS Column ,i.name AS Index,i.type AS IndexType
FROM sys.indexes i
JOIN sys.columns c ON c.column_id=i.object_id
JOIN sys.tables t ON t.object_id=c.object_id

Tiny correction: COLUMN and INDEX are keywords. Best to enclose in brackets.


SELECT
	t.[name] AS [DBTableName]
	, c.[name] AS [Column]
	, i.[name] AS [Index]
	, i.[type] AS [IndexType]
FROM
	sys.indexes i
	JOIN sys.columns c ON c.[column_id] = i.[object_id]
	JOIN sys.tables t ON t.[object_id] = c.[object_id]

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

NeilG
Aged Yak Warrior

530 Posts

Posted - 2009-03-10 : 08:25:20

charlie, thanks but i'm trying to do the same but with the sql 2000 sys table but struggling have you ever done this before

NeilG
Aged Yak Warrior

530 Posts

Posted - 2009-03-10 : 08:50:53

I've looked at something like this do people think I working along the correct lines

SELECT
*
FROM
sysobjects a
INNER JOIN
syscolumns b
ON
a.id = b.id
INNER JOIN
sysindexkeys c
ON
a.id = c.id
AND b.colid = c.colid
INNER JOIN
sysindexes d
ON
a.id = d.id
and c.indid = d.indid
WHERE
a.type = 'u'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources