I'm trying to run the recommended SQL maintenance on my Dynamics GP databases. Where can I find the reindex.sql and recomp.sql scripts if they are not in the c:\program files\microsoft dynamics\gp\sql\util folder?
*This post is locked for comments
I have the same question (0)
Copy and paste the below into a query window and 'save as' into the utilities folder.
Recomp.sql
/*Count : 2 */
/*Begin_Recompile*/
declare
@cStatement varchar(255)declare
T_cursor CURSOR for select 'sp_recompile [' + convert(varchar(64),name) + ']' from sysobjects where type = 'U' and uid = 1set nocount onOPEN
T_cursorFETCH
NEXT FROM T_cursor INTO @cStatementWHILE
(@@FETCH_STATUS <> -1)begin
EXEC (@cStatement) FETCH NEXT FROM T_cursor INTO @cStatementend
DEALLOCATE
T_cursor/*End_Recompile*/
/*Begin_UpdateStats*/
declare
@cStats varchar(255)declare
T_cursor CURSOR for select 'update statistics [' + convert(varchar(64),name) + ']' from sysobjects where type = 'U' and uid = 1set nocount onOPEN
T_cursorFETCH
NEXT FROM T_cursor INTO @cStatsWHILE
(@@FETCH_STATUS <> -1)begin
EXEC (@cStats) FETCH NEXT FROM T_cursor INTO @cStatsend
DEALLOCATE
T_cursor/*End_UpdateStats*/
Reindex.sql
/*Count : 1 */
declare
@tablename char(255)DECLARE t_cursor CURSOR for select 'DBCC DBREINDEX([' + o.name + '], '''', 0)' fromsysobjects o, syscolumns c where o.id = c.id and o.type = 'U' and c.name like '%DEX_ROW_ID%' set NOCOUNT onopen t_cursor FETCH NEXT FROM t_cursor INTO @tablename while (@@fetch_status <> -1) begin if (@@fetch_status <> -2) begin exec (@tablename)end FETCH NEXT FROM t_cursor into @tablenameendDEALLOCATE
t_cursor
Supplied as is - check against a test database first to ensure the above is complete and you are happy with the results.
Best regards,
Share
Report
All responses (
Answers (
2