How to distinguish Primary Keys from Indexes with this query?

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  
BrianMc1958Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
This will give you the list of Primary keys

exec sp_MSforeachtable  @command1 = ' sp_pkeys ''?'''
MikeWalshCommented:
MikeWalshCommented:
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.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

MikeWalshCommented:
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.
MikeWalshCommented:
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.
BrianMc1958Author 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  
MikeWalshCommented:
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.
BrianMc1958Author 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
BrianMc1958Author 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.
BrianMc1958Author Commented:
I'm getting the same bug on 2005.  It won't return indexes unless the table is under "dbo".
MikeWalshCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BrianMc1958Author Commented:
OK.  Thanks so much for your help!
--BrianMc1958
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.