--开发服务器判断哪些DB长时间没有使用
DECLARE @lastDDLDate DATETIME
SET @lastDDLDate='2017-01-01'
DECLARE @dbs TABLE (
rowNum INT IDENTITY(1,1) PRIMARY KEY
,dbName NVARCHAR(300)
,lastCreateTime DATETIME
,lastModifyTime DATETIME
,cnt INT
,statistic_time DATETIME
,last_user_update DATETIME
,last_user_seek DATETIME
,last_user_scan DATETIME
,last_user_lookup DATETIME
)
INSERT INTO @dbs(dbName)
SELECT NAME FROM sys.databases AS d WHERE d.name NOT IN ('tempdb','master','model','msdb')
DECLARE @i INT,@imax INT,@sql NVARCHAR(MAX),@dbName NVARCHAR(300),@cnt INT
,@lastCreateTime DATETIME
,@lastModifyTime DATETIME
,@statistic_time DATETIME
,@last_user_update DATETIME
,@last_user_seek DATETIME
,@last_user_scan DATETIME
,@last_user_lookup DATETIME
SELECT @i=1,@imax=MAX(rowNum) FROM @dbs
WHILE @i<=@imax
BEGIN
SELECT @dbName=dbName FROM @dbs WHERE rowNum=@i
SET @sql='use '+@dbName+'
SELECT @cnt=count(1) FROM sys.objects AS o WHERE o.create_date>@lastDDLDate OR o.modify_date>@lastDDLDate'
EXEC sp_executesql @sql,N'@lastDDLDate datetime,@cnt int out',@lastDDLDate,@cnt OUT
SET @sql='use '+@dbName+'
SELECT @lastCreateTime=max(create_date),@lastModifyTime=max(modify_date) FROM sys.objects AS o'
EXEC sp_executesql @sql,N'@lastCreateTime datetime out,@lastModifyTime datetime out',@lastCreateTime out,@lastModifyTime OUT
SET @sql='use '+@dbName+'
SELECT @statistic_time=max(STATS_DATE(ixu.object_id, ixu.index_id)) ,
@last_user_update=max(ixu.last_user_update) ,
@last_user_seek=max(ixu.last_user_seek) ,
@last_user_scan=max(ixu.last_user_scan) ,
@last_user_lookup=max(ixu.last_user_lookup)
FROM sys.dm_db_index_usage_stats ixu
INNER JOIN sys.indexes ix
ON ixu.object_id = ix.object_id
AND ixu.index_id = ix.index_id
INNER JOIN sys.objects ob
ON ixu.object_id = ob.object_id
WHERE ob.type = ''U''
AND ob.is_ms_shipped = 0
AND ixu.database_id = DB_ID()
'
EXEC sp_executesql @sql,N'@statistic_time DATETIME OUT
,@last_user_update DATETIME OUT
,@last_user_seek DATETIME OUT
,@last_user_scan DATETIME OUT
,@last_user_lookup DATETIME OUT',@statistic_time OUT
,@last_user_update OUT
,@last_user_seek OUT
,@last_user_scan OUT
,@last_user_lookup OUT
UPDATE @dbs SET
cnt = @cnt
,lastCreateTime =@lastCreateTime
,lastModifyTime =@lastModifyTime
,statistic_time =@statistic_time
,last_user_update=@last_user_update
,last_user_seek=@last_user_seek
,last_user_scan =@last_user_scan
,last_user_lookup=@last_user_lookup
WHERE rowNum=@i
SET @i=@i+1
END
SELECT * FROM @dbs ORDER BY lastModifyTime ASC
