codemeit

MSSQL : List all tables with size and row count

It comes to be very handy while analyzing the database tables.
Here is a quick and dirty script lists all the tables and their rows counts, data size.

DECLARE @table table(Id int IDENTITY(1,1)
					, Name varchar(256))

INSERT INTO @table
SELECT b.name + '.'+ a.name
FROM sys.tables a INNER JOIN sys.schemas b
		ON a.schema_id = b.schema_id

INSERT INTO @table
SELECT '-1'

DECLARE @result table(	TableName varchar(256)
						, TotalRows int
						, Reserved varchar(50)
						, DataSize varchar(50)
						, IndexSize varchar(50)
						, UnusedSize varchar(50))

DECLARE @temp varchar(256)
DECLARE @index int
SET @index = 1

WHILE 1=1
BEGIN
	SELECT @temp = Name
	FROM @table
	WHERE Id = @index

	IF @temp = '-1'
		BREAK	

	INSERT @result(	TableName
					, TotalRows
					, Reserved
					, DataSize
					, IndexSize
					, UnusedSize)
	EXEC sp_spaceused @temp

	SET @index = @index + 1
END

SELECT c.name+'.'+b.name as [table]
		, a.*
 FROM @result a
		INNER JOIN sys.tables b
			ON a.TableName = b.name
		INNER JOIN sys.schemas c
		ON b.schema_id = c.schema_id
ORDER BY TotalRows DESC

A simplest/dumbest CSharp IDE

The tiny WPF app (14k) has a textbox allows the input of the C# code, it compiles the code and execute the code after press F5 key.
Here is what it looks like. No surprise.

The main part which compiles the code at runtime is using CSharpCodeProvider from Microsoft.CSharp namespace.

At runtime, it gets translates to perform the following task (path is used as in my environment)

D:\src\CodeF5\bin\Debug> “C:\Windows\Microsoft.NET\Framework\v3.5\csc.exe” /t:exe /utf8output /R:”System.dll” /R:”System.Core.dll” /R:”System.Xml.dll” /R:”System.Xml.Linq.dll” /out:”D:\dev\trunk\proj\CodeF5\src\CodeF5\bin\Debug\MyOutput\My.exe” /D:DEBUG /debug+ /optimize- /w:4 “C:\Users\admin\AppData\Local\Temp\po4a4o8n.0.cs”

Download the source code