Answered by: santanukd
Member Since May-2008 | Answered On : May 14th, 2009
Just replace "[your-schema-name-here]" in the following query with your schema name. Hope it comes handy to some of you out there.
Select 'TABLE' as OBJECT_TYPE, TABLE_NAME as OBJECT_NAME, TABLE_SCHEMA as OBJECT_SCHEMA from information_schema.VIEWS
Select 'VIEW' as OBJECT_TYPE, TABLE_NAME as OBJECT_NAME, TABLE_SCHEMA as OBJECT_SCHEMA from information_schema.VIEWS
Select 'INDEX[Type:Name:Table]' as OBJECT_TYPE, concat(CONSTRAINT_TYPE,' : ',CONSTRAINT_NAME,' : ',TABLE_NAME) as OBJECT_NAME,TABLE_SCHEMA AS OBJECT_SCHEMA from information_schema.TABLE_CONSTRAINTS
Select 'Procedure/Functions' as OBJECT_TYPE, ROUTINE_NAME as OBJECT_NAME, ROUTINE_SCHEMA as OBJECT_SCHEMA from information_schema.ROUTINES