Solved

How to determine if an index already exists for a table?

Posted on 2006-07-08
7
331 Views
Last Modified: 2012-06-21
Hi... I have a table which I want to programmatically create a clustered index using the following statement:

ALTER TABLE tBooks ADD CONSTRAINT PK_BookNbr PRIMARY KEY CLUSTERED (BookNbr)

which works just fine.  Now, I want to check if the index already exists before I issue the command again (from within a C# program).  I was using the following command:

SELECT name FROM sysindexes WHERE name = 'PK_BookNbr'

but it doesn't find the existing index and tries to re-create it.

What is the proper syntax to see if the index already exists in a table?

Thanks for your help in advance.
0
Comment
Question by:rmmarsh
  • 4
  • 3
7 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17066210
the PRIMARY KEY is a constraint, so you have to look here:
SELECT name FROM sysconstraints WHERE name = 'PK_BookNbr'

there will also be an entry in sysindexes, but with a system-generated name
0
 

Author Comment

by:rmmarsh
ID: 17066229
When I run the SELECT statement in the SQL Server Manager, I get an error saying:

 invalid column name 'name'

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17066240
sorry:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sys-c_8f77.asp

SELECT object_name( constid) FROM sysconstraints WHERE object_name(id) = 'PK_BookNbr'
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:rmmarsh
ID: 17066293
nope... that's not it either... nothing is returned from that command

My command -> SELECT name FROM sysindexes WHERE name = 'PK_BookNbr'
works, and shows the name.  However, when I run it in the program, it returns no rows (it should return 1 row)...
 
0
 

Author Comment

by:rmmarsh
ID: 17066295
...works, and shows the name (in the SQL Server Manager).
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 17066368
> However, when I run it in the program, it returns no rows
are you connected to the correct database?

SELECT name FROM <yourdatabase>.dbo.sysindexes WHERE name = 'PK_BookNbr'
0
 

Author Comment

by:rmmarsh
ID: 17066400
Ahhh... thank you so much... forgot about that small item  :D
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now