Enjoy everyday in our life

I started to use information_schema related views quite often now, so I think it’s about time for me to write a blog about the most common way I use it.

1. Get list of table name from current login database:
   select table_name from INFORMATION_SCHEMA.tables where table_type = ‘BASE TABLE’ order by table_name
    
2. Get list of view name from current login database:
   select table_name from INFORMATION_SCHEMA.tables where table_type = ‘VIEW’ order by table_name
    
3. Get the current login database name:
   select distinct table_catalog from INFORMATION_SCHEMA.tables
    
4. Get list of column name and some important attributes (like data type, nullable) belong to a specific table:
   select table_name,column_name,data_type,is_nullable from INFORMATION_SCHEMA.COLUMNS where table_name =‘fnd_tbl_version’
    
5. Get the detail of a foreign key or primary key for a specific table:
   SELECT k.table_name,k.column_name field_name,c.constraint_type,CASE c.is_deferrable WHEN ‘NO’ THEN 0 ELSE 1 END ‘is_deferrable’,
   CASE c.initially_deferred WHEN ‘NO’ THEN 0 ELSE 1 END ‘is_deferred’, rc.match_option ‘match_type’,rc.update_rule ‘on_update’,rc.delete_rule ‘on_delete’,ccu.table_name ‘references_table’, ccu.column_name ‘references_field’,k.ordinal_position ‘field_position’ FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE k LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS c ON k.table_name = c.table_nameAND k.table_schema = c.table_schema AND k.table_catalog = c.table_catalog AND k.constraint_catalog = c.constraint_catalog AND k.constraint_name = c.constraint_name LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc ON rc.constraint_schema = c.constraint_schema AND rc.constraint_catalog = c.constraint_catalog AND rc.constraint_name = c.constraint_name LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu ON rc.unique_constraint_schema = ccu.constraint_schema AND rc.unique_constraint_catalog = ccu.constraint_catalog AND rc.unique_constraint_name = ccu.constraint_name WHERE k.constraint_catalog = DB_NAME() AND k.table_name = ‘fnd_tbl_user_role_link’
   ORDER BY k.constraint_name,k.ordinal_position
   – I found this query on http://www.alberton.info/sql_server_meta_info.html