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... [More]

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 -- =======================================... [More]

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: -----------------------------------------------------------... [More]