Select Column Names or PKs in Table (SQL Server, Oracle, AS400, IBMDB2)

[JOSheaIV]

Hello everyone,

So here's an interesting question for you all. I need to know how to retrieve all the Column Names or Primary Keys from one of the specificed database types: SQL Server, Oracle, AS400, IBMDB2.

Now this has to be as a query, or more specifically I have to be able to write the query string in C# and execute it. I already know how to communicate with each, the problem is, I don't have access to each to test what I have, or figure out what I need. So I am going to post what I have, and then specify what I need. Can people please help me test these to make sure what I do have works, and help with what I can't seem to figure out (or find).

Note: these are all from my C# code, so I use String.Format. If you are unfamiliar with it, replace the {#} (where the # is a numerical value) with the value to the right of the close quotation mark (and comma). They map over sequential. So for the Column name, TableName is the name of the table, and goes where it says {0}

Column Names
SQL Server
String.Format("SELECT COLUMN_NAME AS ColumnName FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='{0}'", TableName);

Oracle
String.Format("SELECT COLUMN_NAME AS ColumnName FROM USER_TAB_COLUMNS WHERE TABLE_NAME='{0}'", TableName);

AS400
String.Format("SELECT COLUMN_NAME AS ColumnName FROM SYSCOLUMNS WHERE TABLE_NAME='{0}'", TableName);

IMBDB2
String.Format("SELECT COLNAME AS ColumnName FROM SYSCAT.COLUMNS WHERE TABNAME='{0}'", TableName);

Primary Keys
SQL Server
String.Format("SELECT column_name AS PrimaryKey FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE OBJECTPROPERTY(OBJECT_ID(constraint_name), 'IsPrimaryKey') = 1 AND table_name = '{0}'", TableName);

Oracle
String.Format("SELECT cols.column_name AS PrimaryKey FROM all_constraints cons, all_cons_columns cols WHERE cols.table_name = '{0}' AND cons.constraint_type = 'P' AND cons.constraint_name = cols.constraint_name AND cons.owner = cols.owner", TableName);

AS400
String.Format("SELECT CONSTRAINT_NAME AS PrimaryKey FROM SYSCST WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = '{0}'", TableName);

IBMDB2
?

So hopefully that all makes sense. I was unable to find how to retrieve all the Primary Keys for an IBMDB2 so could use some help. Also, the rest, I have only been able to test the SQL Server's logic, but not the rest. So if people could help me with confirming these query string will work, or tell me the proper way that would also help.

Thanks