RE: QA-Taskpad

SQL_ME_RICH — Wed, 02 Mar 2011 13:30:02 GMT

I decided to reformat this script (more out of curiosity than need), but please let the code in the initial post be a lesson to all - this is how to NOT format your code for sharing out here. What a headache!Not that mine is much better, but I have revised it so it will at least work. There are a couple of linescommented out (because frankly, I wasn't able to completely figure out what the code was trying to do - maybe someone else can make better sense of it now), as it was causing the script not to run.Here's a reformatting of the code:[code="sql"]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 IntegerDeclare @intTotalDatSize decimal(10,3)Declare @intTotalUsedSize decimal(10,3)Declare @intTotalFreeSize decimal(10,3)Declare @intDataSizeFree decimal(4,2)Set @intTotalDatSize = 0Set @intTotalUsedSize = 0Set @intTotalFreeSize = 0Set NoCount On--Check if the database name provided is a valid database nameSelect @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'EndElseBegin--This is a Valid DatabasePrint ''Print '-----------Database Information-----------'Print 'Database Name : ' + @strScratchPadPrint 'Database Owner : ' + @strDBOwnerPrint 'Date Created : ' + @dtDBCreatedDate--Space Calculations to show the Space InformationCreate 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 #TempValTablePrint '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 #DataFileDetailsSelect @strTotalLogSizeUsed=((PctUsed/100.00)*LogSize),@strTotalLogSize=LogSize,@PctLogUsed = PctUsed from #LogFileDetails where strDBName=@strDBNamePrint '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 Descselect @dtLastDiffBkp=backup_finish_date from msdb..backupset where database_name=@strDBName And type='I' Order by backup_finish_date Descselect @dtLastLogBkp=backup_finish_date from msdb..backupset where database_name=@strDBName And type='L' Order by backup_finish_date DescSelect @dtLastRestore=restore_date from msdb..restorehistory where destination_database_name=@strDBName Order by restore_date DescPrint 'Last Full Database Backup : ' + @dtLastFullBkpPrint 'Last Diff Database Backup : ' + @dtLastDiffBkpPrint 'Last Log Database Backup : ' + @dtLastLogBkpPrint '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 forSelect LogiName,((TotExt*8*8)/1024),((UsedExt*8*8)/1024),FilName from #DataFileDetailsOPEN a_curFETCH NEXT FROM a_cur INTO @strLogiName,@intTotSize,@intUsedSize,@strFileNameWHILE (@@FETCH_STATUS = 0)BEGINDECLARE @curCount INT SET @curCount = @curCount + 1DECLARE @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 IntegerENDPrint ' ------------------------------------------------------------------------------------------------'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_curDEALLOCATE a_curDrop Table #LogFileDetailsDrop Table #TempValTableDrop Table #DataFileDetailsEndPrint ''Print '-----------------------------------------------------------------------------------'[/code]I'm not real big on cursors, and have been discouraged from using them any longer in favor of better methods, but for anyone with a curiosity behind this Taskpad - enjoy.:w00t:

QA-Taskpad

Ganesh-260702 — Fri, 05 Sep 2008 11:24:52 GMT

Comments posted to this topic are about the item [B]QA-Taskpad[/B]

SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Ganesh Pittala / QA-Taskpad / Latest Posts

RE: QA-Taskpad

QA-Taskpad