February 6, 2009 at 3:49 am
Hi
I want to retrieve the first column from all the tables in a MS SQL 2005 db and I also want to know whether it is a PK or not.
I can get the PK for the tables using this:
select a.Table_Name, a.Column_Name from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE a,
INFORMATION_SCHEMA.TABLE_CONSTRAINTS b
where b.constraint_type='PRIMARY KEY'
The problem with the above is that although I can get the PK for all the tables, but it does not tell me if this PK was the first column or not. My issue might seem silly because the order of the columns in a table really should not matter, but I will try to explain the rationale behind it (not now but some other time).
Thanks in advance
Adish
February 6, 2009 at 6:18 am
man......... you realy put a thing that keep me in thinking ok you will get your answer
Raj Acharya
February 6, 2009 at 6:27 am
here is your answer try my best to do this
SELECT OBJECT_NAME(a.OBJECT_ID) AS tablename ,a.NAME AS first_column_name,a.column_id,b.colid,b.indid,
CASE WHEN b.indid = 1 THEN 'PRIMARY KEY ' WHEN b.indid= 2 THEN 'NON CLUSTERED INDEX AND NOT PRIMARYKEY' ELSE 'NOT PRIMARY KEY 'END AS key_desc
FROM sys.columns AS a
INNER join sys.sysobjects AS c ON a.[OBJECT_ID] =c.id
FULL OUTER JOIN SYS.sysindexkeys AS b
ON a.column_id = b.colid and a.[object_id]=b.id
WHERE OBJECT_NAME(a.OBJECT_ID) NOT LIKE 'sys%'
AND a.column_id = 1
AND c.xtype = 'U'
Raj Acharya
February 6, 2009 at 6:58 am
Perfect
Thanks a lot, Raj.
Adish
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply