how-to find out whether or not a column is the primary key in a table on sql server 2008

-- GENERIC WAY OF FINDING OUT WHETHER A COLUMN IS THE PRIMARY KEY FOR A TABLE

-- IN SQL SERVER 2008

DECLARE @TableName nvarchar(200)

DECLARE @ColumnName nvarchar(200)

SELECT @TableName = 'Bug'

SELECT @ColumnName = 'BugId'

SELECT 

INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME AS TABLE_NAME  , 

INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME AS COLUMN_NAME, 

REPLACE(INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE,' ', '_') AS CONSTRAINT_TYPE 

--INTO ##TMP

FROM 

INFORMATION_SCHEMA.TABLE_CONSTRAINTS 

INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE ON 

INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME = 

INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME 

WHERE 

INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME <> N'sysdiagrams' 

AND INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME = @TableName 

AND INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME = @ColumnName

ORDER BY 

INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME ASC

declare @IsPrimaryKey int 

select @IsPrimaryKey  = @@ROWCOUNT

IF @IsPrimaryKey  = 1 

SELECT 'The Column Name ' + @ColumnName + ' IS the Primary Key for the ' + @TableName + ' table '

ELSE 

SELECT 'The Column Name ' + @ColumnName + ' IS NOT the Primary Key for the ' + @TableName + ' table '