Query all objects created or modified after specified date
There are occasions, such as following a deployment or while troubleshooting an unexpected issue, when it's useful to return a list of any objects recently modified. By default this script will query across all databases and return any objects created or modified in the past 24 hours. However, you can also provide an alternate value for @db_name_phrase or @modify_date to narrow the search.
set nocount on;
if object_id('tempdb..#t') is not null
drop table #t;
go
declare @db_name_phrase varchar(180);
declare @modify_date varchar(30);
-- To narrow search result, you may edit criteria here:
set @db_name_phrase = '%';
set @modify_date = dateadd (hour,-24,getdate());
--------
create table #t
(
databasename varchar(180),
schema_name varchar(180),
parent_name varchar(180),
object_name varchar(180),
type_desc varchar(180),
create_date datetime,
modify_date datetime
);
begin;
declare @db_name varchar(180); set @db_name = '';
declare @sql varchar(8000); set @sql = '';
while @db_name is not null
begin;
begin try;
select top 1 @db_name = name
from sys.databases
where name like @db_name_phrase
and name > @db_name
order by name;
if @@rowcount = 0
break;
select @sql = '
use ' + @db_name + ';
select db_name() as databasename,
schema_name(schema_id) as schema_name
, object_name(parent_object_id) parent_name
, name as object_name
,type_desc
,create_date
,modify_date
from sys.objects
where substring(name,1,1) != ''#''
and substring(isnull(object_name(parent_object_id),''''),1,1) != ''#''
and modify_date >= ''' + @modify_date + '''
order by db_name(), schema_name(schema_id), name;
';
print @sql;
insert into #t
(databasename,schema_name,parent_name,object_name,type_desc,create_date,modify_date)
exec (@sql);
end try
begin catch;
print 'Error: ' + cast(error_number() as varchar) + ': ' + error_message();
end catch;
end;
end;
select *
from #t
order by modify_date desc, create_date desc; 
