In System .Objects is an object which returns informationn regarding Databases
Like
'USER_TABLE'
'SYSTEM_TABLE'
'INTERNAL_TABLE'
'SQL_STORED_PROCEDURE'
'SQL_SCALAR_FUNCTION'
'DEFAULT_CONSTRAINT'
'PRIMARY_KEY_CONSTRAINT'
'SERVICE_QUEUE'
So Following querry will explain how we can get these informative property values.
Select name as 'Table Name', create_date as 'Creation Date', modify_date as 'Modification Date',
is_published as 'Is Published', is_schema_published as 'Is Schema Published',
type_desc as 'Type Of Object'
from sys.objects
where type_desc= 'USER_TABLE'
or type_desc= 'SYSTEM_TABLE'
or type_desc= 'INTERNAL_TABLE'
or type_desc= 'SQL_STORED_PROCEDURE'
or type_desc= 'SQL_SCALAR_FUNCTION'
or type_desc= 'DEFAULT_CONSTRAINT'
or type_desc= 'PRIMARY_KEY_CONSTRAINT'
or type_desc= 'SERVICE_QUEUE'
Results
spt_fallback_db 2003-04-08 09:18:01.557 2005-10-14 02:02:31.550 0 0 USER_TABLE
spt_fallback_dev 2003-04-08 09:18:02.870 2005-10-14 02:02:31.567 0 0 USER_TABLE
spt_fallback_usg 2003-04-08 09:18:04.180 2005-10-14 02:02:31.567 0 0 USER_TABLE
spt_monitor 2005-10-14 01:53:52.320 2005-10-14 02:02:31.583 0 0 USER_TABLE
spt_values 2005-10-14 01:53:52.867 2005-10-14 02:02:31.600 0 0 USER_TABLE
MSreplication_options 2005-10-14 02:00:58.427 2005-10-14 02:02:31.613 0 0 USER_TABLE