Database – INFORMATION_SCHEMA – Use Case Scenario

Occasionally, one might find it useful to quickly get “structural” information about databases, tables, and columns.

That is where the INFORMATION_SCHEMA object comes in.  They are part of ANSI Standards.

Each DB Vendor calls them by different names:

  1. Microsoft calls them INFORMATION_SCHEMA
  2. IBM DB/2 and UDB calls them SYSIBM
  3. Oracle calls them Data Dictionary
 

Here are some sample scripts:

Against Microsoft SQL Server, the Query below queries for table & column names

--Get Microsoft SQL Server Tables & columns
select
		  T.table_catalog AS [databaseCatalog]
		, T.TABLE_SCHEMA AS [objectSchema]
		, T.TABLE_NAME AS [objectName]
		, C.COLUMN_NAME AS [columnName]
		, C.DATA_TYPE AS [dataType]
		, C.CHARACTER_MAXIMUM_LENGTH as [maxLength]
		, C.IS_NULLABLE as [isNullable]

FROM INFORMATION_SCHEMA.TABLES T 

JOIN INFORMATION_SCHEMA.COLUMNS C

     ON T.TABLE_SCHEMA = C.TABLE_SCHEMA
     AND T.TABLE_NAME = C.TABLE_NAME 

where   T.TABLE_TYPE in ('BASE TABLE')</pre>

Against IBM/UDB, the Query below queries for table & column names:


select
          CURRENT SERVER as DatabaseName
        , T.CREATOR as schemaName
        , T.NAME as ObjectName
        , C.NAME as ColumnName
        , C.COLTYPE as DataType
        , C.LENGTH as Length
        , C.NULLS as NullAllowed

FROM SYSIBM.SYSTABLES T

JOIN SYSIBM.SYSCOLUMNS C

       ON T.CREATOR = C.TBCREATOR

       AND T.NAME = C.TBNAME

where   T.TYPE in ('T')

And     T.CREATOR not in ('SYSIBM', 'SYSTOOLS', 'BMC')

;

References:

  1. SQL-99 Complete, Really
    http://kb.askmonty.org/en/sql-99-complete-really 
  2. Oracle INFORMATION_SCHEMA
    http://database-geek.com/2009/04/30/oracle-information_schema/ 
  3. Oracle Information Schema Beta by lewiscunningham
    http://sourceforge.net/projects/ora-info-schema/ 
  4. DB2 Catalog tables
    http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2.doc.sqlref%2Frctabls.htm

2 thoughts on “Database – INFORMATION_SCHEMA – Use Case Scenario

  1. The example for Microsoft calls them INFORMATION_SCHEMA was VERY helpful! Thank you! I’m surprised MS hasn’t made this any easier yet with the newer versions.

    • Neale – Thanks for you warm comments. There is really a lot more that could be said about that topic. But, hopefully you have a bit more “actionable” information.

      Thanks for leaving a line or two and Happy SQL Days.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s