Link to home
Create AccountLog in
Avatar of inthedark
inthedarkFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Getting a list of tables and a selected table schema and Identity field name.

In SQL Server to obtain a list of tables you run a method of the ConnectionObject:

Returns a recordset of tabale names

Set RS = CN.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "Table"))

How can this be done with PostgreSQL?


And to get a table schema into a recordset:

Set RS = CN.OpenSchema(adSchemaColumns, Array(Empty, Empty, "MyTable", Empty))


How can this be done using PostgreSQL?

The schema that is returned is lacking in some important information so if you want to write generic code and you want to know what a table's identity field is you need more sql like this:

SELECT  t.TABLE_NAME, c.name AS COLUMN_NAME, IDENT_SEED(t.TABLE_NAME) As Seed, IDENT_INCR(t.TABLE_NAME) As Increment
 From INFORMATION_SCHEMA.TABLES AS t INNER JOIN sysobjects AS s ON s.name = t.TABLE_NAME
 INNER JOIN syscolumns AS c ON c.id = s.id
 Where ((t.TABLE_NAME='" + TableName + "')
 And (OBJECTPROPERTY(OBJECT_ID(t.TABLE_NAME), 'TableHasIdentity') = 1)
 AND (t.TABLE_TYPE = 'BASE TABLE')) AND c.autoval IS NOT NULL
 
It must be a lot simpler to get the identity field of a table in PostgreSQL?

Thanks in advance......
Avatar of earth man2
earth man2
Flag of United Kingdom of Great Britain and Northern Ireland image

There are loads of object in schema called information_schema that define the database.  To get a list of tables the following will suffice..

select table_name from information_schema.tables;
Please disregard my last remark I misunderstood your posting.
ASKER CERTIFIED SOLUTION
Avatar of grant300
grant300

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of inthedark

ASKER

Sorry for the delay I will start playing with this soon. Thanks for the help.  I wil be awarding points soon.