SQL SERVER: Checking all tables row count and size

This is one of those posts where I just blurt out neat stuff I just learned! Today I talked to a man who knew a lot more about databases than I do (especially SQL Server) and one of the neat things he taught me were the SQL Server stored procedures sp_msForEachTable and sp_spaceused. This enabled me to put together a very wonderful little function to loop over all tables (using sp_msForEachTable) and fetch information about how much space they use (with sp_spaceused).

I created a function that puts it all in a temporary table because otherwise you just got a whole bunch of individual outputs which may be both hard to read and difficult to export:

CREATE TABLE #tableInformation
(
	name VARCHAR(255),
	rows INT,
	reserved varchar(255),
	data varchar(255),
	index_size varchar(255),
	unused varchar(255)
)

INSERT #tableInformation
	EXEC sp_msForEachTable 
		'exec sp_spaceused ''?'''
		
SELECT name, rows, reserved, data, index_size, unused
FROM #tableInformation
ORDER BY name

DROP TABLE #tableInformation

The output can look like this:

The result table
The result table

T-SQL: Generating test data quickly

Today I needed to generate a lot of test data and my SQL-fu hadn’t been fed properly lately so I wasn’t sure what approach to take. Luckily I only needed pretty basic arbitrary data, but lots of it. The easiest way was to create a easily configurable script that I could run whenever I needed to create more of my data.

Below is a stripped down example of what such a script could look like. The point of this script is to generate a whole bunch of Person data:

Person table:

CREATE TABLE [dbo].[Person](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](100) NOT NULL,
	[Age] [int] NOT NULL,
	[Email] [varchar](255) NULL,
 CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Script to generate X number of people:

DECLARE @CurrentRow INT
DECLARE @CurrentAge INT
DECLARE @PeopleCount INT 
DECLARE @AgeMin INT
DECLARE @AgeMax INT
DECLARE @Name VARCHAR(100)



--CONFIGURATION
SET @PeopleCount = 10	--The number of people to generate
SET @Name = 'TEST_'		--The name of the person (will have an incremented number as suffix)
SET @AgeMin = 18		--The minimum age of the people to generate
SET @AgeMax = 58		--The maximum age of the people to generate
----------------

SET @CurrentRow = 0
SET @CurrentAge = @AgeMin

SET NOCOUNT ON
WHILE @CurrentRow < @PeopleCount
BEGIN
	SET @CurrentRow = @CurrentRow + 1

	SET @CurrentAge = @CurrentAge + @CurrentRow

	IF @CurrentAge > @AgeMax
	BEGIN
		SET @CurrentAge = @AgeMin
	END

	INSERT INTO Person
	(
		Name,
		Age
	)
	VALUES
	(
		@Name + CONVERT(VARCHAR, @CurrentRow),
		@CurrentAge
	)
END
SET NOCOUNT OFF

PRINT CONVERT(VARCHAR, @PeopleCount) + ' people created!'