Kmallikarjuna.files.wordpress.com



--------------------------------*******************---------------------------------------CREATE TABLES-----------------------------------CREATE TABLE #DATABASE_NAME(name nvarchar(128),-- Database name. db_size nvarchar(13),-- Total size of the database. owner nvarchar(128),-- Database owner (such as sa). dbid smallint,-- Numeric database ID. created char(11),-- Date the database was created. status varchar(340),-- Comma-separated list of valuescompatibility_level tinyint-- Database compatibility level )CREATE TABLE #fixeddrives(drive nvarchar(128),-- Drive Letters. [MB Free] nvarchar(13),-- Total free space of the drive. )---------------------INSERT DATA FROM SYSTEM STORED PROCEDURES-------INSERT INTO #DATABASE_NAME EXEC sp_helpdbINSERT INTO #fixeddrives EXEC XP_fixeddrives----------------------DATABASE PIVOT AND CONVERSION------------------DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX);SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.dbid) FROM #DATABASE_NAME c FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'')set @query = 'SELECT ''name'' as [Column], ' + @cols + ' from ( select name, dbid from #DATABASE_NAME ) X pivot ( max(name)for dbid in (' + @cols + ') ) P UNION SELECT ''db_size_MB'' as [Column], ' + @cols + ' from ( select dbid, REPLACE(db_size,''MB'','''') as db_size_MB from #DATABASE_NAME ) X pivot ( max(db_size_MB)for dbid in (' + @cols + ') ) P ORDER BY [Column] DESC '--------------------------DRIVE/DISK FREE SPACE PIVOT AND CONVERSION---------------------------SQL SERVER 2005----------------------------DECLARE @cols1 AS NVARCHAR(MAX), @query1 AS NVARCHAR(MAX);SET @cols1 = STUFF((SELECT distinct ',' + QUOTENAME(c.drive) FROM #fixeddrives c FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') set @query1 = 'SELECT ''FREE_GB'' as [Drive], ' + @cols1 + ' from ( select CAST((SUM(CAST([MB Free] AS Numeric(15,2)))/1024) AS NUMERIC(15,2)) as FREE_GB , drive from #fixeddrives group by drive ) X pivot ( max(FREE_GB)for drive in (' + @cols1 + ') ) P ORDER BY [Drive] DESC ' --------------------------DRIVE/DISK FREE SPACE PIVOT AND CONVERSION---------------------------SQL SERVER 2008/2008R2/2012----------------declare @chkCMDShell as sql_variantdeclare @chkCMDShell1 as sql_variantselect @chkCMDShell = value from sys.configurations where name = 'xp_cmdshell'select @chkCMDShell1 = value from sys.configurations where name = 'xp_cmdshell'if @chkCMDShell = 0begin EXEC sp_configure 'xp_cmdshell', 1 RECONFIGURE;endelsebegin Print 'xp_cmdshell is already enabled'enddeclare @svrName varchar(255)declare @sql varchar(400)set @svrName = CONVERT(VARCHAR(128),SERVERPROPERTY('machinename') )--@@SERVERNAMEset @sql = 'powershell.exe -c "Get-WmiObject -ComputerName ' + QUOTENAME(@svrName,'''') + ' -Class Win32_Volume -Filter ''DriveType = 3'' | select name,capacity,freespace | foreach{$_.name+''|''+$_.capacity/1048576+''%''+$_.freespace/1048576+''*''}"'--creating a temporary tableCREATE TABLE #output(line varchar(255))--inserting disk name, total space and free space value in to temporary tableinsert #outputEXEC xp_cmdshell @sql--script to retrieve the values in GB in PivotCREATE TABLE #OUTPUT2(DRIVENAME NVARCHAR (255),TOTAL_DISK_SIZE NUMERIC (15,2),freespace_GB NUMERIC (15,2),)INSERT INTO #OUTPUT2 SELECT rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as drivename ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1, (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float)/1024,0) as TOTAL_DISK_SIZE ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1, (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float) /1024 ,0)as freespace_GBfrom #outputwhere line like '[A-Z][:]%'DECLARE @cols2 AS NVARCHAR(MAX), @query2 AS NVARCHAR(MAX);SET @cols2 = STUFF((SELECT distinct ',' + QUOTENAME(c.drivename) FROM #output2 c FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'')set @query2 = 'SELECT ''TOTAL_DISK_SIZE'' as [TOTAL_DISK_SIZE], ' + @cols2 + ' from ( select TOTAL_DISK_SIZE, drivename from #output2 ) X pivot ( max(TOTAL_DISK_SIZE)for drivename in (' + @cols2 + ') ) P UNION SELECT ''FREESPACE_GB'' as [FREESPACE_GB], ' + @cols2 + ' from ( select freespace_GB, drivename from #output2 ) X pivot ( max(FREESPACE_GB)for drivename in (' + @cols2 + ') ) P ORDER BY TOTAL_DISK_SIZE DESC '-------if @chkCMDShell1 = 0begin EXEC sp_configure 'xp_cmdshell', 0 RECONFIGURE; Print 'xp_cmdshell is Disabled'endelsebegin Print 'xp_cmdshell is enabled as before'end---------------------------EXECUTE SCRIPTS--------------------------- execute(@query)execute(@query1)execute(@query2)---------------------------DROP TEMP TABLES--------------------------DROP TABLE #DATABASE_NAMEDROP TABLE #fixeddrivesdrop table #outputdrop table #output2--------------------------------*******************------------------ ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download