If you have been reading the past few blog entries, you know we are building the foundation for having a quick method for viewing the size of all tables in all databases on a given SQL Server instance. By size I mean both file size in bytes and number of rows. This global view will be called vwTable and will be presented very soon in a subsequent post.
Here is the last piece of the foundational layer that vwTable will use. The below proc PopSpaceUsed creates a table called SpaceUsed. When the proc is run, it will populate the table with the size in bytes for all tables in the SQL Server instance. The table is truncated and repopulated each time the proc is executed. The guts of this proc is that it calls Exec sp_spaceused for every table in a given database, or optionally all the databases. I wasn’t able to find a dynamic management view (DMV) that contains size in bytes for a table. Please let me know if you run across this. Besides size in bytes, sp_spaceused also returns the number of rows. Nevertheless our future views will not need to use this because the number of rows is available in a DMV. (See vwPartitionRaw which draws from sys.partitions). Surprisingly, sp_spaceused executes rather quickly.
Use MyDatabaseName
Exec sp_spaceused 'dbo.Employees'
Since sp_spaceused must be run in the database context of the table, I use a loop to get all the databases on the SQL instance. It is my custom to do this with a simple WHILE loop, which incidentally is the only LOOP construct that T-SQL offers other than cursors. I avoid using cursors as a general principle, although it would probably be just fine to do that if you prefer since the cursor would be very small.
Once the proc is run, you can examine the table.
SELECT *
FROM SpaceUsed
The header of the proc contains a description and proc usage information.
USE Admin
IF OBJECT_ID('dbo.PopSpaceUsed') Is Not Null
DROP PROC dbo.PopSpaceUsed
GO
CREATE PROC [dbo].PopSpaceUsed
@DB varchar(300),
@ShowSql bit = 0,
@SuppressOutput bit = 1
AS
/* DATE AUTHOR REMARKS
5/24/11 PPaiva Initial creation.
DESCRIPTION
Populates SpaceUsed for a given dB. Based on sp_spaceused.
To populate tables of all databases on this server,
use @DB = ALL.
Column SizeMB strips the KB out of column Data,
and converts to MB.
DEPENDENCY
sp_spaceused
USAGE
Exec Admin..PopSpaceUsed 'All'
Exec Admin..PopSpaceUsed 'All', 1
Exec Admin..PopSpaceUsed 'Blah'
Exec Admin..PopSpaceUsed 'Admin', 1
DEBUG
SELECT *
FROM SpaceUsed
Use MyDatabaseName
Exec sp_spaceused 'dbo.Employees'
SELECT *
FROM Admin.sys.tables
ORDER BY name
SELECT *
FROM Admin.sys.schemas
-- Compare the same table in different DBs
SELECT *
FROM SpaceUsed
ORDER BY Server, TableName, SchemaName, DB
-- Largest tables
SELECT *
FROM SpaceUsed
ORDER BY Rows desc, TableName, SchemaName, DB
-- Tables per Database
SELECT Server, DB, Min(PopDate) PopDate, Count(*) Qty
FROM SpaceUsed
GROUP BY Server, DB
ORDER BY Server, DB
*/
SET NOCOUNT ON
DECLARE @ID int,
@MaxID int,
@Name nvarchar(128),
@MaxDB nvarchar(300),
@SchemaName nvarchar(50),
@s nvarchar(1000),
@ServerName varchar(50) = Convert(varchar(50), ServerProperty('ServerName')),
@sNow varchar(30) = Convert(varchar, GetDate(), 120) -- Allows the population date to be consistent
--IF Object_ID('dbo.SpaceUsed') Is NOT Null
-- DROP TABLE dbo.SpaceUsed
-- Create table only once
IF Object_ID('dbo.SpaceUsed') Is Null
BEGIN
-- Left indendted columns are inserted and can be NOT NULL
-- The rest must be NULL unless calculated or a default exists
SET @s = '
CREATE TABLE Admin.dbo.SpaceUsed(
ID int IDENTITY(1,1) NOT NULL CONSTRAINT pk_SpaceUsed PRIMARY KEY CLUSTERED,
Server varchar(100) NOT NULL CONSTRAINT def_SpaceUsed_Server DEFAULT ''' + @ServerName + ''',
DB varchar(100) NULL,
SchemaName varchar(50) NULL,
TableName nvarchar(128) NOT NULL,
Rows int NOT NULL,
SizeMB decimal(22, 3) NULL,
TotalMB AS (CONVERT(int,replace(index_size,''KB'',''''),0)/(1000.0)+CONVERT(int,replace(data,''KB'',''''),0)/(1000.0)),
object_id int NULL,
Reserved varchar(18) NOT NULL,
data varchar(18) NOT NULL,
index_size varchar(18) NOT NULL,
unused varchar(18) NULL,
PopDate datetime NULL
)
'
--Print @s
Exec(@s)
CREATE UNIQUE INDEX ix_SpaceUsed_ServerDBSchemaTable
ON dbo.SpaceUsed (Server, DB, SchemaName, TableName)
CREATE INDEX ix_SpaceUsed_Rows
ON dbo.SpaceUsed (Rows)
END
IF @DB = 'All'
TRUNCATE TABLE Admin.dbo.SpaceUsed
ELSE
DELETE
FROM Admin.dbo.SpaceUsed
WHERE DB = @DB
OR DB Is Null
IF Object_ID('tempdb.dbo.#SpaceUsedDBList') Is Not Null
DROP TABLE #SpaceUsedDBList
IF Object_ID('tempdb.dbo.#PopSpaceUsedTableList') Is Not Null
DROP TABLE #PopSpaceUsedTableList
IF @DB <> 'All'
IF Not Exists( SELECT *
FROM sys.databases
WHERE name = @DB
AND state_desc = 'online')
BEGIN
SET @s = 'Database <' + @DB + '> was not found on this server.'
RaisError(@s, 16, 1)
RETURN
END
-- Since sp_spaceused only works for the current database context,
-- we have to execute it as embedded SQL. This table will contain
-- the names of all tables with database names, then we are ready
-- to call sp_spaceused.
CREATE TABLE #PopSpaceUsedTableList(
ID int NOT NULL IDENTITY(1, 1) PRIMARY KEY,
DB nvarchar(300),
object_id int,
SchemaName nvarchar(50),
TableName nvarchar(128)
)
SELECT name
INTO #SpaceUsedDBList
FROM sys.databases
WHERE name Not In ('master', 'model', 'msdb', 'tempdb')
AND state_desc = 'online'
AND (name = @DB OR @DB = 'All')
SELECT @MaxDB = Max(name),
@DB = ''
FROM #SpaceUsedDBList
-- For all designated databases
WHILE @DB < @MaxDB
BEGIN
-- Get next DB
SELECT @DB = Min(name)
FROM #SpaceUsedDBList
WHERE name > @DB
-- Get table list for this DB
SET @s = 'INSERT INTO #PopSpaceUsedTableList (DB, object_id, SchemaName, TableName)
SELECT ''' + @DB + ''' , t.object_id, s.name SchemaName, t.Name
FROM [' + @DB + '].sys.tables t
JOIN [' + @DB + '].sys.schemas s
ON t.schema_id = s.schema_id
WHERE t.name Not In (''dtproperties'')
ORDER BY t.name, s.name'
Exec(@s)
END
IF @ShowSql = 1
SELECT *
FROM #PopSpaceUsedTableList
SELECT @MaxID = Max(ID),
@ID = 0
FROM #PopSpaceUsedTableList
-- Ready to call out sp_spaceused for each table in designated DBs
WHILE @ID < @MaxID
BEGIN
-- Get next table
SELECT @ID = Min(ID)
FROM #PopSpaceUsedTableList
WHERE ID > @ID
SELECT @DB = DB,
@Name = TableName,
@SchemaName = SchemaName
FROM #PopSpaceUsedTableList
WHERE ID = @ID
SET @s = 'use [' + @DB + '] INSERT INTO Admin.dbo.SpaceUsed (TableName, Rows, Reserved, data, index_size, unused)
Exec sp_spaceused ''[' + @SchemaName + '].[' + @Name + ']'''
IF @ShowSql = 1
Print @s
Exec(@s)
UPDATE SpaceUsed
SET SchemaName = @SchemaName,
DB = @DB,
PopDate = @sNow
WHERE ID = @ID
END
UPDATE SpaceUsed
SET Server = Convert(varchar, ServerProperty('ServerName')),
SizeMB = Convert(decimal(22, 3), Replace(data, ' KB', '')) / 1000.
UPDATE SpaceUsed
SET object_id = t.object_id
FROM SpaceUsed s
JOIN #PopSpaceUsedTableList t
ON s.DB = t.DB
AND s.SchemaName = t.SchemaName
AND s.TableName = t.TableName
IF @SuppressOutput = 0
SELECT *
FROM SpaceUsed
ORDER BY Server, DB, TableName, SchemaName
