Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement