Thread: System info for Postgre
Hi all,
I am trying to create a system to let my users do some Ad Hoc querying of
their database and I found a nifty program to do it. Unfortunately it was
written with a MS SQL database in mind (I guess) so I have no clue how to
translate the SQL statements that pull tables and columns to one that
PostgreSQL will understand. I've posted it below...can anyone point me in
the right direction for what translates to what? This is the original SQL
statement, which doesn't work in PostgreSQL.
select sysobjects.id as tableid,
syscolumns.id as columnid,
sysobjects.name as tablename,
syscolumns.name as colname,
syscolumns.isnullable,
syscolumns.autoval,
syscolumns.colorder,
syscolumns.length,
systypes.name as typename
from sysobjects,syscolumns,systypes
where sysobjects.id = syscolumns.id and systypes.name <> 'sysname'
and syscolumns.xtype=systypes.xtype
and sysobjects.xtype='U'
and sysobjects.name <> 'dtproperties'
order by tablename,colorder
Thanks,
Summer S. Wilson ICQ 26835530
Programmer/Analyst I, EIT TCE
Webmaster, An Eclectic World (http://eclectic-world.com)
Editor, EZLearnPhotography (http://www.ezlearnphotography.com)
"Summer S. Wilson" <collectonian@eclectic-world.com> writes: > I am trying to create a system to let my users do some Ad Hoc querying of > their database and I found a nifty program to do it. Unfortunately it was > written with a MS SQL database in mind (I guess) so I have no clue how to > translate the SQL statements that pull tables and columns to one that > PostgreSQL will understand. I've posted it below...can anyone point me in > the right direction for what translates to what? Hmm. This looks like it probably translates to a query on PG's pg_class, pg_attribute, and pg_type system catalogs, which you can find documented at http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/catalogs.html but I have no idea where to look for equivalent documentation on MS SQL. BTW, I'd judge that this is emitting something pretty close to the display produced by psql's "\d table". You might care to start psql with -E switch and see what SQL queries it uses to collect the info for \d. It'd be a good starting point anyway. regards, tom lane
Summer, take a look at the tables and views that are prefixed pg_ for the postgres system data. hih steve boyle ----- Original Message ----- From: "Summer S. Wilson" <collectonian@eclectic-world.com> To: <pgsql-novice@postgresql.org> Sent: Friday, January 25, 2002 4:24 PM Subject: [NOVICE] System info for Postgre > Hi all, > > I am trying to create a system to let my users do some Ad Hoc querying of > their database and I found a nifty program to do it. Unfortunately it was > written with a MS SQL database in mind (I guess) so I have no clue how to > translate the SQL statements that pull tables and columns to one that > PostgreSQL will understand. I've posted it below...can anyone point me in > the right direction for what translates to what? This is the original SQL > statement, which doesn't work in PostgreSQL. > > select sysobjects.id as tableid, > syscolumns.id as columnid, > sysobjects.name as tablename, > syscolumns.name as colname, > syscolumns.isnullable, > syscolumns.autoval, > syscolumns.colorder, > syscolumns.length, > systypes.name as typename > from sysobjects,syscolumns,systypes > where sysobjects.id = syscolumns.id and systypes.name <> 'sysname' > and syscolumns.xtype=systypes.xtype > and sysobjects.xtype='U' > and sysobjects.name <> 'dtproperties' > order by tablename,colorder > > Thanks, > > > Summer S. Wilson ICQ 26835530 > Programmer/Analyst I, EIT TCE > Webmaster, An Eclectic World (http://eclectic-world.com) > Editor, EZLearnPhotography (http://www.ezlearnphotography.com) > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >