Reporting on newly created databases

I came up with a quick query that can be run daily to identify newly created databases.  The scenario behind this was that our sandbox environment has gotten out of control.  Many of the databases are completely empty or were created for a quick test.  I’ll run this every night and it will report out only on newly created databases — though that could be changed very easily.  Here is the query:

select

d.objname                                                  database,
u.usename                                                  database_owner,
d.objcreated                                             database_created,
sum(case when o.objid is not null then 1 else 0 end)    total_objects,
sum(case when o.objclass = 4905 then 1 else 0   end)    total_tables,
sum(case when o.objclass = 4906 then 1 else 0   end)    total_views,
sum(case when o.objclass = 4909 then 1 else 0   end)    total_sequences,
sum(case when o.objclass = 4913 then 1 else 0   end)    total_synonyms,
sum(case when o.objclass = 4914 then 1 else 0   end)    total_procedures,
sum(case when o.objclass = 4917 then 1 else 0   end)    total_aggregates,
sum(case when o.objclass = 4919 then 1 else 0   end)    total_functions
from        _t_object d
inner join     _t_user   u on d.objowner = u.usesysid
left  join  _t_object o on o.objdb    = d.objid
where        d.objclass = 4902
and            d.objcreated::date > current_date  -1
group by 1, 2, 3

Advertisements
This entry was posted in administration, General. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

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