Category Archives: InsideDatabases

Scripts to List Space

Growth of Database

The following script provides an educated guess regarding database growth

It does this by examining the size of backups. It queries MSDB for backup sizes, calculated the difference, divided by the number of days in between, and finally averages the difference.

/*
DESCRIPTION    Estimate Database growth based on backups.

Assuming backup size grows at the same rate as the Database size.  
First calculate difference in size between backups,
and then divides it by the number of days the backup took place
Finaly, averages the result.
CONFIGURATION    
    Set @LastXday to limit the number of days to search for
    Set @dbname for the database
 
Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2012

 */ 
declare @dbname varchar(500)
declare @LastXdays nvarchar(2)
set @dbname = 'AdventureWorks'
set @LastXdays = '150'

;WITH BK_Growth (BKsize,BKDate,rownumber) as(  
	SELECT
		 backup_size
		,backup_start_date
		,row_number() OVER 
				(ORDER BY database_name,
				[type], backup_start_date DESC)
				AS rownumber
	FROM msdb.dbo.backupset
	WHERE
		database_name like @dbname
		and DATEDIFF(dd,([backup_finish_date]),GETDATE()) < @LastXdays
		and type = 'D'
)
select 
@dbname 'dbname'
,CAST( CAST(avg(100*((
(prev.BKSize - cur.BKSize)/cur.BKSize)/(DATEDIFF(dd,cur.BKDate,Prev.BKDate))
)) as NUMERIC(5,3))
as VARCHAR(5))+' %' 'Average Daily Growth'

,     (CAST(mfrows.RowSize AS FLOAT)*8)/1024 RowSizeMB
,     (CAST(mflog.LogSize AS FLOAT)*8)/1024 LogSizeMB
 
from BK_Growth cur
inner join BK_Growth prev on CUR.rownumber = (PREV.rownumber+1)

cross apply sys.databases db
    LEFT JOIN (
    SELECT database_id, SUM(size) RowSize 
    FROM sys.master_files 
    WHERE type = 0 and database_id = DB_ID(@DBName)
    GROUP BY database_id, type
        ) mfrows ON mfrows.database_id = db.database_id
    
    LEFT JOIN (
    SELECT database_id, SUM(size) LogSize 
    FROM sys.master_files 
    WHERE type = 1  and database_id = DB_ID(@DBName)
    GROUP BY database_id, type
        ) mflog ON mflog.database_id = db.database_id

WHERE mfrows.RowSize is not null
GROUP BY mfrows.RowSize, mflog.LogSize        

 

Data File Size and Free Space – One Database

This script lists file size and how full the data files are for a single Database.

 

/*
DESCRIPTION    List space on a single database and location of its files

CONFIGURATION    Top. Modify the "use" statement
 
Compatibility list:
MSSQL2005
MSSQL2008
 
 
*/


USE Adventure_Works_2012

SELECT  
b.name
, b.type_desc
, CAST(b.size/128.0 AS int) 'SizeMB'  
, CAST(b.size/128.0 - CAST(FILEPROPERTY(b.name, 'SpaceUsed' ) AS int)/128.0 AS int) 'SpaceUsedMB'  
, CAST(ceiling(100 * (b.size/128.0 -CAST(FILEPROPERTY(b.name,'SpaceUsed' ) AS int)/128.0)/(b.size/128.0)) AS varchar(8)) + '%'  'FreeSpace'

,  CASE
	WHEN b.is_percent_growth = 0 THEN LTRIM(STR(b.growth * 8.0 / 1024,10,1)) + ' MB, '  
    ELSE 'By ' + CAST(b.growth AS VARCHAR) + ' percent, '
 END
 + CASE  
    WHEN b.max_size = -1 THEN 'unrestricted growth'
	ELSE 'restricted growth to ' + LTRIM(STR(b.max_size * 8.0 / 1024,10,1)) + ' MB'  
    END AS Autogrow
, cast(b.max_size/128.0 as int) 'MaxSize'
, physical_name
 
FROM sys.database_files b




Shrink All Transaction Logs

The following script loops through all the Tlogs and attempts to shrink them. Remember that a Tlog can be shrunk only if there is empty space at the end. In order to tell where the empty space is, run:


use [database]
DBCC LOGINFO

If the Status column is 0, then it's empty. If you do not have empty space at the end, it will not shrink, so you need to run Tlog backups. Sometimes you need to run Tlog backups several times for the portion that's empty to move to the end of the file. More on that here:

http://www.mssqltips.com/sqlservertip/1225/how-to-determine-sql-server-database-transaction-log-usage/

Now, for the script:

/*
Get a list of Databases and its Tlogs, and shrink them
Do the following.
1- Run Tlog Backups
2- Run this script

Author: Miguel Quintana

Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2012

Does not work:
MSSQL2000

*/




DECLARE @numRec INT
DECLARE @counter INT
DECLARE @DBName SYSNAME
DECLARE @Tlog SYSNAME
DECLARE @CMD NVARCHAR(MAX)

DECLARE @table TABLE
(RowID INT IDENTITY(1, 1), dbname SYSNAME, tlogname SYSNAME)

-- create a table with the DBname and its Tlog file
-- skip offline and system DBs
INSERT INTO @table 
SELECT db_name(database_id), name FROM sys.master_files 
WHERE type_desc = 'LOG' and state_desc ='ONLINE' and database_id > 4

-- Set and initialize counters
SELECT @numRec=count(*), @counter=1 FROM @table

WHILE  @counter <= @NumRec
BEGIN 

SELECT @DBName = dbname, @Tlog = tlogname FROM @table WHERE RowID = @counter

SET @CMD='USE ' + quotename(@DBName, N'[') + 
                        N'
CHECKPOINT
DBCC SHRINKFILE (['+@Tlog+'], 0, TRUNCATEONLY)'

print @CMD
-- EXECUTE (@CMD)

set @counter=@counter+1
END


 

Ok.. well… what happen if you are forced to shrink the Tlog and you don't have space for backups? Now what?

There is a way where you can take a backup "into the ether"… tricking the database into dumping all pages and forcing to mark the Tlog as empty pages.

Doing this will not cause data loss, however it will invalidate the backup chain, and a full backup should be taken as soon as possible.

 

 


BACKUP LOG [AdventureWorks2012] TO  DISK = N'NUL' 

 

Last Autogrowth Event

.


/*
DESCRIPTION:    Queries the default trace to find out last AUTOGROWTH event

The default trace should be enabled by, well, default

Check if it is enabled:  select status, path from sys.traces where is_default = 1

How far back does it report?

The default trace creates up-to 5 files, 20 mb in size. So depending on how busy the server is, it could be 20 mins or days



Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2008R2
MSSQL2012

DOES NOT WORK
MSSQL2000

Update Log:
 
*/

 
 
Use master
go
 
declare @curr_tracefilename varchar(500) ;  
declare @base_tracefilename varchar(500) ;  
declare @indx int ;
 
select @curr_tracefilename = path from sys.traces where is_default = 1 ;  
 
set @curr_tracefilename = reverse(@curr_tracefilename);
 
select @indx = patindex('%\%', @curr_tracefilename) ;
 
set @curr_tracefilename = reverse(@curr_tracefilename) ;
 
set @base_tracefilename = left( @curr_tracefilename,len(@curr_tracefilename) - @indx) + '\log.trc' ;  
 
 
select  
 loginname
 , spid
 --,convert(int, EventClass) as EventClass,  
,DatabaseName
,[Filename]
,Duration
, StartTime
, EndTime
, (IntegerData*8.0/1024) as ChangeInSize
, e.name
, RIGHT(@curr_tracefilename,@indx) as curr_tracefilename
 
from ::fn_trace_gettable(@base_tracefilename, default ) a
INNER JOIN sys.trace_events e    ON eventclass = trace_event_id

where  
            e.name = 'Log File Auto Grow'
        OR  e.name = 'Log File Auto Shrink'
        OR  e.name = 'Data File Auto Grow'
        OR  e.name = 'Data File Auto Shrink'
 
order by StartTime desc ;  
GO

.

Space usage using Mount Points

What is this?

For reference, check

http://www.mssqltips.com/sqlservertip/2623/configuring-volume-mountpoints-on-a-sql-server-2008-failover-cluster-running-on-windows-server-2008/

A LUN can be mounted as a folder, however, traditional tools are unable to see the space utilized inside the mounted point.

 

To do that, we need to use WMI queries.

Step 1

Save this VBS file on the file system

 

'Author : Vidhya Sagar
'Modified by: Miguel Quintana
'Date : 01st Feb 2009
'Written for sql-articles.com
'Version 2

On Error Resume Next

Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\.\root\cimv2")

' The WMI class will only exist when there are volumes presentm mounted to a drive or a folder
' therefore, the "on error resume next" becomes mandatory.
	
	Set colDisks = objWMIService.ExecQuery _
    ("SELECT * FROM Win32_Volume WHERE DriveLetter IS NULL")

' This section deals with volumes mounted to a NTFS folder.
If colDisks.count > 0 Then
    strComputer = "."
    Set objWMIService = GetObject("winmgmts:" _
          & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
 
    Set colDisks = objWMIService.ExecQuery _
        ("Select * from Win32_LogicalDisk Where DriveType = 3")
    For Each objDisk in colDisks
    Wscript.Echo mid((objDisk.size)/1048576,1,8) & "*" & mid((objDisk.Freespace)/1048576,1,8) & "#" & "Logical Disk" & "|" & objDisk.DeviceID
    Next
    Set objWMIService1 = GetObject("winmgmts:" _
        & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
 
    Set colDisks1 = objWMIService1.ExecQuery _
        ("SELECT * FROM Win32_Volume WHERE DriveLetter IS NULL")
 
    For Each objDisk1 in colDisks1
    Wscript.Echo mid((objDisk1.Capacity)/1048576,1,8) & "*" & mid((objDisk1.Freespace)/1048576,1,8) & "#" & "MountedDrive" & "|" & mid(objDisk1.Name,1,100)
    Next
Else

' This section deals with drives that are not mounted(such as C:\) and with
' mounted volumes to a drive, not a folder
    strComputer = "."
    Set objWMIService = GetObject("winmgmts:" _
          & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
 
    Set colDisks = objWMIService.ExecQuery _
        ("Select * from Win32_LogicalDisk Where DriveType = 3")
    For Each objDisk in colDisks
    Wscript.Echo mid((objDisk.size)/1048576,1,8) & "*" & mid((objDisk.Freespace)/1048576,1,8) & "#" & "Logical_Disk" & "|" & objDisk.DeviceID
    Next
End if




Step 2, run the following TSQL script:

 

/*
DESCRIPTION    List space available in each drive. Compatible with mounted folders.
Must use with Win.vbs file
 
CONFIGURATION    See config section to specify the location of the win.vbs file
        SET @VBSPATH='cscript C:\temp\win.vbs'

 
Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2000

ISSUES:

 
*/


 
    
SET NOCOUNT ON


-- Check to see if 'Show Advanced Option' is enabled
-- Check to see if 'Ole Automation options' is enabled

 
declare @chkOLE as sql_variant
declare @adv_opt as sql_variant
declare @op_ch1 as int
declare @op_ch2 as int
-- these variables keep track if we changed this options, we'll change them back before finishing
 
set @op_ch1=0
set @op_ch2=0
 
select @adv_opt = value from sys.configurations where name = 'show advanced options'
if @adv_opt = 0
begin
 EXEC sp_configure 'show advanced options',1
 RECONFIGURE WITH OVERRIDE;
 set @op_ch1 = 1
end
 
select @chkOLE = value from sys.configurations where name = 'Ole Automation Procedures'
if @chkOLE = 0
begin
 EXEC sp_configure 'xp_cmdshell', 1
 RECONFIGURE WITH OVERRIDE;
 set @op_ch2 = 1
end
 
 


--  Starting the script


-----******************** CONFIGURATION *****************
DECLARE @VBSPATH VARCHAR(200)
SET @VBSPATH='cscript C:\temp\win.vbs' -- Change the path here

-----******************** ENDS CONFIGURATION *****************
 
SET NOCOUNT ON
IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE NAME ='##tmp')
DROP TABLE ##tmp
 
CREATE TABLE ##tmp(diskspace VARCHAR(200))
INSERT ##tmp
EXEC master.dbo.xp_cmdshell @VBSPATH
 
SET ROWCOUNT 3
DELETE ##tmp
 
SET ROWCOUNT 0
 
IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE NAME ='##tmp2')
DROP TABLE ##tmp2
 
CREATE TABLE ##tmp2(DriveName VARCHAR(100),DriveDescription VARCHAR(15),TotalDiskSpace_in_MB VARCHAR(10), Freespace_in_MB VARCHAR(10))
INSERT ##tmp2
 
 
SELECT RTRIM(SUBSTRING(diskspace,charindex('|',diskspace)+1,100)),
 SUBSTRING(diskspace,charindex('#',diskspace)+1,charindex('|',diskspace)-charindex('#',diskspace)-1)
, SUBSTRING(diskspace,1,charindex('*',diskspace)-1) ,
SUBSTRING(diskspace,charindex('*',diskspace)+1,(charindex('#',diskspace)-charindex('*',diskspace))-1)
 
FROM ##tmp WHERE diskspace IS NOT NULL
SELECT * FROM ##tmp2
 
--select * from ##tmp
 
---  End of the routine
 
-- Clean up. See if we need to change the settings back.
 
if @op_ch1 = 1
begin
 EXEC sp_configure 'xp_cmdshell', 0
 RECONFIGURE WITH OVERRIDE;
end
 
if @op_ch2 = 1
begin
 EXEC sp_configure 'show advanced options',0
 RECONFIGURE WITH OVERRIDE;
 
end


Data file sizes and free space

This script builds up on http://www.cookingsql.com/2014/12/free-space-on-drives/ and correlate the data with the location of data files and their sized.

 

/*
DESCRIPTION    List space on database files for each database and its location
 
CONFIGURATION    none

 
Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2012

 
Issues: Some time it has issues when the database name is too long, such as in some "Sharepoint" servers

 
*/
declare @t table 
([database_name] sysname, LogicalDBName sysname, sizeMB bigint, FreeSpaceMB bigint,
freespace nvarchar(4), Autogrow nvarchar(100), MAxSize bigint, physname nvarchar(max))

declare @cmd nvarchar (2000)
set @cmd =
'use [?]
SELECT 
''?'',
b.name,
CAST(b.size/128.0 AS int) ''SizeMB'', 
CAST(b.size/128.0 - CAST(FILEPROPERTY(b.name, ''SpaceUsed'' ) AS int)/128.0 AS int) ''FreeSpaceMB'', 
CAST(ceiling(100 * (b.size/128.0 -CAST(FILEPROPERTY(b.name,''SpaceUsed'' ) AS int)/128.0)/(b.size/128.0)) AS varchar(8)) + ''%''  ''FreeSpace'',
    CASE 
        WHEN b.is_percent_growth = 0 
            THEN LTRIM(STR(b.growth * 8.0 / 1024,10,1)) + '' MB, '' 
        ELSE
            ''By '' + CAST(b.growth AS VARCHAR) + '' percent, ''
    END + 
    CASE 
        WHEN b.max_size = -1 THEN ''unrestricted growth''
        ELSE ''restricted growth to '' + 
            LTRIM(STR(b.max_size * 8.0 / 1024,10,1)) + '' MB'' 
    END AS Autogrow,
cast(b.max_size/128.0 as int) ''MaxSize'',
physical_name

FROM [?].sys.database_files b'
insert into @t
Exec sp_MSForEachDB @cmd

select * from @t


Data Allocation per drive

This script joins two scripts

We first get the free space o drives, and correlate the data (inner join) with the location of data files

 /*
DESCRIPTION    List space on database files for each database and its location
 
CONFIGURATION    none
 
Compatibility list:
MSSQL2008R2 and higher

Issues: Some time it has issues when the database name is too long, such as in some "Sharepoint" servers
 
*/

 /*
DESCRIPTION    List space on database files for each database and its location
 
CONFIGURATION    none
 
Compatibility list:
MSSQL2008R2 and higher



Issues: Some time it has issues when the database name is too long, such as in some "Sharepoint" servers
 
*/

/* This section checks if xp_cmdshell is enabled, and keeps track of the settings */ 
declare @chkOLE as sql_variant
declare @adv_opt as sql_variant
declare @op_ch1 as int
declare @op_ch2 as int
-- these variables keep track if we changed this options, we will change them back before finishing
 
set @op_ch1=0
set @op_ch2=0
 
select @adv_opt = value from sys.configurations where name = 'show advanced options'
if @adv_opt = 0
begin
 EXEC sp_configure 'show advanced options',1
 RECONFIGURE WITH OVERRIDE
 set @op_ch1 = 1
end
 
select @chkOLE = value from sys.configurations where name = 'Ole Automation Procedures'
if @chkOLE = 0
begin
 EXEC sp_configure 'Ole Automation Procedures',1
 RECONFIGURE WITH OVERRIDE
 set @op_ch2 = 1
end
 
/*
This section queries space avail on each drive.
*/
-- ==============
 
DECLARE @cmd2 NVARCHAR(2000)
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20)
DECLARE @MB bigint ; SET @MB = 1048576
 
DECLARE @HOSTNAME varchar(20)
 
-- checks if this is a cluster
select top 1  @HOSTNAME=nodename from ::fn_virtualservernodes()
IF @HOSTNAME is NULL set @hostname = cast(ServerProperty('machinename') as nvarchar(10))
 
declare @temp table 
(servername nvarchar(100), database_name nvarchar(100),driveletter nvarchar(2),totaldriveMB int,
 freedriveMB int,FreeDiskPct nvarchar(3), filename nvarchar(100), filesizeMB nvarchar(10), freeSpaceMB int,
FreeSpacePct varchar(8),AutoGrowth nvarchar(100), MaxsizeMB int, phys nvarchar(100))
 
declare @drives TABLE
(ServerName varchar(15),  drive char(1) PRIMARY KEY,
 FreeSpace int NULL,  TotalSize int NULL)  
 
INSERT @drives(drive,FreeSpace)
EXEC master.dbo.xp_fixeddrives
EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT
 
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
 
DECLARE dcur CURSOR LOCAL FAST_FORWARD
 
FOR SELECT drive from @drives
ORDER by drive
OPEN dcur
 
FETCH NEXT FROM dcur INTO @drive
WHILE @@FETCH_STATUS=0
 
BEGIN
EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive
UPDATE @drives
SET TotalSize=@TotalSize/@MB, ServerName = @HOSTNAME
WHERE drive=@drive
 
FETCH NEXT FROM dcur INTO @drive
END
CLOSE dcur
DEALLOCATE dcur
 
EXEC @hr=sp_OADestroy @fso
 
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
 
 
 /* This section turns off the xp_cmdshell settings if needed  */
 
if @op_ch2 = 1
begin
 EXEC sp_configure 'Ole Automation Procedures', 0
 RECONFIGURE WITH OVERRIDE;
end
 
if @op_ch1 = 1
begin
 EXEC sp_configure 'show advanced options',0
 RECONFIGURE WITH OVERRIDE;
 
end



/* This section queries every database for space stats.*/  

declare @t table 
([database_name] sysname, LogicalDBName sysname, sizeMB bigint, FreeSpaceMB bigint,
freespace nvarchar(4), Autogrow nvarchar(100), MAxSize bigint, physname nvarchar(max))

declare @cmd nvarchar (2000)
set @cmd =
'use [?]
SELECT 
''?'',
b.name,
CAST(b.size/128.0 AS int) ''SizeMB'', 
CAST(b.size/128.0 - CAST(FILEPROPERTY(b.name, ''SpaceUsed'' ) AS int)/128.0 AS int) ''FreeSpaceMB'', 
CAST(ceiling(100 * (b.size/128.0 -CAST(FILEPROPERTY(b.name,''SpaceUsed'' ) AS int)/128.0)/(b.size/128.0)) AS varchar(8)) + ''%''  ''FreeSpace'',
    CASE 
        WHEN b.is_percent_growth = 0 
            THEN LTRIM(STR(b.growth * 8.0 / 1024,10,1)) + '' MB, '' 
        ELSE
            ''By '' + CAST(b.growth AS VARCHAR) + '' percent, ''
    END + 
    CASE 
        WHEN b.max_size = -1 THEN ''unrestricted growth''
        ELSE ''restricted growth to '' + 
            LTRIM(STR(b.max_size * 8.0 / 1024,10,1)) + '' MB'' 
    END AS Autogrow,
cast(b.max_size/128.0 as int) ''MaxSize'',
physical_name

FROM [?].sys.database_files b'
insert into @t
Exec sp_MSForEachDB @cmd

-----------------------------------


select * 
from @t t
inner join @drives d on left(t.physname,1)=d.drive

 

List Number of Rows per table

This script gives you an idea of how big the tables are

/*
DESCRIPTION    List the number of records per table
CONFIGURATION  Set the USE database section 
Author         Miguel Quintana  
 
Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2000

 
*/
 
    USE [databasename]

select
substring(obj.name, 1, 50)        as Table_Name,
ind.rows                        as Number_of_Rows
from sysobjects as obj
inner join sysindexes as ind
on obj.id = ind.id
where obj.xtype = 'u'
and    ind.indid < 2
order by ind.rows desc

 

List Space used by tables

This script is very useful to determine what tables are using the most space,  and also for planning Replication task and figure out how much space is needed for the snapshot folder


/*
DESCRIPTION    List table sizes for a list of tables
Configuration    within script. See comments
Fill in USE for the database
Must know how to compile list of tables


Compatibility list:
MSSQL2005
MSSQL2008

Unknown
MSSQL2000
*/ 
 

/* ********  CONFIGURATION *************
Source of tables
1  Excel Spreadsheet
2  Cursor
3  All tables
*/
declare @source int
SET @SOURCE = 2

---- ************ MUST SET DB HERE

USE CAMPIVCDB


/* ******** END CONFIGURATION ************ */
 
 
DECLARE @tmpTableSizes TABLE
(
    tableName varchar(100),
    numberofRows varchar(100),
    reservedSize varchar(50),
    dataSize varchar(50),
    indexSize varchar(50),
    unusedSize varchar(50)
)

IF (@source = 1)
BEGIN
-- Use MS Excel to generate this list

insert @tmpTableSizes exec sp_spaceused 'vpx_hist_stat1' ;
insert @tmpTableSizes exec sp_spaceused '' ;
insert @tmpTableSizes exec sp_spaceused 'table_3' ;
insert @tmpTableSizes exec sp_spaceused 'table_4' ;
insert @tmpTableSizes exec sp_spaceused 'table_5' ;

END

IF (@source = 2)
BEGIN
-- Or use this cursor

declare @tableName sysname
declare tablelist cursor for

SELECT '['+SCHEMA_NAME(schema_id)+'].['+name+']'
FROM CAMPIVCDB.sys.objects
WHERE name like
'vpx_hist_stat%'

OPEN tablelist
FETCH NEXT FROM tablelist INTO @tableName
WHILE @@FETCH_STATUS = 0
BEGIN
insert @tmpTableSizes exec sp_spaceused @tablename;

FETCH NEXT FROM tablelist INTO @tableName
END
CLOSE tablelist
DEALLOCATE tablelist

END

IF (@source=3)
BEGIN
-- or use this line for ALL tables

EXEC sp_MSforeachtable 'INSERT @tmpTableSizes exec sp_spaceused ''?'''


END

IF (@source NOT IN (1,2,3))
BEGIN 
PRINT 'SOURCE NEEDS T OBE 1, 2, OR 3'
END
ELSE
BEGIN
-- use this section for a summary
-- /*
select
@@servername
,sum(cast(LEFT(reservedSize, LEN(reservedSize) - 3)/1024 as numeric(6,2))) as 'reserved size MB'
,SUM(cast(LEFT(datasize, LEN(datasize) - 3)/1024 as numeric(6,2))) as 'datasize MB'
from @tmpTableSizes

--*/

-- use this section for a detail view

--/*
select 
@@servername
,tableName, numberofrows
, cast(LEFT(reservedSize, LEN(reservedSize) - 4) as int) as 'reserved size KB'
, cast(LEFT(datasize, LEN(datasize) - 4) as int) as 'datasize KB'
from @tmpTableSizes
-- ORDER BY TABLENAME
ORDER BY numberofRows desc
--*/

 


END