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?
 
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.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
This will give you the list of Primary keys

exec sp_MSforeachtable  @command1 = ' sp_pkeys ''?'''
0
 
MikeWalshCommented:
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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.
0
 
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.
0
 
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.
0
 
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  
0
 
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.
0
 
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
0
 
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.
0
 
BrianMc1958Author Commented:
I'm getting the same bug on 2005.  It won't return indexes unless the table is under "dbo".
0
 
BrianMc1958Author Commented:
OK.  Thanks so much for your help!
--BrianMc1958
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.