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')

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

/*
  - IBM/UDB get table & column names
  SYSIBM.SYSTABLES
  http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2.doc.sqlref%2Fztabls.htm

  SYSIBM.SYSCOLUMNS
  http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2.doc.sqlref%2Fztabls.htm

*/

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s