开发服务器判断哪些DB长时间没有使用

--开发服务器判断哪些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