Monday, June 29, 2009

Find primary key of a table in various databases (SQL Server, Oracle and DB2)

SQL Queries to find primary key columns of a table in various databases

1. For SQL Server ==============
select tc.CONSTRAINT_NAME as index_name, kcu.COLUMN_NAME, kcu.ORDINAL_POSITION as column_position from INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME where tc.CONSTRAINT_TYPE = 'PRIMARY KEY' and tc.TABLE_SCHEMA = 'DBO' and tc.TABLE_NAME = 'VARTABLE' Order By index_name asc, column_position asc, column_name asc

2. For Oracle ==========

select all_constraints.index_name, all_ind_columns.column_name, all_ind_columns.column_position from all_constraints, all_ind_columns where all_constraints.owner = all_ind_columns.index_owner and all_constraints.constraint_name = all_ind_columns.index_name and all_constraints.constraint_type = 'P' and all_constraints.owner = 'DBO' and all_constraints.table_name = UPPER('VARTABLE') order by all_constraints.index_name asc, all_ind_columns.column_position asc, all_ind_columns.column_name asc

3. For DB2 =======select RTRIM(a.ixname) as index_name, RTRIM(a.colname) as column_name, a.colseq as column_position from sysibm.syskeys a Inner Join sysibm.sysindexes b on a.ixname = where b.tbname = UPPER('VARTABLE') and b.uniquerule = 'P'order by a.ixname asc, a.colseq asc, a.colname asc;

No comments:

Post a Comment