I found this in this history:
Posted by Ian Stewart replied on 01-18-2010 3:32 AM
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 = 1
set nocount on
OPEN T_cursor
FETCH NEXT FROM T_cursor INTO @cStatement
WHILE (@@FETCH_STATUS <> -1)
begin
EXEC (@cStatement)
FETCH NEXT FROM T_cursor INTO @cStatement
end
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 = 1
set nocount on
OPEN T_cursor
FETCH NEXT FROM T_cursor INTO @cStats
WHILE (@@FETCH_STATUS <> -1)
begin
EXEC (@cStats)
FETCH NEXT FROM T_cursor INTO @cStats
end
DEALLOCATE T_cursor
/*End_UpdateStats*/
Reindex.sql
/*Count : 1 */
declare @tablename char(255)
DECLARE t_cursor CURSOR for
select 'DBCC DBREINDEX([' + o.name + '], '''', 0)'
from
sysobjects o, syscolumns c
where o.id = c.id and o.type = 'U' and c.name like '%DEX_ROW_ID%'
set NOCOUNT on
open 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 @tablename
end
DEALLOCATE t_cursor
Supplied as is - check against a test database first to ensure the above is complete and you are happy with the results.
Kiind regards,
Leslie
Share
Report
All responses (
Answers (
65,271
