-- ============================================= -- Outer cursor to get database name -- ============================================= PRINT ' ' PRINT GetDate() PRINT '*********************' -- Create temp table IF OBJECT_ID('TEMPDB..#tmp_returnValue') IS NOT NULL Drop Table #tmp_returnValue CREATE TABLE #tmp_returnValue (Status int) IF OBJECT_ID('TEMPDB..#tschemaname') IS NOT NULL Drop Table #tschemaname CREATE TABLE #tschemaname (table_name varchar(256),table_schema varchar(256)) DECLARE cursor_getDBname CURSOR Local Forward_Only STATIC READ_ONLY TYPE_WARNING FOR SELECT [name], [state], database_id FROM master.sys.databases WHERE [name] not in ('Master', 'Model', 'msdb', 'tempdb') and databaseproperty(name, N'IsReadOnly') = 0 and compatibility_level = 90 -- and database_id > 21 --and name = 'dba' ORDER BY database_id /*********************************************** ORDER BY is specifically set to database_id, in order to Facitlitate restarting the job with a statement of where database_id > x */ DECLARE @DBname VARCHAR(100) DECLARE @dbid integer DECLARE @string NVARCHAR(4000) DECLARE @string1 NVARCHAR(4000) DECLARE @string2 NVARCHAR(4000) DECLARE @string3 NVARCHAR(4000) DECLARE @Action VARCHAR(20) DECLARE @status INTEGER DECLARE @sys_name VARCHAR(1000) DECLARE @schema VARCHAR(1000) DECLARE @Oname VARCHAR(1000) DECLARE @fillfactor VARCHAR(2) declare @state int SET @fillfactor = 80 OPEN cursor_getDBname FETCH NEXT FROM cursor_getDBname INTO @DBname, @State, @dbid WHILE (@@fetch_status <> -1) BEGIN Print '---------------------------------------------------------------------------' Print 'Database Name = ' + @DBname + ', database_id = ' + Convert(varchar(10),@dbid) + ', State = ' + Convert(varchar(10),@state) --SET @string = 'USE ' + @dbname -- SET @string2 = 'DECLARE curReindex CURSOR FOR ' + -- '(SELECT table_name, table_schema' + -- ' FROM information_schema.tables ' + -- ' WHERE table_type = ' + char(39) + 'BASE TABLE' + char(39) + -- ' AND table_name <> ' + char(39) + 'dtproperties' + char(39) + -- ' AND NOT (table_name LIKE ' + char(39) + 'tmp%' + char(39) + '))' SET @string2 = 'USE [' + @DBname + ']; ' + @string2 set @string2 = 'truncate table #tschemaname; use ['+@dbname+'];' + 'insert into #tschemaname ' + 'SELECT table_name, table_schema' + ' FROM information_schema.tables ' + ' WHERE table_type = ' + char(39) + 'BASE TABLE' + char(39) + ' AND table_name <> ' + char(39) + 'dtproperties' + char(39) + ' AND NOT (table_name LIKE ' + char(39) + 'tmp%' + char(39) + ')' Print @string2 exec (@string2) DECLARE curReindex INSENSITIVE CURSOR FOR select table_name,table_schema from #tschemaname -- EXEC sp_executesql @String2 -- PRINT @String2 OPEN curReindex FETCH NEXT FROM curReindex INTO @sys_name, @schema PRINT @@fetch_status WHILE (@@FETCH_STATUS <> -1) -- ========================================================================= -- This cursor will loop thru the base tables and reindex them -- ========================================================================= IF LEFT(@sys_name,1) = char(39) -- Check for tables with single quotes in the name BEGIN SET @string = 'USE [' + @dbname + ']; ALTER INDEX ALL ON [' + @schema + '].[' + @sys_name + '] REBUILD WITH ( FILLFACTOR = ' + @fillfactor + ', PAD_INDEX = ON ' + ', SORT_IN_TEMPDB = ON)' PRINT @string EXEC SP_EXECUTESQL @string FETCH NEXT FROM curReindex INTO @sys_name, @schema END ELSE BEGIN SET @string = 'USE [' + @dbname + ']; ALTER INDEX ALL ON [' + @schema + '].[' + + @sys_name + '] REBUILD WITH ( FILLFACTOR = ' + @fillfactor + ', PAD_INDEX = ON ' + ', SORT_IN_TEMPDB = ON)' PRINT @string EXEC SP_EXECUTESQL @string FETCH NEXT FROM curReindex INTO @sys_name, @schema END CLOSE curReindex DEALLOCATE curReindex -- End of Inner ---------------------------------------------------- FETCH NEXT FROM cursor_getDBname INTO @DBname, @State, @dbid END CLOSE cursor_getDBname DEALLOCATE cursor_getDBname