InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 01:58:30 GMT20http://www.sqlservercentral.com/Forums/Topic436453-110-1.aspxWe use acursor driven proc but just altering it to be #temptbl oriented, you might handle it like this:[code]DECLARE @SQLStatement nvarchar(4000)--DECLARE TableList CURSOR LOCAL FAST_FORWARD READ_ONLY FORDeclare @tmpReindexList table (SeqNo int identity(1,1) not null, SQLStatement nvarchar(4000) not null)insert into @tmpReindexList (SQLStatement) SELECT N'DBCC DBREINDEX (''['+ db_name() + '].' + QUOTENAME(TABLE_SCHEMA) + N'.' + QUOTENAME(TABLE_NAME) + ''')' as SQLStatementFROM INFORMATION_SCHEMA.TABLESWHERE EXISTS ( SELECT * FROM sysindexes WHERE id = OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + N'.' + QUOTENAME(TABLE_NAME)) AND indid IN(0,1) )ORDER BY 1-- OPEN TableListdeclare @SeqNo intWHILE 1 = 1BEGIN --FETCH NEXT FROM TableList INTO @TableName Select top 1 @SeqNo = SeqNo, @SQLStatement=SQLStatement from @tmpReindexList if @@rowcount = 0 break print @SQLStatement EXEC(@SQLStatement) delete from @tmpReindexList where SeqNo = @SeqNoEND-- CLOSE TableList-- DEALLOCATE TableListexec sp_updatestatsdbcc updateusage(0) with count_rows[/code]btw I've kept the commented lines in the code so you can easily see the difference :hehe:Fri, 28 Dec 2007 01:20:22 GMTALZDBAhttp://www.sqlservercentral.com/Forums/Topic436453-110-1.aspxYou could do something like:create table #tables (table_name varchar(80), completed datetime)insert #tablesSELECT TABLE_NAME, nullFROM INFORMATION_SCHEMA.TABLESWHERE TABLE_TYPE = 'BASE TABLE'while exists (select table_name from #tables where completed is null) begin select top 1 @table = table_name from #tables where completed is null ... exec code update #tables set completed = getdate() where table_name = @table endUse this to drive your system. Not sure it's much better than a cursor, but if you change #tables to some permanent table, you can track when things are happening, even restart if the job fails with a new table.Thu, 27 Dec 2007 09:09:45 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic436453-110-1.aspxHey DamonThanks for your reply and i appreciate your time and i did liked your code anyways my lead wants me to remove all the cursors use something else instead of cursor's so i dont want any cursor's Thanksif you can provide a solution without any cursor would be a great helpThu, 27 Dec 2007 08:31:54 GMTDreamszhttp://www.sqlservercentral.com/Forums/Topic436453-110-1.aspxAll that I have in my script library is a cursor-based script, which believe came from the SQL Server 2000 Books Online (BOL).----Begin Script--/*Perform a 'USE ' to select the database in which to run the script.*/-- Declare variablesUSE CGSET NOCOUNT ONDECLARE @tablename VARCHAR (128)DECLARE @execstr VARCHAR (255)DECLARE @objectid INTDECLARE @indexid INTDECLARE @frag DECIMALDECLARE @maxfrag DECIMAL-- Decide on the maximum fragmentation to allowSELECT @maxfrag = 5.0-- Declare cursorDECLARE tables CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'-- Create the tableCREATE TABLE #fraglist ( ObjectName CHAR (255), ObjectId INT, IndexName CHAR (255), IndexId INT, Lvl INT, CountPages INT, CountRows INT, MinRecSize INT, MaxRecSize INT, AvgRecSize INT, ForRecCount INT, Extents INT, ExtentSwitches INT, AvgFreeBytes INT, AvgPageDensity INT, ScanDensity DECIMAL, BestCount INT, ActualCount INT, LogicalFrag DECIMAL, ExtentFrag DECIMAL)-- Open the cursorOPEN tables-- Loop through all the tables in the databaseFETCH NEXT FROM tables INTO @tablenameWHILE @@FETCH_STATUS = 0BEGIN-- Do the showcontig of all indexes of the table INSERT INTO #fraglist EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS') FETCH NEXT FROM tables INTO @tablenameEND-- Close and deallocate the cursorCLOSE tablesDEALLOCATE tables-- Declare cursor for list of indexes to be defraggedDECLARE indexes CURSOR FOR SELECT ObjectName, ObjectId, IndexId, LogicalFrag FROM #fraglist WHERE LogicalFrag >= @maxfrag AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0-- Open the cursorOPEN indexes-- loop through the indexesFETCH NEXT FROM indexes INTO @tablename, @objectid, @indexid, @fragWHILE @@FETCH_STATUS = 0BEGIN PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ', ' + RTRIM(@indexid) + ') - fragmentation currently ' + RTRIM(CONVERT(varchar(15),@frag)) + '%' SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ', ' + RTRIM(@indexid) + ')' EXEC (@execstr) FETCH NEXT FROM indexes INTO @tablename, @objectid, @indexid, @fragEND-- Close and deallocate the cursorCLOSE indexesDEALLOCATE indexes-- Delete the temporary tableDROP TABLE #fraglistGO--End Script--Happy T-SQLingThu, 27 Dec 2007 07:14:07 GMTKey DBAhttp://www.sqlservercentral.com/Forums/Topic436453-110-1.aspxi have a job which is failing my lead wants me to avoid the cursors in that job and modify it any suggestion would be of great help please--DECLARE @Database VARCHAR(255) DECLARE @Table VARCHAR(255) DECLARE @cmd NVARCHAR(500) DECLARE @fillfactor INT SET @fillfactor = 90 DECLARE DatabaseCursor CURSOR FOR SELECT database_name FROM WHERE status='y'AND database_name NOT IN ('master','model','msdb','tempdb') ORDER BY 1 OPEN DatabaseCursor FETCH NEXT FROM DatabaseCursor INTO @Database WHILE @@FETCH_STATUS = 0 BEGIN SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT table_catalog + ''.'' + table_schema + ''.'' + table_name as tableName FROM ' + @Database + '.INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE''' -- create table cursor EXEC (@cmd) OPEN TableCursor FETCH NEXT FROM TableCursor INTO @Table WHILE @@FETCH_STATUS = 0 BEGIN SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')' EXEC (@cmd) FETCH NEXT FROM TableCursor INTO @Table END CLOSE TableCursor DEALLOCATE TableCursor FETCH NEXT FROM DatabaseCursor INTO @Database END CLOSE DatabaseCursor DEALLOCATE DatabaseCursorthanksWed, 26 Dec 2007 11:52:38 GMTDreamsz