• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 344
  • Last Modified:

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

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
rmmarsh
Asked:
rmmarsh
  • 4
  • 3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
rmmarshAuthor Commented:
When I run the SELECT statement in the SQL Server Manager, I get an error saying:

 invalid column name 'name'

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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 Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
rmmarshAuthor Commented:
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
 
rmmarshAuthor Commented:
...works, and shows the name (in the SQL Server Manager).
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
> 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
 
rmmarshAuthor Commented:
Ahhh... thank you so much... forgot about that small item  :D
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now