DECLARE @ownername SYSNAME
DECLARE @tablename SYSNAME
DECLARE @indexname SYSNAME
DECLARE @sql NVARCHAR(4000)
DECLARE dropindexes CURSOR FOR
SELECT indexes.name, objects.name, schemas.name
FROM sys.indexes
JOIN sys.objects ON indexes.OBJECT_ID = objects.OBJECT_ID
JOIN sys.schemas ON objects.schema_id = schemas.schema_id
WHERE indexes.index_id > 0
AND indexes.index_id < 255
AND objects.is_ms_shipped = 0
AND NOT EXISTS (SELECT 1 FROM sys.objects WHERE objects.name = indexes.name)
ORDER BY objects.OBJECT_ID, indexes.index_id DESC
SELECT * FROM sys.stats
OPEN dropindexes
FETCH NEXT FROM dropindexes INTO @indexname, @tablename, @ownername
WHILE @@fetch_status = 0
BEGIN
SET @sql = N'DROP INDEX '+QUOTENAME(@ownername)+'.'+QUOTENAME(@tablename)+'.'+QUOTENAME(@indexname)
PRINT @sql
EXEC sp_executesql @sql
FETCH NEXT FROM dropindexes INTO @indexname, @tablename, @ownername
END
CLOSE dropindexes
DEALLOCATE dropindexes
GO
DECLARE @ownername SYSNAME
DECLARE @tablename SYSNAME
DECLARE @statsname SYSNAME
DECLARE @sql NVARCHAR(4000)
DECLARE dropstats CURSOR FOR
SELECT stats.name, objects.name, schemas.name
FROM sys.stats
JOIN sys.objects ON stats.OBJECT_ID = objects.OBJECT_ID
JOIN sys.schemas ON objects.schema_id = schemas.schema_id
WHERE stats.stats_id > 0
AND stats.stats_id < 255
AND objects.is_ms_shipped = 0
ORDER BY objects.OBJECT_ID, stats.stats_id DESC
OPEN dropstats
FETCH NEXT FROM dropstats INTO @statsname, @tablename, @ownername
WHILE @@fetch_status = 0
BEGIN
SET @sql = N'DROP STATISTICS '+QUOTENAME(@ownername)+'.'+QUOTENAME(@tablename)+'.'+QUOTENAME(@statsname)
EXEC sp_executesql @sql
--PRINT @sql
FETCH NEXT FROM dropstats INTO @statsname, @tablename, @ownername
END
CLOSE dropstats
DEALLOCATE dropstats
|