Tuesday, June 15, 2010

Get name of non-primarykey columns of a table in SQL server

Get all column names of a table that are not primary key.

Select COLUMN_NAME

FROM INFORMATION_SCHEMA.columns

WHERE TABLE_NAME = 'tbl_Name'

AND COLUMN_NAME NOT in

(select c.COLUMN_NAME

from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,

INFORMATION_SCHEMA.KEY_COLUMN_USAGE c

where pk.TABLE_NAME = 'tbl_Name'

and CONSTRAINT_TYPE = 'PRIMARY KEY'

and c.TABLE_NAME = pk.TABLE_NAME

and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME)

No comments:

Post a Comment