SQLTeam.com | Weblogs | Forums

Exclude Tables on Update Statistics script

sql2008r2

#1

I have a script that I came across which Updates Statistics for a database. The database has a handful of tables that have a '' at the end (i.e. 'Employee Database List', 01_2013$). The script reports errors on these tables "Msg 102, Level 15, State 1, Line 7
Incorrect syntax near '03_2013$'.
Msg 319, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon."

Here is the script I am using:
--Update Statistics All Tables

USE HRxxx -- Change desired database name here
GO
SET NOCOUNT ON
GO
DECLARE updatestats CURSOR FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = 'BASE TABLE'

OPEN updatestats

DECLARE @tablename NVARCHAR(128)
DECLARE @Statement NVARCHAR(300)

FETCH NEXT FROM updatestats INTO @tablename
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT N'UPDATING STATISTICS ' + @tablename
SET @Statement = 'UPDATE STATISTICS ' + @tablename + ' WITH FULLSCAN'
EXEC sp_executesql @Statement
FETCH NEXT FROM updatestats INTO @tablename
END

CLOSE updatestats
DEALLOCATE updatestats
GO
SET NOCOUNT OFF
GO

--I'd like to either exclude these tables or find a way to run it without the errors being generated. Appreciate feedback and replies.


#2

Add brackets around the table name:

SET @Statement = 'UPDATE STATISTICS [' + @tablename + '] WITH FULLSCAN'


#3

To exclude tables, add them to the WHERE clause:

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME NOT IN ('table1', 'table2', ...)


#4

Thanks, it did the trick!


#5

Thanks Tara!


#6

If you want to exclude tables that contain $, you can do that directly without having to list each one:

where TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME NOT LIKE '%$%'