To expand on James Allen's answer:

SELECT d.name,
last_user_seek = MAX(last_user_seek),
last_user_scan = MAX(last_user_scan),
last_user_lookup = MAX(last_user_lookup),
last_user_update = MAX(last_user_update)
FROM sys.dm_db_index_usage_stats AS i
JOIN sys.databases AS d ON i.database_id=d.database_id
GROUP BY d.name

Use this modified version if you don't want results per database context and wish to include the database name at the beginning of the result set.

SELECT
last_user_seek = MAX(last_user_seek),
last_user_scan = MAX(last_user_scan),
last_user_lookup = MAX(last_user_lookup),
last_user_update = MAX(last_user_update)
FROM
sys.dm_db_index_usage_stats
WHERE
[database_id] = DB_ID()

One caveat with this method is that the information in the DMV will be cleared and nulled whenever you restart SQL Server.

Found this over at MySQLtips - worked for me.

select d.name, x1 =
(select X1= max(bb.xx) 
from (
    select xx = max(last_user_seek) 
        where max(last_user_seek) is not null 
    union all 
    select xx = max(last_user_scan) 
        where max(last_user_scan) is not null 
    union all 
    select xx = max(last_user_lookup) 
        where max(last_user_lookup) is not null 
    union all 
        select xx = max(last_user_update) 
        where max(last_user_update) is not null) bb) 
FROM master.dbo.sysdatabases d 
left outer join 
sys.dm_db_index_usage_stats s 
on d.dbid= s.database_id 
group by d.name

Just come across this post, as was asked this question today... well, was asked which databases are not been used in an instance with quite a few. So, I changed the above query from Andrey Shvidky to write into a temporary table, and then select on the results, comparing anything that has been found with a last used max date, to any remaining databases within sys.databases… the theory being, (and assumption), that these all have NULL last use date and therefore do not show in the results for the main query, and therefore are candidates for further investigation for removal.

SELECT UNPVT.[DataBase], 
       MaxLastUse = MAX(UNPVT.MaxLastUse)
    INTO #tempdatabases
   FROM
    (
       SELECT [DataBase], 
           last_user_seek, 
           last_user_scan, 
           last_user_lookup, 
           last_user_update
      FROM
    (
        SELECT [DataBase] = DB.[name], 
               last_user_seek = MAX(IU.last_user_seek), 
               last_user_scan = MAX(IU.last_user_scan), 
               last_user_lookup = MAX(IU.last_user_lookup), 
               last_user_update = MAX(IU.last_user_update)
        FROM sys.databases AS DB
             INNER JOIN sys.dm_db_index_usage_stats AS IU ON IU.database_id = DB.database_id
        GROUP BY DB.[name]
    ) AS DBIU
) AS P UNPIVOT(MaxLastUse FOR ColumnName IN(last_user_seek, 
                                            last_user_scan, 
                                            last_user_lookup, 
                                            last_user_update)) AS UNPVT
GROUP BY UNPVT.[DataBase]
HAVING MAX(UNPVT.MaxLastUse) IS NOT NULL
ORDER BY MAX(UNPVT.MaxLastUse) ASC;
SELECT name, 
       create_date AS 'Created on', 
       'Not used since last instance start', 
(
    SELECT create_date
    FROM sys.databases
    WHERE database_id = 2
) AS 'Instance start date'
FROM sys.databases
WHERE database_id NOT IN(1, 2, 3, 4)
AND name NOT IN
(
    SELECT [DataBase]
    FROM #tempdatabases
);
DROP TABLE #tempdatabases;

You need to have auditing, or a sql server trace set up ahead of time.

Similar to methods above, but to get result simply as a single value for a particular database id (in this example it's 6 )

select max(LastAccess)
from (
    SELECT last_user_seek as LastAccess FROM sys.dm_db_index_usage_stats WHERE last_user_seek is not null and [database_id]=6
    union
    SELECT last_user_lookup as LastAccess FROM sys.dm_db_index_usage_stats WHERE last_user_seek is not null and [database_id]=6
    union
    SELECT last_user_seek as LastAccess FROM sys.dm_db_index_usage_stats WHERE last_user_seek is not null and [database_id]=6
    union
    SELECT last_user_update as LastAccess FROM sys.dm_db_index_usage_stats WHERE last_user_seek is not null and [database_id]=6
) UserAccess

SQL Server can log event information for logon attempts and you can view it by reviewing the errorlog. By turning on the auditing level of SQL Server.

follow these steps to enable auditing of all/successfull connections with Enterprise Manager in SQL Server:

Expand a server group. Right-click a server, and then click Properties. On the Security tab, under Audit Level, click all/success etc(required option).

You must stop and restart the server for this setting to take effect

Same as above with unpivot

select
    UNPVT.[DataBase]
    ,MaxLastUse     = max(UNPVT.MaxLastUse)
from
   (
    select
        [DataBase], last_user_seek, last_user_scan, last_user_lookup, last_user_update
    from
        (
            select
                [DataBase]          = DB.[name]
                ,last_user_seek     = max(IU.last_user_seek)
                ,last_user_scan     = max(IU.last_user_scan)
                ,last_user_lookup   = max(IU.last_user_lookup)
                ,last_user_update   = max(IU.last_user_update)
            from
                sys.databases as DB
                inner join sys.dm_db_index_usage_stats as IU on
                    IU.database_id = DB.database_id
            group by
                DB.[name]
        ) as DBIU
    ) as P unpivot (MaxLastUse for ColumnName in (last_user_seek, last_user_scan, last_user_lookup, last_user_update)) as UNPVT
group by
    UNPVT.[DataBase]
order by
    UNPVT.[DataBase]

Not easily, but if you turn on the logging feature of SQL Server you can examine the logs using software to discover when the last query was, and what it was.