September 23, 2007 at 4:48 pm
Comments posted to this topic are about the item Simple script to ReIndex complete database
SQL DBA.
April 2, 2008 at 10:39 am
This is the same script without the overhead of the cursor and taking into account that views are also in the information_schema.tables view (and should not be returned byt the query:
DECLARE @TableName varchar(255)
SET @TableName = (SELECT TOP 1 TABLE_NAME FROM information_schema.tables WHERE table_name not like 'sys%' and table_name <>'dtproperties' and TABLE_TYPE = 'BASE TABLE' order by TABLE_NAME asc)
WHILE @TableName is not null
BEGIN
PRINT 'Reindexing ' + @TableName
BEGIN TRY
DBCC DBREINDEX(@TableName,' ',0)-- change fill factor here as per your requirement
END TRY
BEGIN CATCH
PRINT 'Error reindexing ' + @TableName
END CATCH
SET @TableName = (SELECT TOP 1 TABLE_NAME FROM information_schema.tables WHERE table_name not like 'sys%' and table_name <>'dtproperties' and TABLE_TYPE = 'BASE TABLE' and cast(table_name as varchar(255)) > @TableName order by TABLE_NAME asc)
END
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy