BEGIN
SET NOCOUNT ON
DECLARE @sLogicoMDF VARCHAR(250)
DECLARE @sLogicoLDF VARCHAR(250)
DECLARE @rs CURSOR
DECLARE @sTabla VARCHAR(200)
DECLARE @sSQL VARCHAR(2000)
SET @rs = CURSOR FOR SELECT NAME FROM BASEDATOS..SysObjects WHERE xType = 'U' AND NAME <> 'dtproperties' ORDER BY Name
OPEN @rs
FETCH NEXT FROM @rs INTO @sTabla
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sSQL = 'DBCC DBREINDEX (''BASEDATOS.dbo.' + LTRIM(@sTabla) + ''') WITH NO_INFOMSGS'
EXEC (@sSQL)
FETCH NEXT FROM @rs INTO @sTabla
END
CLOSE @rs
DEALLOCATE @rs
-- Una vez regenerados los índices, compactamos ficheros
SELECT @sLogicoMDF = RTRIM(LTRIM(ISNULL(NAME, ''))) FROM BASEDATOS..SYSFILES WHERE CAST(GroupID AS BIT) = 1
SELECT @sLogicoLDF = RTRIM(LTRIM(ISNULL(NAME, ''))) FROM BASEDATOS..SYSFILES WHERE CAST(GroupID AS BIT) = 0
USE BASEDATOS
IF LEN(@sLogicoMDF) > 0
BEGIN
CHECKPOINT
DBCC SHRINKFILE (@sLogicoMDF, 7)
CHECKPOINT
END
IF LEN(@sLogicoLDF) > 0
BEGIN
CHECKPOINT
DBCC SHRINKFILE (@sLogicoLDF, 7)
CHECKPOINT
END
SET NOCOUNT OFF
END