SQL Server database table sizes

To get a good overview of the amount of data that resides in your database tables, you can use this handy T-SQL scripts.

CREATE PROCEDURE [dbo].[GetDBTableSize]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @cmdstr varchar(100)
--Create Temporary Table
CREATE TABLE #TempTable
(    [Table_Name] varchar(50),
Row_Count int,
Table_Size varchar(50),
Data_Space_Used varchar(50),
Index_Space_Used varchar(50),
Unused_Space varchar(50)
)
--Create Stored Procedure String
SELECT @cmdstr = 'sp_msforeachtable ''sp_spaceused "?"'''
--Populate Tempoary Table
INSERT INTO #TempTable EXEC(@cmdstr)
--Determine sorting method
SELECT * FROM #TempTable ORDER BY Table_Name
--Delete Temporay Table
DROP TABLE #TempTable
END

 

Hope this helps,

Defragmentation script for SQL Server

When you SQL server database becomes slower over time without apparent reasons, probably you’re facing a fragmentation problem. Database table fragmentation is one of the major performance killers.

With the following maintenance script you defrag you tables and indexes.

-- =============================================
-- Copyright by Author Sajal Dam, ISBN 1590594215 
-- Description: Performs defragmentation actions
-- tables and indexes in your database.
-- Execute this script once a month or more often 
-- if necessary
-- =============================================
CREATE PROC [dbo].[pr_Sys_DefragDatabase]
AS
SET NOCOUNT ON
--Create temporary table to hold DBCC SHOWCONTIG output
CREATE TABLE #FragmentationResult(
    ObjectName VARCHAR(255), ObjectId INT, IndexName VARCHAR(255),
    IndexId INT, [Level] INT, Pages INT, [Rows] INT,
    MinimumRecordSize INT, MaximumRecordSize INT,
    AverageRecordSize FLOAT, ForwardedRecords INT, Extents INT,
    ExtentSwitches INT, AverageFreeBytes FLOAT,
    AveragePageDensity FLOAT, ScanDensity FLOAT, BestCount INT,
    ActualCount INT, LogicalFragmentation FLOAT,
    ExtentFragmentation FLOAT
)
--Create temporary table to hold tables/indexes that require
-- defragmentation
CREATE TABLE #Defragmentation(
    [id] INT IDENTITY,
    ObjectName VARCHAR(255),
    IndexName VARCHAR(255),
    ScanDensity FLOAT
)

--Identify all user tables in the current database to analyze
-- fragmentation
SELECT SO.id as [id], SO.name as [name] INTO #UserTables
    FROM sysobjects SO
    INNER JOIN sysusers SU
    ON SO.uid = SU.uid
    WHERE type = 'U'
    AND SU.name = 'dbo' 
    ORDER BY [id]
 
--Determine fragmentation of every user table/index
DECLARE @id INT, @name VARCHAR(255), @TableCnt INT
SET @id = 0
SELECT @TableCnt = COUNT(*) FROM #UserTables
    WHILE @TableCnt > 0
    BEGIN
        SELECT TOP 1 @id=[id], @name=[name]
        FROM #UserTables
        WHERE [id] > @id
        
        INSERT INTO #FragmentationResult
        EXEC('DBCC SHOWCONTIG([' + @name + '])
            WITH ALL_INDEXES, TABLERESULTS')
        SET @TableCnt = @TableCnt - 1
    END
    
--Determine user tables/indexes that require defragmentation
INSERT INTO #Defragmentation
SELECT ObjectName, IndexName, ScanDensity
    FROM #FragmentationResult
    WHERE ScanDensity < 50 --Scan Density is low
    AND LogicalFragmentation > 9 --Logical Scan Fragmentation is high
    AND PAGES > 8 --Not a very small table
DROP TABLE #FragmentationResult

--Defragment tables/indexes with high fragmentation
DECLARE @oname VARCHAR(255), @iname VARCHAR(255), @sdensity FLOAT
SET @id = 0
SELECT @TableCnt = COUNT(*) FROM #Defragmentation
WHILE @TableCnt > 0
BEGIN
    SELECT TOP 1 @id=[id], @oname = ObjectName, 
        @iname = IndexName, @sdensity = ScanDensity
        FROM #Defragmentation
        WHERE [id] > @id
    PRINT '** De-fragmentation #' + CAST(@id AS VARCHAR(15))+ ' **'
    PRINT 'DBCC DBREINDEX on [' + @oname + '].[' + @iname
            + '] with ScanDensity = ' + CAST(@sdensity AS VARCHAR(15)) + '%'
    DBCC DBREINDEX(@oname, @iname)
    SET @TableCnt = @TableCnt - 1
END

--Release resources
DROP TABLE #UserTables
DROP TABLE #Defragmentation
SET NOCOUNT OFF

 

Hope this help,

Easy way to clear your SQL server database

Sometimes you want to make a clean start with your SQL server database since it’s filled up with old demo data. We all now that you can delete or truncate your tables easily by executing some T-SQL statements. When you only have a couple of tables and no complex foreign key dependencies you can do it by hand. When you database is larger and for more complex it can be a time consuming task and it’s different for each database schema.

The following script makes it really easy (beware: select the correct database!).

 1: ------------------------------------------------------------------------------------
 2: ---- NOTE: !!! DOUBLE CHECK SELECTED DATABASE !!!! ----
 3: ---- FIRST TRY RUNNING WITH TRANSACTIONS ON, LATER REPLACE ROLLBACK WITH COMMIT ----
 4: ------------------------------------------------------------------------------------
 5: begin transaction
 6:  
 7: -- disable all constraints
 8: EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
 9:  
 10: -- delete data in all tables
 11: EXEC sp_MSForEachTable "DELETE FROM ?"
 12:  
 13: -- enable all constraints
 14: exec sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
 15:  
 16: -- some of the tables have identity columns we may want to reseed them
 17: EXEC sp_MSforeachtable "DBCC CHECKIDENT ( '?', RESEED, 0)"
 18:  
 19: rollback transaction
 20: --commit transaction

Note: I have the code surrounded by a transaction that performs a rollback by default. Just to make sure that you don´t run this script accidentally on the wrong database. You can download the script by using the link below

clear_all_database_tables.sql (1.54 kb)  

Hope this can save some time for you too,