John McCormack DBA

SQL Server Databases and Cloud

How to write a PIVOT query in T-SQL

By Leave a Comment

sql server pivotIn this post, I explain how to write a simple PIVOT query in T-SQL and why we might want to use the PIVOT operator. The Oxford English Dictionary definition of PIVOT is

The central point, pin, or shaft on which a mechanism turns or oscillates.

So what we are doing here is TURNING THE DATA AROUND (PIVOTING) for visual purposes. The object of the exercise is to return 3 wide rows of data, instead of the 36 unique rows in the table.

Create the table and data which we will use in out PIVOT query

Lets create a database table which records the size of different tables within a database over time.

[sql]
CREATE TABLE TableSize
(
TableSizeId int identity (1,1) Primary Key,
DatabaseName sysname,
Name sysname,
DataKB INT,
Collection_Date Date
)

[/sql]

[Read more…]

An alternative to sp_msforeachdb

By Leave a Comment

It’s well known that the sp_msforeachdb stored procedure is undocumented however it can be handy. The trouble with the proc is that it is unreliable. What’s more, it could be dropped from future SQL Server releases causing all your automated scripts to fail.

This little script shows you a more reliable solution and saves you from relying on an undocumented procedure.

USE Master
SET NOCOUNT ON
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp

SELECT name,0 as completed
INTO #tmp
FROM sys.databases
WHERE name NOT IN('tempdb','Training2012') -- A list of DBs you don't wish to include

DECLARE @dbname sysname
DECLARE @cmd NVARCHAR(4000)

WHILE EXISTS(SELECT 1 FROM #tmp WHERE completed = 0)
BEGIN

SET @dbname = (SELECT TOP 1 NAME FROM #tmp WHERE completed = 0) -- You can ORDER BY name if you care about the order
SET @cmd = 'BACKUP DATABASE '+@dbname+' TO DISK = ''D:\Backup\'+@dbname+'.Bak'''
EXEC sp_executesql @cmd
UPDATE #tmp SET completed = 1 WHERE name = @dbname

END

Let’s break up what the script actually does:

  1. DROP temp table if it already exists
    1. [sql]IF OBJECT_ID(‘tempdb..#tmp’) IS NOT NULL DROP TABLE #tmp[/sql]
  2. Select into a temporary table with two columns. (Name and Completed). We set completed=0 for each row created.
    1. [sql]SELECT name,0 as completed INTO #tmp FROM sys.databases[/sql]
  3. The WHERE clause is optional
    1. [sql]WHERE name NOT IN(‘tempdb’,’Training2012′)[/sql]
  4. Create a @dbname variable (for each DB name) and a @cmd variable (for building up the T-SQL command).
    1. [sql]DECLARE @dbname sysname
      DECLARE @cmd NVARCHAR(4000)[/sql]
  5. The LOOP (Create and execute a new statement for each DB name and exit the loop once all DBs have been handled).
    1. [sql]WHILE EXISTS (SELECT 1 FROM #tmp WHERE completed = 0)
      BEGIN
      SET @dbname = (SELECT TOP 1 NAME FROM #tmp WHERE completed = 0) — You can ORDER BY name if you care about the order
      SET @cmd = ‘BACKUP DATABASE ‘+@dbname+’ TO DISK = ”D:\Backup\’+@dbname+’.Bak”’
      EXEC sp_executesql @cmd
      UPDATE #tmp SET completed = 1 WHERE name = @dbname
      END[/sql]

This approach is simple and reliable and it avoids the use of undocumented stored procedures and cursors. The example above demonstrates a native backup but this command can be anything you like.