NOTE TO SELF: SQL Find inactive DBs

/*
Find DB activity for known list of DBs

ALL CREDIT goes to:
http://sqlblog.com/blogs/aaron_bertrand/archive/2008/05/06/when-was-my-database-table-last-accessed.aspx

I merely tweaked for my own needs

-- You can adjust the script to select from sys.databases WHERE Name in (....). But 
-- as for me I like to keep a second listing in a management DB 

  CREATE TABLE [DBs]
    (
     [UID] [int] NOT NULL
                 IDENTITY(1, 1) NOT FOR REPLICATION
   , [DBID] [int] NULL
   , [DBName] [nvarchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS
                              NOT NULL
   , [Type] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS
                          NOT NULL
   , [DateCreated] [datetime]
        NOT NULL
        CONSTRAINT [DF_DBs_DateCreated] DEFAULT (GETDATE()))
  ON
    [PRIMARY]

*/
DECLARE
		@DBName VARCHAR(128) --Database name	  
	  , @SQL VARCHAR(8000)	 
	  , @DBCount INT
	  , @DBCounter INT

 IF OBJECT_ID('tempdb..#tmpDBs') IS NOT NULL 
		DROP TABLE #tmpDBs

IF OBJECT_ID('tempdb..##tmpDBActivity') IS NOT NULL 
		DROP TABLE ##tmpDBActivity

CREATE TABLE ##tmpDBActivity(DBName sysname,last_read DATETIME, last_write DateTime)

SELECT
		ROW_NUMBER() OVER (ORDER BY DBName ASC) IDX
	  , DBName 
	 INTO
		#tmpDBs
	 FROM
		ManageDBs.dbo.DBs
				
SELECT @DBCount = @@ROWCOUNT, @DBCounter = 1
WHILE @DBCounter <= @DBCount
	BEGIN
		SELECT @DBName = DBName FROM #tmpDBs Where IDX = @DBCounter	
		
		SELECT @SQL = '
USE ' + QUOTENAME(@DBName,'[') + '
;WITH  agg AS
(
    SELECT
        last_user_seek,
        last_user_scan,
        last_user_lookup,
        last_user_update
    FROM
        sys.dm_db_index_usage_stats
    WHERE
        database_id = DB_ID()
        AND OBJECT_SCHEMA_NAME([object_id]) = ' + QUOTENAME('dbo',CHAR(39)) + '
)
INSERT INTO ##tmpDBActivity
SELECT
	DB_NAME(DB_ID()) DBName
    ,last_read = MAX(last_read)
    ,last_write = MAX(last_write)
FROM
(
    SELECT  last_user_seek, NULL  FROM  agg
    UNION  ALL
    SELECT  last_user_scan, NULL  FROM  agg
    UNION  ALL
    SELECT  last_user_lookup, NULL  FROM  agg
    UNION  ALL
    SELECT  NULL, last_user_update FROM  agg
) AS  x (last_read, last_write);'
	EXEC (@SQL)
	SELECT @DBCounter = @DBCounter+1
	
	END

SELECT     
	DBName
  , last_read
  , last_write 
  , CASE WHEN last_read IS NULL AND last_write IS NULL 
		THEN 'INACTIVE' ELSE '' END STATUS
FROM 
	##tmpDBActivity	
ORDER BY last_read ASC

DROP TABLE ##tmpDBActivity

Results:

DBName	last_read	last_write	STATUS
CheeseAndRiceCo	NULL	NULL	INACTIVE
ChickenButtLLC	NULL	NULL	INACTIVE
FranksNBeansLTD	2011-05-11 10:48:28.197	NULL
BobCo	2011-06-09 10:46:30.923	NULL
BadSeafood	2011-07-07 12:01:21.137	2011-07-06 16:39:07.253
ManageDBs	2011-08-23 19:01:46.510	2011-08-23 19:01:46.510