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!'

MySql: if-condition

Sometimes it can be handy to have a if-condition in a SQL query and MySql makes it quite easy. The syntax is quite easy to learn. Here’s an example:

Tabell `Person`:
Id Name Age Gender AddressId 
3	John Doe	50	Male	3
4	Jane Doe	30	Female	4
6	Jake Doe	35	Male	3
7	Jessica Doe	61	Female	4

If we want to fetch all name and prefix them “Mr” or “Mrs” then we could do this:

SELECT IF( Gender = 'Male', CONCAT( 'Mr', ' ', Name ) , CONCAT( 'Mrs', ' ', Name ) ) AS Name
FROM `Person`
WHERE 1
LIMIT 0 , 30

This will fetch all the names and prefix them with Mr if they are male and Mrs if they are female (I’m prudent enough to assume that the names of unmarried women are not stored in a database :))

Name 
Mr John Doe
Mrs Jane Doe
Mr Jake Doe
Mrs Jessica Doe