SQL

January 22, 2015
Research
0 Comments
paris

Shows all the databases of a server and the last time they were accessed

SELECT DatabaseName, MAX(LastAccessDate) LastAccessDate
FROM
    (SELECT
        DB_NAME(database_id) DatabaseName
        , last_user_seek
        , last_user_scan
        , last_user_lookup
        , last_user_update
    FROM sys.dm_db_index_usage_stats) AS PivotTable
UNPIVOT 
    (LastAccessDate FOR last_user_access IN
        (last_user_seek
        , last_user_scan
        , last_user_lookup
        , last_user_update)
    ) AS UnpivotTable
GROUP BY DatabaseName
HAVING DatabaseName NOT IN ('master', 'tempdb', 'model', 'msdb')
ORDER BY 2

Shows all the current database connections currently

Source code

SELECT @@ServerName AS server,NAME AS dbname,COUNT(STATUS) AS number_of_connections,GETDATE() AS timestampFROM sys.DATABASES sdLEFT JOIN sysprocesses sp ON sd.database_id = sp.dbidWHERE database_id NOT BETWEEN 1 AND 4GROUP BY NAMEORDER BY 3

(No Ratings Yet)