How Can I Generate a List of the Columns that Comprise the Primary Key?
-
There has to be a simple way to do this...
I’m writing some generic code that generates HTML, and I’d like to be able to run it on any of 40+ servers. How can I generate a list of the columns that make up the Primary Key? I got sp_primarykeys to work, but it appears to require a Linked Server to be defined which points back to the same server. (I obviously don’t want to define 40+ Linked Servers.) And, I’d like to avoid a temporary table if possible.
The following query almost works, but it displays Foreign Key and Primary Key columns:
select
*
from
TestDBAWeb.INFORMATION_SCHEMA.KEY_COLUMN_USAGE
where
TABLE_NAME = 'RequestDatabaseGroupList'
The following query also almost works, but the “distinct”clause will not let me concatenate additional text and it forces me to display:
select
distinct COLUMN_NAME,
ORDINAL_POSITION
from
TestDBAWeb.INFORMATION_SCHEMA.KEY_COLUMN_USAGE
where
TABLE_NAME = 'RequestDatabaseGroupList'
order by
ORDINAL_POSITION
The following pseudo code is closer to what I want, but it is not syntactically correct:
select
'<INPUT TYPE="HIDDEN" NAME="PassOriginal' + distinct COLUMN_NAME + '" VALUE="#URL.PassDevelopmentArea_NME#">'
from
TestDBAWeb.INFORMATION_SCHEMA.KEY_COLUMN_USAGE
where
TABLE_NAME = 'RequestDatabaseGroupList'
order by
ORDINAL_POSITION
Any help is appreciated.
Jon
-
Hi Jon,
You can take a join between sysindexkeys and syscolumns. Give it the filter of indiid as 1 (Primary key) and the id of the table you want the information for. I hope it helps you.
Regards,
amitm79
- select
*
from
INFORMATION_SCHEMA.KEY_COLUMN_USAGE a
where exists(select * from sysobjects
where a.CONSTRAINT_NAME = NAME and xtype='pk')
-
I've been able to do this 2 different ways. My problem with using the "Information_Schema" Views was that I needed not only the Column Names, but the DataType of those Columns as well.
The First way worked for me for a while, until one day I noticed it was working for EVERY Table except ONE! ONE!
Select SysColumns.Name As ColumnName, SysColumns.XUserType, SysTypes.Name As Type From SysIndexKeys Inner Join SysIndexes ON SysIndexKeys.IndID = SysIndexes.IndID And SysIndexKeys.ID = SysIndexes.ID Inner Join SysColumns ON SysIndexKeys.ColID = SysColumns.ColID And SysIndexKeys.ID = SysColumns.ID Inner Join SysTypes ON SysColumns.XUserType = SysTypes.XUserType Where SysColumns.ID IN(Select ID From SysObjects Where Name = 'TableName') And (SysColumns.Name = Index_Col('TableName', SysIndexes.IndID, SysColumns.ColID))
The 2nd way seemed to work a little better, but in my opinion, Querying the Information_Schema Views is a lot slower than using the SysTables.
Select C.Column_Name As ColumnName, Data_Type As Type From Information_Schema.Key_Column_Usage CU Inner Join Information_Schema.Columns C ON (CU.Table_Name = C.Table_Name) And (CU.Column_Name = C.Column_Name) Where (Constraint_Catalog = 'DatabaseName') And (CU.Table_Name = 'TableName') And (Left(Constraint_Name, 2) = 'PK')
However, one of the Replys here gave me an idea. I don't really like the last part of the 2nd Query because it "assumes" that the Primary Key will start with "PK". Anytime you start assuming, in the long run, you're eventually wrong! So, I think I'll try Inner Join on the SysObjects where the Type = 'P'.
If anyone can find out why the first Query won't always work, PLEASE let me know!!!
-
How about:
SELECT U.ORDINAL_POSITION, U.COLUMN_NAME FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
ON U.CONSTRAINT_NAME = C.CONSTRAINT_NAME
WHERE C.CONSTRAINT_TYPE = 'PRIMARY KEY' AND U.TABLE_NAME = 'YOUR TABLE NAME'
* Noel -
Ok, here's a revised version...
Select C.Column_Name As ColumnName, Data_Type As Type From Information_Schema.Key_Column_Usage CU Inner Join Information_Schema.Columns C ON (CU.Table_Name = C.Table_Name) And (CU.Column_Name = C.Column_Name) Where (Constraint_Catalog = 'DatabaseName') And (CU.Table_Name = 'TableName') And (CU.Constraint_Name IN(Select Name From SysObjects Where Xtype = 'pk'))
-
The folowing is the SQL I use to generate a report on the Constraints in a database. You can modify it to pull just Primary Keys.
Hope this helps
SELECT K.CONSTRAINT_CATALOG AS [Database],
K.TABLE_NAME AS [OBJECT_NAME],
K.CONSTRAINT_NAME,
K.COLUMN_NAME AS [Column],
K.ORDINAL_POSITION AS [colorder],
FK.R_PARENT,
FK.R_COLUMN,
(CASE O.xtype WHEN 'C' THEN 'Check'
WHEN 'F' THEN 'Foreign Key'
WHEN 'PK' THEN 'Primary Key'
WHEN 'UQ' THEN 'Unique'
WHEN 'D' THEN 'Default'
ELSE '' END) AS [Constraint Type]
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE K
LEFT OUTER JOIN (SELECT FK.constid,
RO.[NAME] AS R_PARENT,
CR.[NAME] AS R_COLUMN
FROM sysforeignkeys FK
INNER JOIN syscolumns CR
ON FK.rkeyid = CR.id
INNER JOIN sysobjects RO
ON FK.rkeyid = RO.id
AND FK.rkey = CR.colid) FK
ON OBJECT_ID(K.CONSTRAINT_NAME) = FK.constid
INNER JOIN sysobjects O
ON OBJECT_ID(K.CONSTRAINT_NAME) = O.id
WHERE CONSTRAINT_NAME NOT LIKE '%dtproperties%'
ORDER BY TABLE_NAME, CONSTRAINT_NAME, colorder
-
How about
exec sp_pkeys tablename
-
SELECT
column_name name,
constraint_name name
FROM
'+@destination_DatabaseName+'.information_schema.key_column_usage
WHERE
constraint_catalog='''+@destination_DatabaseName +'''
and table_name = '''+@destination_TableName+'''
ORDER BY
constraint_name, ordinal_position
============================
Richard S. Hale
Senior Database Marketing Developer
Business Intelligence
The Scooter Store
RHale@TheScooterStore.com
DSK: 830.627.4493
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply