list the primay key in entire database

[ravi]

I'm running the below query for listing all the primary key in a database but it is retiving 0 rows.

DECLARE @cmdstr varchar(100)
CREATE TABLE dbo.TempTable
( TABLE_QUALIFIER varchar(10),
TABLE_OWNER varchar(5),
Table_Name varchar(10),
COLUMN_NAME varchar(15),
KEY_SEQ int,
PK_NAME varchar(50),
)
--Create Stored Procedure String
SELECT @cmdstr = 'sp_msforeachtable ''sp_pkeys "?"'''

INSERT INTO TempTable EXEC(@cmdstr)
SELECT Table_Name,COLUMN_NAME,PK_NAME FROM TempTable ORDER BY Table_Name
DROP TABLE TempTable

Any body can give advice regarding this.
Thanks,
Ravi

[rmiao]

Tried query information_schema.key_column_usage?

[ravi]

rmiao,
Nop, information_schema.key_column_usage is not giving correct primary key information.
Any other way to get the primay key list.
Thanks,
Ravi

[ravi]

Finaly I found the script for Find the Table has Primay key and Don't have primary key list.
Please test once again and let me know if it is wrong.


---Find the Table have Primary key Column
select distinct so.name Table_Name --,sc.name Column_Name
from sysindexes si, sysindexkeys sk, syscolumns sc, sysobjects so
where si.indid=sk.indid
and sk.colid=sc.colid
and sk.id=so.id
and so.type='U'
and si.indid=1
order by so.name

---Find the Table Don't have Primary key Column
select name from sysobjects so1
where not exists(
select 1
from sysindexes si, sysindexkeys sk, syscolumns sc, sysobjects so
where si.indid=sk.indid
and sk.colid=sc.colid
and sk.id=so.id
and so.type='U'
and si.indid=1
and so1.id=so.id)
and so1.type='U'
order by so1.name

[MAK]

--Just do a sp_helptext sp_Pkeys and understand the logic. Here is the hacked code to get PKs from all tables.

select Tablename=a.name, ColumnName=c.name, PKName=b.name ,IndexName=d.name,
Key_SEQ = c.colid from
sysobjects a, sysobjects b, syscolumns c ,Sysindexes d where b.type='K'
and b.parent_obj = a.id and c.id=a.id and (d.status & 0x800) = 0x800 and
a.id = d.id
and (c.name = index_col (a.name, d.indid, 1) or
c.name = index_col (a.name, d.indid, 2) or
c.name = index_col (a.name, d.indid, 3) or
c.name = index_col (a.name, d.indid, 4) or
c.name = index_col (a.name, d.indid, 5) or
c.name = index_col (a.name, d.indid, 6) or
c.name = index_col (a.name, d.indid, 7) or
c.name = index_col (a.name, d.indid, 8) or
c.name = index_col (a.name, d.indid, 9) or
c.name = index_col (a.name, d.indid, 10) or
c.name = index_col (a.name, d.indid, 11) or
c.name = index_col (a.name, d.indid, 12) or
c.name = index_col (a.name, d.indid, 13) or
c.name = index_col (a.name, d.indid, 14) or
c.name = index_col (a.name, d.indid, 15) or
c.name = index_col (a.name, d.indid, 16))

[Claire]

What's wrong with R-Meow's answer?

select table_name,column_name from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where constraint_name like 'pk%'

did give you the right answer!

[ravi]

Clair,
Constraint name is user defined name my exisiting constraint name not like pk it is different. If I'm using the INFORMATION_SCHEMA.KEY_COLUMN_USAGE will give PK and foriegn key constraint.
Either my script or MAK script will work fine. Why don't you try and give your feed back?.
Thanks for your suggestion.
Ravi

[Claire]

I dont get you. You want to list all the primary key right...the

INFORMATION_SCHEMA.KEY_COLUMN_USAGE

will gives you primary key! What's the difference from your constraints?

[ravi]

Clair,
The INFORMATION_SCHEMA.KEY_COLUMN_USAGE
will give all the constraints. My tables have primary key with pppxxx and various non genric name so that I can't able to get only primay keys tables.
As per your suggestion if the tables has primary key name with PKxxx then we can use this otherwise or generaly we need to use mine or MAK script.

Thanks,
Ravi

[Claire]

There is a column name in that view called "CONSTRAINT_TYPE"

select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where constraint_type = 'primary key'


I dont think by putting where condition here you have any limitation for your primary key constraint name!

[Claire]

No updates?

[KingSexy182]

I totally agree with Claire.

Why would you want to go around the mountain when you can go straight through it!!!

Claire i use the information_schema views all the time, it does all the joins for you. You dont have to do all messy joins, when you can let SQL Server do it for you!!!!


I remember those times when i was using this piece of query all the time:

select o.name, c.name, c.colid
from sysobjects o
inner join syscolumns c
on o.id = c.id
where o.type = 'u'

these days i just 'select * from information_schema.columns' or whateva im looking for.

Regards.

[ravi]

I agree clair post 100% now. But rmiao
suggestion using INFORMATION_SCHEMA.KEY_COLUMN_USAGE didn't work well.
And clair's select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where constraint_type = 'primary key'
will work perfect.
Thanks for the continous reply.
Ravi

[KingSexy182]

Hi Ravi,
One thing ive learnt is that there are no right or wrong answers.

information_schema.key_column_usage in a way lists the primary keys. It lists every column (if multiple) used in primary, unique, foreign keys.
And if you were just searching the for a list of the constraints then information_schema.table_constraints would be fine.

[matmis]

Actually, there is a bug in MSSQL 2000 in the INFORMATION_SCHEMA.KEY_COLUMN_USAGE view. Sometimes it does not list the columns for primary keys and unique constraints. Actually, this occurs when the table is outside "dbo" schema -- maybe it's the source of ravi's problem