Indexes on Tables - 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

SQL Server 2000 Forums

SQL Server Administration (2000)

Indexes on Tables

Author

Topic

aelazouzi
Starting Member

7 Posts

Posted - 2005-09-21 : 05:10:11

Hi All,

I'm auditing a SQL 2000 Server database.
I Have two questions:
The first is:
Is there a method to collect information about Indexes on all tables?

I'm using the following query:

select table_name = sysobjects.name,
index_name = sysindexes.name, index_id = indid
from sysindexes, sysobjects
where sysobjects.name = 'TableName'
and sysobjects.id = sysindexes.id

But I dont get which index is a clusterd index or not.

The Second is:
I see some Indexes begining with _WA_SYS_.....

What are this indexes?

Tanx

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-21 : 05:15:39

See if this helps you

Select CU.table_name, CU.Column_Name,CU.constraint_Name,TC.Constraint_Type
from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CU inner join
INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC on CU.Constraint_Name=TC.Constraint_Name
order by CU.table_name

>>I see some Indexes begining with _WA_SYS_.....
I think they show all other columns of that table

Also run this for more information

sp_help 'TableName'

Madhivanan

Failing to plan is Planning to fail

aelazouzi
Starting Member

7 Posts

Posted - 2005-09-21 : 05:57:25

THank u madhivanan for your reaction.

I have some indexes begining with _WA_SYS_.... do u know what kind of indexes are they?
Are This indexes created automaticly by the SQL server?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-21 : 06:55:27

They are other columns with default indexes. But I am not sure

Madhivanan

Failing to plan is Planning to fail

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-09-21 : 07:13:50

>>I have some indexes begining with _WA_SYS_.... do u know what kind of indexes are they?
They are statistics.. histogram goodness...read all about them in BOL..

DavidM

A front-end is something that tries to violate a back-end.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources