use master
go
if exists (select id
from dbo.sysobjects
where id = object_id(N'[dbo].[sp_UpdateSize]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
begin
drop procedure [dbo].[sp_UpdateSize]
print 'PROC DROP: sp_UpdateSize'
end
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create proc sp_UpdateSize
@db varchar(128)
as
/*************************************************************************
Author: Luis Enrique
Date: 20020815
Contact:This email address is being protected from spambots. You need JavaScript enabled to view it.
Params: @DB Nombre de la base de datos
Returns: ejecuta DBCC UPDATEUSAGE para cada indice de cada tabla
**************************************************************************/
set nocount on
from dbo.sysobjects
where id = object_id(N'[dbo].[sp_UpdateSize]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
begin
drop procedure [dbo].[sp_UpdateSize]
print 'PROC DROP: sp_UpdateSize'
end
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create proc sp_UpdateSize
@db varchar(128)
as
/*************************************************************************
Author: Luis Enrique
Date: 20020815
Contact:
Params: @DB Nombre de la base de datos
Returns: ejecuta DBCC UPDATEUSAGE para cada indice de cada tabla
**************************************************************************/
set nocount on
if @db is null
set @db = db_name()
set @db = db_name()
create table #paso (id int identity, sql varchar(8000))
insert into #paso(sql)
select 'dbcc updateusage (' + @db + ', [' +
object_name(id) + '], [' + name +
']) WITH COUNT_ROWS, NO_INFOMSGS '
from sysindexes
where indid > 0
and indid < 255
and (status & 64)=0
and id in (select id from sysobjects where type = 'u')
order by id, indid
select 'dbcc updateusage (' + @db + ', [' +
object_name(id) + '], [' + name +
']) WITH COUNT_ROWS, NO_INFOMSGS '
from sysindexes
where indid > 0
and indid < 255
and (status & 64)=0
and id in (select id from sysobjects where type = 'u')
order by id, indid
declare @ap int,
@sql varchar(8000)
set @ap = 1
while @ap <= (select max(id) from #paso)
begin
select @sql = sql
from #paso
where id = @ap
exec (@sql)
set @ap = @ap + 1
end
@sql varchar(8000)
set @ap = 1
while @ap <= (select max(id) from #paso)
begin
select @sql = sql
from #paso
where id = @ap
exec (@sql)
set @ap = @ap + 1
end
drop table #paso
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
if exists (select id
from dbo.sysobjects
where id = object_id(N'[dbo].[sp_UpdateSize]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
begin
print 'PROC CREATED: sp_UpdateSize'
print 'HOWTO: '
print ' exec sp_UpdateSize DATABASE_NAME '
end
GO
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
if exists (select id
from dbo.sysobjects
where id = object_id(N'[dbo].[sp_UpdateSize]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
begin
print 'PROC CREATED: sp_UpdateSize'
print 'HOWTO: '
print ' exec sp_UpdateSize DATABASE_NAME '
end
GO