Posted By: Larsenal | Jun 9th @ 4:31 PM
page 1 of 1
Comments: 5 | Views: 528
Larsenal
Larsenal
ready to give an answer
Given the name of a table, I want to PRINT the name of the primary key for the table.

sp_Pkeys nearly does the trick except that it returns a table.  I just want to PRINT the column name.

Thanks in advance.

Matthew van Eerde
Matthew van Eerde
AKA Maurits
You could use INSERT/EXEC to dump the table into a temporary table, select the name into a local variable, and print the local variable.
figuerres
figuerres
???
Primary Key is not always 1 column, so you may need to print multiple names.

here is a sql query:

select s.name as TABLE_SCHEMA, t.name as TABLE_NAME,

k.name as CONSTRAINT_NAME, c.name as COLUMN_NAME,

ic.key_ordinal AS ORDINAL_POSITION

from sys.key_constraints as k

join sys.tables as t

on t.object_id = k.parent_object_id

join sys.schemas as s

on s.schema_id = t.schema_id

join sys.index_columns as ic

on ic.object_id = t.object_id

and ic.index_id = k.unique_index_id

join sys.columns as c

on c.object_id = t.object_id

and c.column_id = ic.column_id

where k.type = 'PK' and t.name='YOURTABLEHERE'

the rules of SQL state that an object in the database be definined by the same language as the database objects....
in other words a sql database structure is queryable...

Hi,
i already use this SP, you can use it:
--=================================================
ALTER Procedure [dbo].[uspGetTablePrimaryKey]
(
    @TableName nvarchar(200),
    @DatabaseName varchar(100)
)
AS
Begin
    Declare @SQLStatement varchar(4000)
    Set @SQLStatement = 
    'Select ' + 
    'Col.Column_Name ' + 
    'from ' + 
    @DatabaseName + '.' + 'INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab, ' +
    @DatabaseName + '.' + 'INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col ' +
    ' Where ' + 
    ' Col.Constraint_Name = Tab.Constraint_Name ' + 'and ' + 
    ' Col.Table_Name = Tab.Table_Name ' + 'and ' +
    ' Tab.Constraint_Type = ''PRIMARY KEY ''' + 'and ' +
    ' Col.Table_Name = ''' + @TableName + ''''
    Execute (@SQLStatement)
END
by the way,
if your DB allows to have two columns as primary key and you want there names as a string "comma seperated" use this script:

DECLARE @PrimaryKeys VARCHAR(2000)

SELECT 
    @PrimaryKeys = @PrimaryKeys + ',' + Col.Column_Name
FROM 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab, 
    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col
WHERE 
    Col.Constraint_Name = Tab.Constraint_Name AND
    Col.Table_Name = Tab.Table_Name AND
    Constraint_Type = 'PRIMARY KEY 'AND
    Col.Table_Name = 'LoanServicerTransaction'

SET @PrimaryKeys = SUBSTRING(@PrimaryKeys,0,LEN(@PrimaryKeys))

page 1 of 1
Comments: 5 | Views: 528