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;
No comments:
Post a Comment