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:
	WHO		DATE		DESCRIPTION
	---		----------	---------------------------------------------------

***************************************************************************/
RETURNS @tblCols TABLE 
   (
   	name		sysname,		-- Name of primary key column
	colid		smallint		-- 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