Solved

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

Posted on 2006-07-08
7
334 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 143

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 143

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
Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

 

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 143

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

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.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

730 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