Declare @strDBName 
Varchar(50)
Set @strDBName = 'tempdb'

--Declare other variables which are required in the script.
Declare @strScratchPad Varchar(50)
Declare @dtDBCreatedDate Varchar(24)
Declare @strDBOwner Varchar(50)
Declare @intTotalDBSize Varchar(12)
Declare @strSQL Varchar(1000)
Declare @strTotalDataSizeUsed decimal(10,3)
Declare @strTotalLogSize decimal(10,3)
Declare @strTotalLogSizeUsed decimal(10,3)
Declare @dtLastFullBkp Varchar(22)
Declare @dtLastDiffBkp Varchar(22)
Declare @dtLastLogBkp Varchar(22)
Declare @dtLastRestore Varchar(22)
Declare @strLogiName Char(20)
Declare @intTotSize decimal(10,3)
Declare @intUsedSize decimal(10,3)
Declare @strFileName Varchar(100)
Declare @PctLogUsed Integer
Declare @intTotalDatSize decimal(10,3)
Declare @intTotalUsedSize decimal(10,3)
Declare @intTotalFreeSize decimal(10,3)
Declare @intDataSizeFree decimal(4,2)
Set @intTotalDatSize = 0
Set @intTotalUsedSize = 0
Set @intTotalFreeSize = 0
Set NoCount On--Check if the database name provided is a valid database name
Select @strScratchPad = name,@dtDBCreatedDate=crdate,@strDBOwner=suser_sname(sid) 
from master..sysdatabases 
where name=@strDBName 
Print '-----------**********Database Query Analyzer Taskpad V 1.0**********-----------'
If @strScratchPad is Null 
Begin 
Print ''Print 'The Database name provided is not a valid one on this server'
End
Else
Begin--This is a Valid Database
Print ''Print '-----------Database Information-----------'
Print 'Database Name : ' + @strScratchPad
Print 'Database Owner : ' + @strDBOwner
Print 'Date Created : ' + @dtDBCreatedDate--Space Calculations to show the Space Information
Create Table #TempValTable(strAdhocCol Varchar(100))
Create Table #DataFileDetails(FileID Int,FileGr Int,TotExt Numeric,UsedExt Numeric,LogiName Varchar(100),FilName Varchar(255))
Create Table #LogFileDetails(strDBName Varchar(100),LogSize Decimal(10,2),PctUsed Decimal(10,2),Sts Int)
Set @strSQL = 'Insert Into #TempValTable select CONVERT(decimal(10,2),Sum(((size*8)/64)/16)) from [' + @strDBName + '].dbo.sysfiles'
Exec (@strSQL)
Select @intTotalDBSize=strAdhocCol 
from #TempValTable
Print 'Database Size : ' + @intTotalDBSize + ' MB (Data + Log size)'
Insert Into #DataFileDetails 
Exec('use ' + @strDBName + ' DBCC SHOWFILESTATS')
Insert Into #LogFileDetails 
Exec('DBCC SQLPerf(LogSpace)')
Select @strTotalDataSizeUsed=(Sum((UsedExt)*8*8/1024)) 
from #DataFileDetails
Select @strTotalLogSizeUsed=((PctUsed/100.00)*LogSize),@strTotalLogSize=LogSize,@PctLogUsed = PctUsed 
from #LogFileDetails 
where strDBName=@strDBName
Print 'Space Available : ' + Convert(Varchar(15),(@intTotalDBSize - (@strTotalDataSizeUsed + @strTotalLogSizeUsed))) + 'MB (Data + Log Size)'--Show the Backup InformationPrint 
Print ''Print '--------------Maintenance-----------------'
Set @dtLastFullBkp = ' None'
Set @dtLastDiffBkp = ' None'
Set @dtLastLogBkp = ' None'
Set @dtLastRestore = ' None'
select @dtLastFullBkp=backup_finish_date 
from msdb..backupset 
where database_name=@strDBName 
And type='D' 
Order by backup_finish_date Desc
select @dtLastDiffBkp=backup_finish_date 
from msdb..backupset 
where database_name=@strDBName 
And type='I' 
Order by backup_finish_date Desc
select @dtLastLogBkp=backup_finish_date 
from msdb..backupset 
where database_name=@strDBName 
And type='L' 
Order by backup_finish_date Desc
Select @dtLastRestore=restore_date 
from msdb..restorehistory 
where destination_database_name=@strDBName 
Order by restore_date Desc
Print 'Last Full Database Backup : ' + @dtLastFullBkp
Print 'Last Diff Database Backup : ' + @dtLastDiffBkp
Print 'Last Log Database Backup : ' + @dtLastLogBkp
Print 'Last Database Restore : ' + @dtLastRestore--Show Space Allocated Information 
Print ''
Print '--------------Space Allocated-------------'
Print 'Data : '
Print '  Logical File NameSizeUsed SpaceFree SpaceFile Path'
Print '  ------------------------------------------------------------------------------------------------'
DECLARE a_cur cursor for
Select LogiName,((TotExt*8*8)/1024),((UsedExt*8*8)/1024),FilName 
from #DataFileDetailsOPEN a_cur
FETCH NEXT FROM a_cur 
INTO @strLogiName,@intTotSize,@intUsedSize,@strFileName
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE @curCount INT 
SET @curCount = @curCount + 1
DECLARE @DBtoCheck VARCHAR(50)
DECLARE @strFileNameSet VARCHAR(50)
DECLARE @logintoCreate VARCHAR(50)
DECLARE @strRole VARCHAR(50)
Print 'EXEC ' + @DBtoCheck + '..sp_addrolemember ''' + @strRole + ''',''' + @logintoCreate + ''''
--Print ' ' + @strLogiName + '' + Convert(Varchar(10),@intTotSize) + 'MB' + Convert(Varchar(10),@intUsedSize) + 'MB' + Convert(Varchar(10),(@intTotSize-@intUsedSize)) + 'MB ' + @strFileNameSet @intTotalDatSize = @intTotalDatSize + @intTotSizeSet @intTotalUsedSize = @intTotalUsedSize + @intUsedSizeSet @intTotalFreeSize = @intTotalFreeSize + (@intTotSize-@intUsedSize)
FETCH NEXT FROM a_cur 
INTO @strLogiName,@intTotSize,@intUsedSize,@strFileName
--Declare @intDataSizeFree Integer
END
Print '  ------------------------------------------------------------------------------------------------'
Set @intDataSizeFree = ((@intTotalFreeSize/@intTotalDatSize)*100)
Print 'Total:' + Convert(Varchar(12),@intTotalDatSize) + 'MB ' + Convert(Varchar(12),@intTotalUsedSize) + 'MB ' + Convert(Varchar(12),@intTotalFreeSize) + 'MB (' + Convert(Varchar(12),@intDataSizeFree) + '%)'
Print ''
Print 'Transaction Log Space : '
Print '' + Convert(Varchar(12),@strTotalLogSize) + 'MB' + Convert(Varchar(12),@strTotalLogSizeUsed) + 'MB (' + Convert(Varchar(6),@PctLogUsed) + '%)' + Convert(Varchar(12),@strTotalLogSize-@strTotalLogSizeUsed) + 'MB'
CLOSE a_cur
DEALLOCATE a_cur
Drop Table #LogFileDetails
Drop Table #TempValTable
Drop Table #DataFileDetails
End
Print ''
Print '-----------------------------------------------------------------------------------'