MS SQL Create Clustered Index on All Tables

I was creating Windows Azure project, and was faced when uploading the database to SQL Azure, that many of the tables didn't have Clustered Indexes, thought of saving some time by writing a script.

This script will create Clustered Index on all tables which do not have clustered index.


DECLARE @table_name VARCHAR(50); -- table name 
DECLARE @col_name VARCHAR(50); -- column name 
DECLARE @idx_name VARCHAR(256); -- name for index 
DECLARE @cmd1 NVARCHAR(256); -- command syntax

DECLARE db_cursor CURSOR FOR 
SELECT     sys.sysobjects.name AS tbName, sys.syscolumns.name AS colName, 'idx_' + sys.sysobjects.name + '_' + sys.syscolumns.name as idxName
FROM         sys.sysobjects INNER JOIN
                      sys.syscolumns ON sys.sysobjects.id = sys.syscolumns.id INNER JOIN
                      sys.systypes ON sys.syscolumns.xtype = sys.systypes.xtype LEFT OUTER JOIN
                      sys.indexes ON sys.sysobjects.id = sys.indexes.object_id
WHERE     (sys.sysobjects.xtype = 'U')  AND (sys.systypes.name = 'int') AND (sys.syscolumns.colid = 1) AND
                      (sys.indexes.index_id = 0)
ORDER BY tbName;

OPEN db_cursor;  
FETCH NEXT FROM db_cursor INTO @table_name, @col_name, @idx_name;  

WHILE @@FETCH_STATUS = 0  
BEGIN  

SET @cmd1 = 'CREATE CLUSTERED INDEX ' + @idx_name + ' ON ' + @table_name + '(' + @col_name + ');'

EXECUTE sp_executesql @cmd1

       FETCH NEXT FROM db_cursor INTO @table_name, @col_name, @idx_name;
END  

CLOSE db_cursor; 
DEALLOCATE db_cursor;

SQL Find tables without Clustered Indexes

USE AdventureWorks ----Replace AdventureWorks with your DBName
GO

SELECT DISTINCT [TABLE] = OBJECT_NAME(OBJECT_ID)
FROM SYS.INDEXES
WHERE INDEX_ID = 0
AND OBJECTPROPERTY(OBJECT_ID,'IsUserTable') = 1
ORDER BY [TABLE]
GO