Function to List Primary Key Columns for a Table

Question

Function to List Primary Key Columns for a Table

kgayda

SSCrazy

Points: 2157
More actions
October 4, 2007 at 3:29 pm

#198772

Comments posted to this topic are about the item Function to List Primary Key Columns for a Table

Karen Gayda
MCP, MCSD, MCDBA

gaydaware.com

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Sergey Kazachenko Ten Centuries Points: 1051 More actions · Answer 1

The "i.id = t.id" in the WHERE clause is not needed because it's already in the join condition.

Since we are looking for primary keys and text fields cannot be part of primary key, the "AND i.indid BETWEEN 1 And 254" is not needed as well.

Otherwise, nice code!

celinocaliente2002 Grasshopper Points: 15 More actions · Answer 2

Here is an alternative using the INFORMATION_SCHEMA view.

select column_name

from information_schema.table_constraints join information_schema.key_column_usage on (information_schema.table_constraints.constraint_name = information_schema.key_column_usage.constraint_name)

whereinformation_schema.table_constraints.table_name = @table_name

and constraint_type = 'PRIMARY KEY'

Function to List Primary Key Columns for a Table

Cookies on SQLServerCentral