RSS

Create Clustered index on all heap tables by on single click V2

11 Mar

images (1)Hello my followers in my last post i created one DMV to can help us for figuring the heap tables on database level then create automatic clustered index on this heap table then after i worked on the DMV i found it worked on database level not on server level and this very hard to execute it on databases one by one (I love to create general DMV for all Server to be saved as Stored procedure under MSDB ) and i found also the DMV take the first column in the table then i create on it cluster index and this from index design and scmaa design not correct because so i updated my DMV to cover two new point very important and they will do the stored proceure more comprehensive and reliable 

update on version number 2:

  1. Select the best column from heap table to create on it the clustered index (i used case when T-SQL to return the data type columns with specific data types i can create on it clustered index.)
  2. DMV now running on server level to cover all database exists on SQL Server instance. ( Converted the query to dynamic query and i looped on each database on the server then i executed this dynamic query on it to print to me one script for the database )

How to execute the Stored procedure #Check_Heap_Tables#

  1. Create Stored Procedure Check_Heap_Tables on MSDB database.
  2. Execute Check_Heap_Tables Stored procedure .
  3. Copy the T-SQL result then execute it on anther session.
  4. Copy the T-SQL for Clustered index create and execute it on new session.

Download Stored Procedure Create Clustered index on Heap tables V2

— =============================================
— Author: Mustafa EL-masry
— Create date: 01/03/2015
— Updated : 10/03/2015
— new Update :To cover all the databases in one single click , Select the int or Bigint Columns for Clsuter index
— Description: Create Clustered index on All heap tables
— =============================================
USE msdb
GO
Create Proc Check_Heap_Tables
AS
begin
SET NOCOUNT ON
DECLARE @Databasename VARCHAR(200)
— database name
DECLARE db_cursor_1 CURSOR
FOR
SELECT name
FROM sys.databases
WHERE database_id > 4
AND name <> ‘distribution’
AND state_desc = ‘ONLINE’
OPEN db_cursor_1
FETCH NEXT FROM db_cursor_1 INTO @Databasename

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ‘ SET NOCOUNT ON
GO’
DECLARE @sql NVARCHAR(MAX)= ‘USE ‘ + QUOTENAME(@Databasename)
+ ‘;
CREATE TABLE #Table_Policy ( ID INT PRIMARY KEY IDENTITY(1, 1) NOT NULL , Table_Name NVARCHAR(100) , Rows_Count INT , Is_Heap INT , Is_Clustered INT , num_Of_nonClustered INT );
WITH cte AS ( SELECT table_name = o.name ,o.[object_id] ,i.index_id ,i.type ,i.type_desc FROM sys.indexes i INNER JOIN sys.objects o ON i.[object_id] = o.[object_id] WHERE o.type IN ( ”U” )AND o.is_ms_shipped = 0 AND i.is_disabled = 0 AND i.is_hypothetical = 0 AND i.type <= 2 ),
cte2 AS ( SELECT * FROM cte c PIVOT ( COUNT(type) FOR type_desc IN ( [HEAP], [CLUSTERED], [NONCLUSTERED] ) ) pv ) INSERT INTO #Table_Policy ( Table_Name , Rows_Count , Is_Heap , Is_Clustered ,num_Of_nonClustered)
SELECT c2.table_name ,[rows] = MAX(p.rows) ,is_heap = SUM([HEAP]) ,is_clustered = SUM([CLUSTERED]) ,num_of_nonclustered = SUM([NONCLUSTERED]) FROM cte2 c2 INNER JOIN sys.partitions p ON c2.[object_id] = p.[object_id]
AND c2.index_id = p.index_id GROUP BY table_name –DMV
SELECT * FROM #Table_Policy WHERE num_Of_nonClustered = 0 AND Is_Heap = 1
DECLARE @name NVARCHAR(100)
DECLARE db_cursor CURSOR
FOR
SELECT Table_Name FROM #Table_Policy WHERE num_Of_nonClustered = 0
AND Is_Heap = 1
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @name2 NVARCHAR(100)
DECLARE db_cursor2 CURSOR
FOR
WITH CTE
AS ( SELECT TOP 1 COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @name AND DATA_TYPE IN ( ”int” )
UNION
SELECT TOP 1 COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @name AND DATA_TYPE IN ( ”bigint” )
UNION
SELECT TOP 1 COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @name AND DATA_TYPE IN ( ”NVARCHAR” )
UNION
SELECT TOP 1 COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @name
AND DATA_TYPE IN ( ”Varchar” )
UNION
SELECT TOP 1
COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @name
AND DATA_TYPE IN ( ”Char” )
)
SELECT TOP 1
COLUMN_NAME
FROM CTE
OPEN db_cursor2
FETCH NEXT FROM db_cursor2 INTO @name2
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @SQL2 NVARCHAR(MAX)= N”Create Clustered index [IX_”
+ @name + ”] on [” + @name + ”]
(” + @name2
+ ” ASC) with (Fillfactor=80,Data_Compression=page)
GO”
PRINT @SQL2
FETCH NEXT FROM db_cursor2 INTO @name2
END
CLOSE db_cursor2
DEALLOCATE db_cursor2
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
GO
DROP TABLE #Table_Policy
go’
PRINT @sql
FETCH NEXT FROM db_cursor_1 INTO @Databasename
END
CLOSE db_cursor_1
DEALLOCATE db_cursor_1
END

Follow the author:

View all my tips , LinkedIn Website Slideshare  , ABOUT ME

 
Leave a comment

Posted by on March 11, 2015 in General topics

 

Tags: , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s