select i.relname as index_name, a.attname as column_name from pg_class t, pg_class i, pg_index ix, pg_attribute a, pg_namespace n where t.oid = ix.indrelid and i.oid = ix.indexrelid and a.attrelid = t.oid and a.attnum = ANY(ix.indkey) and n.oid=t.relnamespace and t.relkind = 'r' and ... Read more
Author Archives: Shiji Pan
How to get index column names of a table from MySQL
SELECT INDEX_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'my_schema_name' AND TABLE_NAME = 'my_table_name' See also: How to get index column names of a table from DB2 How to get index column names of a table from SQL Server How to get index column names of a table ... Read more
How to get index column names of a table from SQL Server
select i.name as IndexName, co.[name] as ColumnName from sys.indexes i join sys.objects o on i.object_id = o.object_id join sys.schemas s on s.schema_id = o.schema_id join sys.index_columns ic on ic.object_id = i.object_id and ic.index_id = i.index_id join sys.columns co on co.object_id = ... Read more
How to get index column names of a table from DB2
select name,colnames from sysibm.sysindexes where tbname='my_table_name' See also: How to get index column names of a table from SQL Server How to get index column names of a table from MySQL How to get index column names of a table from PostgreSQL How to get index column names of a table from ... Read more
How to get primary key of a table from Oracle
SELECT cols.column_name FROM all_constraints cons, all_cons_columns cols WHERE cons.constraint_type = 'P' AND cons.constraint_name = cols.constraint_name AND cons.table_name = cols.table_name AND cons.owner = cols.owner AND cons.table_name='my_table_name' AND cons.owner='my_schema_name' See ... Read more
How to get primary key of a table from DB2
SELECT COLNAME FROM syscat.COLUMNS WHERE tabschema='my_schema_name' AND tabname='my_table_name' AND KEYSEQ=1 See also: How to get primary key of a table from PostgreSQL How to get primary key of a table from SQLite How to get primary key of a table from MySQL How to get primary key of a table ... Read more
How to get primary key of a table from SQL Server
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + CONSTRAINT_NAME), 'IsPrimaryKey') = 1 AND TABLE_NAME = 'my_table_name' AND TABLE_SCHEMA = 'my_schema_name' See also: How to get primary key of a table from PostgreSQL How to get ... Read more
How to get primary key of a table from MySQL
select COLUMN_NAME,INDEX_NAME from INFORMATION_SCHEMA.STATISTICS where TABLE_SCHEMA = 'my_schema_name' and TABLE_NAME='my_table_name' and INDEX_NAME='PRIMARY' See also: How to get primary key of a table from PostgreSQL How to get primary key of a table from SQLite How to get primary key of a ... Read more
How to get primary key of a table from SQLite
Run PRAGMA table_info('my_table_name') find the record that column ‘pk’ is 1, it’s the primary key. See also: How to get primary key of a table from PostgreSQL How to get primary key of a table from MySQL How to get primary key of a table from SQL Server How to get primary key ... Read more
How to get primary key of a table from PostgreSQL
SELECT pg_attribute.attname, format_type(pg_attribute.atttypid, pg_attribute.atttypmod) FROM pg_index, pg_class, pg_attribute, pg_namespace WHERE pg_class.oid = 'my_table_name'::regclass AND indrelid = pg_class.oid AND nspname = 'my_schema_name' AND pg_class.relnamespace = pg_namespace.oid ... Read more