DECLARE @SQL NVARCHAR(2000)
DECLARE @filename NVARCHAR(2000)
DECLARE @DB sysname
create table #temp (id int identity(1,1),Alternate_Name varchar(500),
Size bigint,
creationDate int,
creationTime int,
LastWrittenDate int,
LastWrittenTime int,
LastAccessedDate int,
LastAccessedTime int,
Attribute int )
CREATE TABLE #temp1 (
id int identity(1,1),
name varchar(500))
DECLARE curDB CURSOR FOR
select b.name ,a.filename
from sysaltfiles a inner join
sysdatabases b on a.dbid =b.dbid
where a.name not in ('master','mastlog','tempdev','templog','modeldev','modellog','msdbdata','msdblog')
and a.filename like '%.mdf%'
OPEN curDB
FETCH NEXT FROM curDB INTO @db,@filename
WHILE @@FETCH_STATUS = 0
BEGIN
select @SQL ='exec xp_getfiledetails'+''''+@filename+''''
insert into #temp1(name) values (@db)
insert into #Temp (Alternate_Name,Size,creationDate,creationTime,LastWrittenDate,LastWrittenTime,
LastAccessedDate,LastAccessedTime,Attribute) exec sp_executesql @SQL
FETCH NEXT FROM curDB INTO @db,@filename
END
CLOSE curDB
DEALLOCATE curDB
select a.name,b.LastWrittenDate,b.LastAccessedDate from #temp1 a
inner join #temp b
on a.id =b.id
drop table #Temp
drop table #temp1
------------------------------------------------------------------------------------------------------
;;WITH myCTE AS
(
SELECT
DB_NAME(database_id) AS TheDatabase,
last_user_seek,
last_user_scan,
last_user_lookup,
last_user_update
FROM sys.dm_db_index_usage_stats
)
SELECT
ServerRestartedDate = (SELECT CREATE_DATE FROM sys.databases where name='tempdb'),
x.TheDatabase,
MAX(x.last_read) AS last_read,
MAX(x.last_write) AS last_write
FROM
(
SELECT TheDatabase,last_user_seek AS last_read, NULL AS last_write FROM myCTE
UNION ALL
SELECT TheDatabase,last_user_scan, NULL FROM myCTE
UNION ALL
SELECT TheDatabase,last_user_lookup, NULL FROM myCTE
UNION ALL
SELECT TheDatabase,NULL, last_user_update FROM myCTE
) AS x
GROUP BY TheDatabase
ORDER BY TheDatabase asc
SELECT @@ServerName AS server
,NAME AS dbname
,COUNT(STATUS) AS number_of_connections
,GETDATE() AS timestamp
FROM sys.databases sd
LEFT JOIN sys.sysprocesses sp ON sd.database_id = sp.dbid
WHERE database_id NOT BETWEEN 1 AND 4
GROUP BY NAME
ORDER BY NAME asc
--use [ocafinal]
--select MAX(create_date),MAX( modify_date) from sys.objects