?
Solved

How to distinguish Primary Keys from Indexes with this query?

Posted on 2006-03-24
12
Medium Priority
?
239 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  
0
Comment
Question by:BrianMc1958
  • 6
  • 5
12 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16281056
This will give you the list of Primary keys

exec sp_MSforeachtable  @command1 = ' sp_pkeys ''?'''
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16281157
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16281203
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16281274
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
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16281341
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
 

Author Comment

by:BrianMc1958
ID: 16281388
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
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16281416
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
 

Author Comment

by:BrianMc1958
ID: 16281419
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
 

Author Comment

by:BrianMc1958
ID: 16281443
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
 

Author Comment

by:BrianMc1958
ID: 16281539
I'm getting the same bug on 2005.  It won't return indexes unless the table is under "dbo".
0
 
LVL 13

Accepted Solution

by:
MikeWalsh earned 2000 total points
ID: 16281646
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
 

Author Comment

by:BrianMc1958
ID: 16281738
OK.  Thanks so much for your help!
--BrianMc1958
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

850 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