Drop Table if Exists

       IF OBJECT_ID(N'CensusReportData', N'U') IS NOT NULL

       DROP TABLE CensusReportData

Database and Logfile Sizes 

Database sizes


SELECT DB_NAME(database_id) AS DatabaseName,

Name AS Logical_Name, Physical_Name, (size*8)/1024 SizeMB FROM sys.master_files

--WHERE DB_NAME(database_id) = 'AdventureWorks'

GO


Logfile sizes

SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
--WHERE DB_NAME(database_id) = 'AdventureWorks'
ORDER BY DB_NAME(database_id)

GO

Log file sizes v2

IF OBJECT_ID(N'master.dbo.TempForLogSpace', N'U') IS NOT NULL

  drop table master.dbo.TempForLogSpace

go

declare @sql_command nvarchar(MAX)

CREATE TABLE master.dbo.TempForLogSpace

(

DBName varchar(MAX),

LogSize real,

LogSpaceUsed real,

Status int

)

SELECT @sql_command = 'dbcc sqlperf (logspace)'

INSERT INTO master.dbo.TempForLogSpace EXEC (@sql_command)

--select * from master.dbo.TempForLogSpace

SELECT DB_NAME(database_id) AS DatabaseName,

sf.[Name] AS Logical_Name,

Physical_Name, (size*8)/1024 SizeMB,tmpLog.LogSpaceUsed ,state_desc,databasepropertyex(DB_NAME(database_id),'Recovery') as [Recovery],tmpLog.[Status]

FROM sys.master_files sf LEFT OUTER JOIN master.dbo.sysdatabases sd ON DB_NAME(database_id)=sf.[name]  LEFT OUTER JOIN master.dbo.TempForLogSpace tmpLog ON DB_NAME(database_id) = tmpLog.DBName

where physical_name like '%ldf%'

--WHERE DB_NAME(database_id) = 'nSightDB'

ORDER BY DB_NAME(database_id)

GO