April 29, 2008 at 7:45 am
We are puzzled, wondering and frustrated 😉 about what is wrong with the code below. It executes perfectly on many DBs we have but fails on some throwing the error “Incorrect synatx near the keyword ‘with’. This a production environment (SQL Cluster, Replication, etc). If you need more details I can provide. Thanks
DECLARE @Database VARCHAR(100)
DECLARE @Table VARCHAR(100)
DECLARE @cmd NVARCHAR(300)
DECLARE @fillfactor INT
SET @fillfactor = 70
SET @Database = ‘TestOnly’
SET @cmd = ‘DECLARE TableCursor CURSOR FOR SELECT table_catalog + ”.” + table_schema + ”.” + table_name as tableName
FROM ‘ + @Database + ‘.INFORMATION_SCHEMA.TABLES WHERE table_type = ”BASE TABLE”’
EXEC (@cmd)
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @Table
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = ‘ALTER INDEX ALL ON ‘ + @Table + ‘ REBUILD WITH (FILLFACTOR = ‘ + CONVERT(VARCHAR(3),@fillfactor) + ‘)’
EXEC (@cmd)
FETCH NEXT FROM TableCursor INTO @Table
END
CLOSE TableCursor
DEALLOCATE TableCursor
April 29, 2008 at 8:26 am
You may have a table name that needs square brackets.
[font="Courier New"]DECLARE @Database VARCHAR(100)
DECLARE @Table VARCHAR(100)
DECLARE @cmd NVARCHAR(300)
DECLARE @fillfactor INT
SET @fillfactor = 70
SET @Database = ‘TestOnly’
SET @cmd = ‘DECLARE TableCursor CURSOR FOR SELECT ”[” + table_catalog + ”].[” + table_schema + ”].[” + table_name + ”]” as tableName
FROM [‘ + @Database + ‘].INFORMATION_SCHEMA.TABLES WHERE table_type = ”BASE TABLE”’
EXEC (@cmd)
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @Table
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = ‘ALTER INDEX ALL ON ‘ + @Table + ‘ REBUILD WITH (FILLFACTOR = ‘ + CONVERT(VARCHAR(3),@fillfactor) + ‘)’
EXEC (@cmd)
FETCH NEXT FROM TableCursor INTO @Table
END
CLOSE TableCursor
DEALLOCATE TableCursor[/font]
April 30, 2008 at 1:36 pm
You could also use the QUOTENAME function for your object names.
http://msdn.microsoft.com/en-us/library/ms176114.aspx
I have a stored procedure that you could use to dynamically rebuild or reorganize indexes. It uses the DMV sys.dm_db_index_physical_stats to check the index fragmentation.
http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
Ola Hallengren
May 1, 2008 at 9:37 am
Thanks Michael that seems to be the case when I break a tablename and put a space in there. I'm not sure if 'SQL reserved' names apply here either becuase our initial script worked eventhough sql reserved words exist as tablenames e.g key 'key', 'address', etc.
Ola, I will try/test your sp sometime today, does your script takes into account sql reserved words? I'm leaning towards this as the cause for our failing code. Thanks as well.
May 1, 2008 at 10:11 am
Yes, I think that will work fine.
May 1, 2008 at 9:04 pm
I have just run into this very problem and putting the table name enclosed by [] works including reserved words as tables names and spaces in table names.
I use a procedure from this post by Tom Pullen to do my index rebuilds
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply