Sample SQL – Columns Information

Here is a sample of SQL that queries table column’s information.
Please change the part of <table_name> into the actual table name.

In the case of MY_SQL

select c.table_name,c.column_name , c.is_nullable, column_type
from information_schema.columns c
where table_name='<table_name>' order by ordinal_position

In the case of Postgresql

SELECT  relname,attname,typname,case typname
                when 'timestamp' then 14
                when 'numeric' then (atttypmod - 4) / 65536
                else atttypmod-4
                end as len
                ,case attnotnull
                when 't' then 'not null'
                else '' end as attnotnull
          FROM    pg_class,pg_attribute,pg_type
          WHERE   relkind     ='r'
          AND relname     ='<table_name>'
          AND pg_class.oid = pg_attribute.attrelid
          AND attnum      > 0
          AND pg_type.oid = atttypid
          order by attnum
Share on Facebook
Post to Google Buzz
Bookmark this on Yahoo Bookmark
Bookmark this on Digg
Share on FriendFeed

This entry was posted in Database. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>