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

SQL Server: How to copy a row with an incremented primary key

So, today I had to copy a row in our database for testing purposes (needed the exact same data but with one small fix and compare it to the original). Due to fantastic database design the row so many rows that I had no interest in just writing an insert statement and copying the values by hand. I googled the internet and found several solutions that may work. The following was my favorite:

> select * from person

+ ------- + --------- + -------- +

| id      | name      | age      |

+ ------- + --------- + -------- +

| 1       | Magnus    | 29       |

| NULL    | NULL      | NULL     |

+ ------- + --------- + -------- +

I want to copy the row with id 1. This is a very short row with only three columns, so it would be easy to do by hand. Consider it a very small example.
Now, to copy this to another table do the following:

SELECT * INTO tmpperson FROM person

Then drop the id column from the temporary table:

ALTER TABLE tmpperson DROP COLUMN id

and insert it back:

INSERT INTO person SELECT * FROM tmpperson

SQL Server 2008: Create an alter table script

There are times where you need to save a table as a script in order to use it later. This is great if you want to script how the table is created or perhaps script an insertion of an item. If you, however, want to script a change to the table such as the addition of a column then you will see the following nuisance:

 

The “ALTER To”-item in the menu is grayed out. This is because you have to make the changes prior to scripting. Follow these steps to create a new column and script the changes:

1. Go to the design mode for the table in question:

 

2. Add you new column:

 

3: Right click on the column and select “Generate Change Script”:

 

4: Copy the script and do whatever you want with is:

 

To test the script you close the design WITHOUT SAVING IT. You then open a new query window, select the appropriate database and run the query. This way you can guarantee that the query is correct:

SQL Server Profiler missing templates

If you’ve upgraded to SQL Server 2008 R2 then you might have experienced a problem with your templates; they are gone!  You will also notice that your trace provider is now Microsoft SQL Server 10.50 and not Microsoft SQL Server 2008. This means different template folders and for some reason the templates are not always moved to the new 1050 folder from the old 100 folder, so what you have to do is this:

  • If you don’t have a 1050 folder, create on at C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Profiler\Templates\Microsoft SQL Server
  • Copy all the template files from C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Profiler\Templates\Microsoft SQL Server\100
  • Paste them in your 1050 folder at C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Profiler\Templates\Microsoft SQL Server\1050

A profiler for MySql

Allright, one things that really bugs me when I do random development in PHP/MySql is the lack of the fantastic SQL Server profiler which lets you monitor all queries coming in to the database.
I haven’t been looking too hard for one in MySql but after being stuck on some seriously annoying bugs in a project I decided to go find one.
And I did.
And it was costly…

If you have money, I’m sure the Jet Profiler for MySql is great, but I don’t and I’m not paying $499 for monitoring some queries…

There is a command line profiler that works great and that arrived in MySql version 5.0.37 and it’s quite easy to use, here’s an example:

mysql> SET profiling = 1;
Query OK, 0 ROWS affected (0.00 sec)
 
mysql> SELECT * FROM USER;
Empty SET (0.00 sec)
 
mysql> INSERT INTO USER (Username, Password) VALUES ('Superuser', 'Secret');
Query OK, 1 ROW affected (0.03 sec)
 
mysql> SELECT * FROM USER;
+----+-----------+----------+
| Id | Username  | Password |
+----+-----------+----------+
|  2 | Superuser | Secret   |
+----+-----------+----------+
1 ROW IN SET (0.00 sec)
 
mysql> SHOW profiles;
+----------+------------+-------------------------------------------------------
---------------+
| Query_ID | Duration   | Query
               |
+----------+------------+-------------------------------------------------------
---------------+
|        1 | 0.00032425 | SELECT * FROM USER
               |
|        2 | 0.03341050 | INSERT INTO USER (Username, Password) VALUES ('Superus
er', 'Secret') |
|        3 | 0.00022775 | SELECT * FROM USER
               |
+----------+------------+-------------------------------------------------------
---------------+
3 ROWS IN SET (0.00 sec)
 
mysql>

Very nice, this can help a lot when your running dynamic queries and want to see why they’re not working :)!
One thing though, the profiling works on a session basis, so different sessions cannot listen to the others queries, but this can be used inside whatever application you’re running in tests as well.