Sys.Databases has some create information, but I was looking for a way to track aging, last access, and if databases got dropped. In a development environment, I was hoping this might help me gauge which development databases were actually being used or not.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
|
/*******************************************************
run check on each constraint to evaluate if errors
*******************************************************/
if object_id('tempdb..##CheckMe') is not null
drop table ##CheckMe;
select
temp_k = identity(int, 1, 1)
,X.*
into ##CheckMe
from
(select
type_of_check = 'FK'
,'[' + s.name + '].[' + o.name + '].[' + i.name + ']' as keyname
,CheckMe = 'alter table ' + quotename(s.name) + '.' + quotename(o.name) + ' with check check constraint ' + quotename(i.name)
,IsError = convert(bit, null)
,ErrorMessage = convert(varchar(max), null)
from
sys.foreign_keys i
inner join sys.objects o
on i.parent_object_id = o.object_id
inner join sys.schemas s
on o.schema_id = s.schema_id
where
i.is_not_trusted = 1
and i.is_not_for_replication = 0
union all
select
type_of_check = 'CHECK'
,'[' + s.name + '].[' + o.name + '].[' + i.name + ']' as keyname
,CheckMe = 'alter table ' + quotename(s.name) + '.' + quotename(o.name) + ' with check check constraint ' + quotename(i.name)
,IsError = convert(bit, null)
,ErrorMessage = convert(varchar(max), null)
from
sys.check_constraints i
inner join sys.objects o
on i.parent_object_id = o.object_id
inner join sys.schemas s
on o.schema_id = s.schema_id
where
i.is_not_trusted = 1
and i.is_not_for_replication = 0
and i.is_disabled = 0) as X
|
Webmentions
(No webmentions yet.)