We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

How to distinguish Primary Keys from Indexes with this query?

BrianMc1958
BrianMc1958 asked
on
Medium Priority
259 Views
Last Modified: 2012-06-27
Dear Experts,

I have found a query which returns all the Primary Keys and Indexes for a db.  Is there a way I can unambiguously distinguish which is a Primary Key and which is an Index?  (I know I could probably do it just by looking for "PK_" in the name, but that's not perfect.)

This is the query:

SELECT *
FROM sysindexes i
WHERE indid BETWEEN 1 AND 254
AND indexproperty(id, name, 'IsHypothetical') = 0
AND indexproperty(id, name, 'IsStatistics') = 0
AND indexproperty(id, name, 'IsAutoStatistics') = 0
AND objectproperty(id, 'IsMsShipped') = 0
ORDER by name

And this is a variation on the query which shows table names in clear text:

SELECT "table" = object_name(id), name
FROM sysindexes i
WHERE indid BETWEEN 1 AND 254
AND indexproperty(id, name, 'IsHypothetical') = 0
AND indexproperty(id, name, 'IsStatistics') = 0
AND indexproperty(id, name, 'IsAutoStatistics') = 0
AND objectproperty(id, 'IsMsShipped') = 0
ORDER BY "table", name

BTW, I'm a SQL dummy...  Hopefully the answer is simple...

Thanks,
BrianMc1958  
Comment
Watch Question

AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
This will give you the list of Primary keys

exec sp_MSforeachtable  @command1 = ' sp_pkeys ''?'''
No.. I knew that didn't sound right just as soon as I clicked submit. 1 means it is a clustered index. All PKs default to clustered index unless you change them, but using my 1 would give you some that are not true PKs and potentially exclude some.
I believe Status may be your answer. I could not find much in the way of supporting documentation, but I believe this holds true and has in all the tests I have done so far:

Status 18450 = PK
Status 2113552 = Clustered Index
Status 2097152 = Nonclustered Index

There are other statuses as well. But it's always a bad idea to be querying the system table directly.

What are you trying to accomplish there are probably other ways of getting at info you need including documented stored procs, information schema views etc.
select kcu.TABLE_SCHEMA, kcu.TABLE_NAME, kcu.CONSTRAINT_NAME, kcu.COLUMN_NAME, kcu.ORDINAL_POSITION
  from INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc
  join INFORMATION_SCHEMA.KEY_COLUMN_USAGE as kcu
    on kcu.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA
   and kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
   and kcu.TABLE_SCHEMA = tc.TABLE_SCHEMA
   and kcu.TABLE_NAME = tc.TABLE_NAME
 where tc.CONSTRAINT_TYPE = 'PRIMARY KEY'

This also shows only PKs. This is using the information schemas. MS prefers us to use these especially in pieces of code we will be saving and upgrading with versions of SQL, system tables can change at any time.

Author

Commented:
To MikeWalsh:  Thanks.  I had been using a Java method called DataBaseMetadata.dbmd.getIndexInfo.  However--I think do to a recent MS SQL upgrade--it is now failing on tables not under the "dbo" schema.  And all the tables I want are NOT under the "dbo" schema.  It seems like a major bug in MS SQL.  I have seen the warnings about accessing system tables directly, but I'm a bit desperate here.  Do you know of a safer way to get what I want?

Thanks,
BrianMc1958  
What do you want and what do you need it for? What version are you under, SQL 2005 I presume?

I don't need full details on the need it for, but it will help to give you the best answer.

Author

Commented:
Our posts crossed in the mail...

Anyway, what I'm stuck on here is getting the indexes.  I already have a method of getting the Primary Keys.  If necessary, I guess I could use my original posted code to get all the keys, and then eliminate the Primary Keys returned from my other query.  It just seems the must be an easier way!

--BrianMc1958

Author

Commented:
OK.  We crossed again.  I have both SQL 2000 and 2005 (I think).  I need the Primary Keys and Indexes to auto-generate Java code based on them.

Author

Commented:
I'm getting the same bug on 2005.  It won't return indexes unless the table is under "dbo".
Yeah I haven't played with 2005 much, but there is a new setup with schemas and permissions that is probably causing this. I haven't read a whole lot on that so don't want to lead you astray there.

For SQL 2000, because using the Status codes or the left 2 of the object name are not 100% reliable (unless all objects are definitely starting with PK if they are a primary key) , I would just get the PKs in one of the ways described, then get all objects, and parse one result set against the other by object name so you are left with just indexes and pks.. It is a little more intensive, but its not the end of the world and you are going to definitely get the right objects defined as the right objects this way.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
OK.  Thanks so much for your help!
--BrianMc1958
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.