getting Primary key and identity colum info

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

getting Primary key and identity colum info

Author

Topic

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-02-20 : 02:59:30

Hi All,

Can any of you help me in getting info about, weather the col is primay key and weather the col is identity.

I have to insert these info into my own data dict table
using information schema i got most of the info except primay key
and identity property of the col.

insert into mydict
values
select column_name,
table_name,
data_type,
--primay ( 1 if primary else 0 if not primary)
--idenity( 1 if identiy else 0 if not identity)
ordinal_position
from information_schema.columns

Thanks,
aak

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-20 : 03:19:32

[code]select column_name,
table_name,
data_type,
case when i.name is not null then 1 else 0 end as is_identity,
case when k.name is not null then 1 else 0 end as is_pk
from information_schema.columns c
left join sys.identity_columns i
on i.name=c.column_name
left join sys.key_constraints k
on k.name=c.column_name
and k.type='pk'[/code]

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-02-20 : 05:49:11

Thank you so much Visakh, I was looking for this code only.

quote:

Originally posted by visakh16

select  column_name,
table_name,
data_type,
case when i.name is not null then 1 else 0 end as is_identity,
case when k.name is not null then 1 else 0 end as is_pk
 from information_schema.columns c
left join sys.identity_columns i
on i.name=c.column_name
left join sys.key_constraints k
on k.name=c.column_name
and k.type='pk'

raky
Aged Yak Warrior

767 Posts

Posted - 2009-02-20 : 07:03:18

when i tested visakh query it is not giving correct results for primary key columns

Try this also

select distinct c.column_name,
c.table_name,
data_type,
case when i.name is not null then 1 else 0 end as is_identity,
case when k.column_name is not null then 1 else 0 end as is_pk
from information_schema.columns c
left join sys.identity_columns i
on i.name=c.column_name
left join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE k
on k.column_name=c.column_name
and k.constraint_name like '%pk_%'

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-20 : 07:51:41

quote:
Originally posted by raky

when i tested visakh query it is not giving correct results for primary key columns

Try this also

select distinct c.column_name,
c.table_name,
data_type,
case when i.name is not null then 1 else 0 end as is_identity,
case when k.column_name is not null then 1 else 0 end as is_pk
from information_schema.columns c
left join sys.identity_columns i
on i.name=c.column_name
left join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE k
on k.column_name=c.column_name
and k.constraint_name like '%pk_%'

This is not right. You can have a constraint named like '%pk_%' and it might not be the primary key.

For PK, You could use this,


SELECT i.name AS IndexName,
OBJECT_NAME(ic.OBJECT_ID) AS TableName,
COL_NAME(ic.OBJECT_ID,ic.column_id) AS ColumnName
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
ON i.OBJECT_ID = ic.OBJECT_ID
AND i.index_id = ic.index_id
WHERE i.is_primary_key = 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-20 : 09:32:24

[code]
select column_name,
c.table_name,
c.data_type,
case when i.name is not null then 1 else 0 end as is_identity,
case when ccu.column_name is not null then 1 else 0 end as is_pk
from information_schema.columns c
left join sys.identity_columns i
on i.name=c.column_name
left join information_schema.table_constraints tc
on tc.table_name=c.table_name
and tc.constraint_type='PRIMARY KEY'
left join information_schema.constraint_column_usage ccu
on ccu.column_name=c.column_name
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources