May 30, 2003 at 12:12 pm
Is there a way to drop all statistics from a table at once without having to list them out?
BY
June 2, 2003 at 8:00 am
This was removed by the editor as SPAM
June 2, 2003 at 9:23 am
Don't know abt dropping all the STATS, but you can definitely update the stats for all the tables with UPDATE STATS. (It drops and re-creates the STATS)
Here is the script to update STATS for all Tables in the DB
DECLARE @tablename varchar(30)
DECLARE @tablename_header varchar(75)
DECLARE tnames_cursor CURSOR FOR SELECT name FROM sysobjects
WHERE type = 'U'
OPEN tnames_cursor
FETCH NEXT FROM tnames_cursor INTO @tablename
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SELECT @tablename_header = "Updating " +
RTRIM(UPPER(@tablename))
PRINT @tablename_header
EXEC ("UPDATE STATISTICS " + @tablename )
EXEC ("sp_recompile " + @tablename )
END
FETCH NEXT FROM tnames_cursor INTO @tablename
END
PRINT " "
PRINT " "
SELECT @tablename_header = "************* NO MORE TABLES" +
" *************"
PRINT @tablename_header
PRINT " "
PRINT "Statistics have been updated for all tables."
DEALLOCATE tnames_cursor
GO
Shas3
June 2, 2003 at 1:28 pm
Just curious, why do you want to drop all statistics?
Be great!
Michael
Be great!
Michael
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply