/****** Object:  View [dbo].[Database File]    Script Date: 02/06/2013 21:18:56 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[Database File]'))
DROP VIEW [dbo].[Database File]
GO

/****** Object:  View [dbo].[Database File]    Script Date: 02/06/2013 21:18:57 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[Database File] AS SELECT [fileid] AS "No_",CAST(RTRIM([filename]) AS NVARCHAR(250)) COLLATE Latin1_General_100_CI_AS AS "File Name",[size]*8 AS "Size (KB)" FROM [dbo].[sysfiles] WHERE FILEPROPERTY([name], 'IsLogFile') = 0
GO


/****** Object:  View [dbo].[Session]    Script Date: 02/06/2013 21:18:43 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[Session]'))
DROP VIEW [dbo].[Session]
GO

/****** Object:  View [dbo].[Session]    Script Date: 02/06/2013 21:18:44 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW     [dbo].[Session] AS            SELECT          CAST(SP.[spid] AS INTEGER) AS "Connection ID",CAST(RTRIM(SP.[loginame]) AS NVARCHAR(64)) COLLATE Latin1_General_100_CI_AS AS "User ID",CAST(CASE WHEN SP.[spid] = @@SPID THEN 1 ELSE 0 END AS TINYINT) AS "My Session",CONVERT(DATETIME, '1754-01-01 '+CONVERT(CHAR(8), SP.[login_time], 108), 120) AS "Login Time",CONVERT(DATETIME, CONVERT(CHAR(10), SP.[login_time], 120)+' 00:00:00:000', 121) AS "Login Date",CAST (SD.[name] AS NVARCHAR(128)) COLLATE Latin1_General_100_CI_AS AS "Database Name",CAST (RTRIM(SP.[program_name]) AS NVARCHAR(64)) COLLATE Latin1_General_100_CI_AS AS "Application Name",CASE WHEN SP.[nt_domain] <> '' THEN 1 ELSE 0 END AS "Login Type",CAST(RTRIM(SP.[hostname]) AS NVARCHAR(64)) COLLATE Latin1_General_100_CI_AS AS "Host Name",SP.[cpu] AS "CPU Time (ms)",CASE WHEN SP.[memusage] < 0 THEN 0 ELSE SP.[memusage]*8 END AS "Memory Usage (KB)",SP.[physical_io] AS "Physical I_O",CAST(CASE WHEN SP.[blocked] <> 0 THEN 1 ELSE 0 END AS TINYINT) AS "Blocked",CAST(CASE WHEN SP.[blocked] <> 0 THEN SP.[waittime] ELSE 0 END AS INTEGER) AS "Wait Time (ms)",CAST(SP.[blocked] AS INTEGER) AS "Blocking Connection ID",CAST(ISNULL(RTRIM(SPB.[loginame]), '') AS NVARCHAR(64)) COLLATE Latin1_General_100_CI_AS AS "Blocking User ID",CAST(ISNULL(RTRIM(SPB.[hostname]), '') AS NVARCHAR(64)) COLLATE Latin1_General_100_CI_AS AS "Blocking Host Name",CAST('' AS NVARCHAR(64)) COLLATE Latin1_General_100_CI_AS AS "Blocking Object",CASE WHEN SP.[cmd] = 'AWAITING COMMAND' THEN CAST(DATEDIFF(SECOND, SP.[last_batch], GETDATE()) AS BIGINT)*1000 ELSE 0 END AS "Idle Time"            FROM            [master].[dbo].[sysprocesses] AS SP            JOIN            [master].[dbo].[sysdatabases] AS SD ON (SP.[dbid] = SD.[dbid])            LEFT OUTER JOIN [master].[dbo].[sysprocesses] AS SPB ON (SP.[blocked] = SPB.[spid])            WHERE           SP.[ecid] = 0 AND SP.[last_batch] >= CONVERT(DATETIME, '2000-01-01 00:00:00', 120)
GO


