Function to List Primary Key Columns for a Table

IF exists (SELECT * from dbo.sysobjects 
WHERE id = object_id(N'[dbo].[PrimaryKeyColumns]') 
AND OBJECTPROPERTY(id, N'IsTableFunction') = 1)
DROP FUNCTION [dbo].[PrimaryKeyColumns]
GO


GO
CREATE FUNCTION dbo.PrimaryKeyColumns (@sysTableName sysname )
/**************************************************************************
DESCRIPTION:    Finds the name and column id of primary key columns for a
table.

PARAMETERS:
@sysTableName- The name of the table for which primary
key info is sought

RETURNS:
Table data type list of primary key column names

USAGE:
SELECT * from dbo.PrimaryKeyColumns( 'authors')

AUTHOR:Karen Gayda

DATE: 10/24/2003

MODIFICATION HISTORY:
WHODATEDESCRIPTION
----------------------------------------------------------------

***************************************************************************/RETURNS @tblCols TABLE 
   (
   namesysname,-- Name of primary key column
colidsmallint-- System id for column
   )
AS
BEGIN

INSERT INTO @tblCols
SELECT  c.name, c.colid
FROM      sysindexes i
  INNER JOIN sysobjects t
ON i.id = t.id
  INNER JOIN sysindexkeys k
        ON i.indid = k.indid
        AND i.id = k.ID
  INNER JOIN syscolumns c
ON c.id = t.id
        AND c.colid = k.colid
WHERE  i.id = t.id
 AND      i.indid BETWEEN 1 And 254 
 AND      (i.status & 2048) = 2048
 AND t.id = OBJECT_ID(@sysTableName)
ORDER BY k.KeyNo


RETURN
END
GO