SQL: Automation for Database Index Defragmentation
The benefit of using index in SQL is to improve the performance when searching through tables without having to scan for each table referenced in a query. Yet as the table grows, the index expands. More devastatingly, the index becomes fragmented, which actually creates unnecessary overhead for queries. In order to resolve the fragmentation problem on tables and indexes, I created the following T-SQL to look through all tables and indexes and attempt to defragment them. USE MASTER ---------------- CREATE TEMPORARY TABLES ---------------- DECLARE @DatabaseName NVARCHAR ( 100 ) DECLARE @TableName NVARCHAR ( 100 ) DECLARE @IndexName NVARCHAR ( 100 ) IF OBJECT_ID ( 'tempdb..#DatabaseList' ) IS NOT NULL BEGIN DROP TABLE #DatabaseList END CREATE TABLE #DatabaseList ( [DatabaseName] NVARCHAR ( 100 ) NOT NULL, ) IF OBJECT_ID ( 'tempdb..#DatabaseTableIndexList' ) IS NOT NULL BEGIN DROP TABLE #DatabaseTableIndexList END CREATE TABLE #Database...