need to check Indexes - 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

need to check Indexes

Author

Topic

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2010-05-10 : 06:46:08

Hi All,

How to check the indexes on the column of any particular table whether it is clustered index or non clustered index.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-05-10 : 07:01:07

If it's a one off thing then you can just use management studio. Go into the object explorer and drill down to the table you are interested in. There is a subfolder called indexes and you can see there whether the index is clustered or non clustered.

If you want to do this via sql you can query the sys.indexes system view. There are loads of examples online so go google it

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

Kristen
Test

22859 Posts

Posted - 2010-05-10 : 07:14:38

I just type


EXEC sp_help MyTableName

into a query window (you don't need the EXEC if its just a single command). The list of indexes, for the table, also indicates which is the Clustered one - as well as lots of other useful info about the Table (sp_help works with other objects too)

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2010-05-10 : 08:59:30

Thanks for your reply but i have requirement in which if the index is
not exists then create on the particular table else drop it ..
i am struggling to get it done ..

quote:
Originally posted by Kristen

I just type

EXEC sp_help MyTableName

into a query window (you don't need the EXEC if its just a single command). The list of indexes, for the table, also indicates which is the Clustered one - as well as lots of other useful info about the Table (sp_help works with other objects too)

Kristen
Test

22859 Posts

Posted - 2010-05-10 : 09:12:41

[code]
IF EXISTS (SELECT * FROM dbo.sysindexes WHERE name = N'MyIndexName'
AND id = object_id(N'[dbo].[MyTable]'))
DROP INDEX [dbo].[MyTable].[MyIndexName]
GO
CREATE INDEX MyIndexName ON dbo.MyTable
...
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources