In SQL server 2008 we can retrieve all objects in a database with a very simple query
Query:
select
name as 'Name',
object_id as 'Object ID',
principal_id as 'Principal ID',
schema_id as 'Schema ID',
parent_object_id as 'Object ID',
type as 'Type',
type_desc as 'Type Description',
create_date as 'Create date',
modify_date as 'Modify date',
is_ms_shipped as 'Shipped',
is_published as 'Published'
from sys.objects
Results:
sysrowsetcolumns 4 NULL 4 0 S SYSTEM_TABLE 1 0
sysallocunits 7 NULL 4 0 S SYSTEM_TABLE 1 0
syshobtcolumns 13 NULL 4 0 S SYSTEM_TABLE 1 0
Area ********** NULL 1 0 U USER_TABLE 0 0
Settings ********** NULL 1 0 U USER_TABLE 0 0
Roles ********** NULL 1 0 U USER_TABLE 0 0
Customers ********** NULL 1 0 U USER_TABLE 0 0
Using this functionality we can easily get all objects created by any user in any database.
