Practical sql: Reorganizing a database by adding clusted indexes to tables

Data | database | index
--
--Arrangedb.sql Reorganization Database
--
--Copyrights @2003.12.29 Digital-Management System Co., Ltd.
--
--Restructuring process: Add clustered index to all user tables so that SQL Server will restructure the table and then
--and then delete the index
--
--Use time: daily maintenance, recommend a weekly reorganization.
--
--NOTE: Please do database shrink, UPDATE statistics, RE index, and integrity check

--drop Table #dcms_index_info
--temporary table to use
CREATE TABLE #dcms_index_info
(
Index_name sysname,
Index_description varchar (210),
Index_keys varchar (2078)
)

--Setting up the environment
Set NOCOUNT on
--Program Start: Variable
DECLARE @sql varchar (500)
DECLARE @TableName varchar (20)
DECLARE @ErrorSave int

--processing with all user tables
DECLARE Cursor1 Cursor
For
Select name from sysobjects where xtype= ' U ' ORDER by name

Open Cursor1
FETCH NEXT from Cursor1 into @TableName

While @ @fetch_status = 0
Begin
--in order to detect whether there are create_date this column in the table, easy to fly in the table, if not skip, but processing
There should be a better way, I'm using it for the time being. Don't want to use sp_columns because you don't want to build a watch, slow down.
If ColumnProperty (object_id (@TableName), ' create_date ', ' PRECISION ') is not null
Begin
print ' Process table ' + @TableName

--Get the index information for the current table
Delete #dcms_index_info
Set @sql = ' sp_helpindex ' + @TableName + ' '
INSERT INTO #dcms_index_info exec (@sql)

--Find out if there is already a clustered index (clustered), if it has not done, in the easy to fly in the clustered index is a minority
Select Index_name from #dcms_index_info where index_description like ' clustered% '

If @ @ROWCOUNT = 0
Begin
Set @sql = ' Create clustered index DcTempIndex001 on ' + @TableName + ' (create_date) '
EXEC (@sql)

Set @sql = ' DROP index ' + @TableName + '. DcTempIndex001 '
EXEC (@sql)
End
End

FETCH NEXT from Cursor1 into @TableName
End

drop table #dcms_index_info
Close Cursor1
Deallocate Cursor1