Database Table Size Script for SQLServer

I found these two scripts online, very useful for rows counts and size:

Declare @QueryString NVARCHAR(MAX);
Select @QueryString =COALESCE(@QueryString + ' UNION ALL ','') +
					'SELECT ' +
					'''' + QUOTENAME(SCHEMA_NAME(sOBJ.SCHEMA_ID)) +
					'.' + QUOTENAME(sOBJ.name) + ''''+ ' AS [TABLENAME]
					, COUNT(*) AS [RowCount] FROM ' + QUOTENAME(SCHEMA_NAME(sOBJ.SCHEMA_ID)) +
					'.' + QUOTENAME(sOBJ.name) + ' WITH (NOLOCK) ' 
					FROM sys.objects sOBJ
					WHERE sOBJ.type='U' and sOBJ.is_ms_shipped=0x0
					Order by SCHEMA_NAME(sObj.Schema_Id), sOBJ.name ;
exec sp_executesql @Querystring 

CREATE TABLE #RowCountsAndSizes (TableName NVARCHAR(128),rows CHAR(11),      
       reserved VARCHAR(18),data VARCHAR(18),index_size VARCHAR(18), 
       unused VARCHAR(18))

EXEC       sp_MSForEachTable 'INSERT INTO #RowCountsAndSizes EXEC sp_spaceused ''?'' '

SELECT     TableName,CONVERT(bigint,rows) AS NumberOfRows,
           CONVERT(bigint,left(reserved,len(reserved)-3)) AS SizeinKB
FROM       #RowCountsAndSizes 
ORDER BY   SizeinKB DESC,NumberOfRows DESC,TableName

DROP TABLE #RowCountsAndSizes

Related Posts

Comments are closed.


Warning: Use of undefined constant XML - assumed 'XML' (this will throw an Error in a future version of PHP) in /homepages/35/d428352912/htdocs/WilliamMendoza/wp-content/plugins/wp-syntaxhighlighter/wp-syntaxhighlighter.php on line 1048