April 3, 2012 at 11:37 pm
kindly ignore this post. posted in 2005 section instead. my bad.
April 3, 2012 at 11:40 pm
what errors are you getting?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 3, 2012 at 11:45 pm
Hi,
Seems to be syntax error but i just can't get it right:
error:
Msg 170, Level 15, State 1, Line 9
Line 9: Incorrect syntax near 'BASE'.
Msg 170, Level 15, State 1, Line 43
Line 43: Incorrect syntax near ')
FETCH NEXT
FROM tables
INTO @tablename
END
CLOSE tables
DEALLOCATE tables
SELECT * FROM #FRAGLIST WHERE COUNTPAGES>'.
======
DECLARE @command varchar(1000)
SELECT @command = 'USE [?]
SET NOCOUNT ON
DECLARE @tablename VARCHAR (255)
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
CREATE TABLE #fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)
OPEN tables
FETCH NEXT
FROM tables
INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT
FROM tables
INTO @tablename
END
CLOSE tables
DEALLOCATE tables
SELECT * FROM #FRAGLIST WHERE COUNTPAGES>1000 AND LOGICALFRAG>10
DROP TABLE #fraglist
GO'
EXEC sp_MSforeachdb @command
April 3, 2012 at 11:49 pm
Try adjusting the size of your command variable
DECLARE @command varchar(1000)
DECLARE @command varchar(8000)
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 3, 2012 at 11:54 pm
Hi SQLRNNR,
I think it's due to syntax issue like '. Can't seems to make it work. Anyway that u can help with taking a look?
thanks!
April 4, 2012 at 9:40 pm
For starters - post the exact code that are currently trying to troubleshoot. Based on the error you submitted and the code there, you would get a syntax error due to the variable being too small for the number of characters in your dynamic sql statement.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 4, 2012 at 9:50 pm
thanks. Exact code and error as below:
DECLARE @command varchar(1000)
SELECT @command = 'USE [?]
SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @indexname VARCHAR (128)
DECLARE @execstr VARCHAR (255)
DECLARE @objectid INT
DECLARE @indexid INT
DECLARE @frag DECIMAL
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
CREATE TABLE #fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)
OPEN tables
FETCH NEXT
FROM tables
INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT
FROM tables
INTO @tablename
END
CLOSE tables
DEALLOCATE tables
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag, indexname
FROM #fraglist
WHERE LogicalFrag >10
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
and countpages>1000
OPEN indexes
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag, @indexname
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT '--Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',' + RTRIM(@indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@frag)) + '%'
PRINT '--Table Name: ' + RTRIM(@tablename) + ', Index Name: ' + RTRIM(@indexname) + ')'
SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',' + RTRIM(@indexid) + ')'
PRINT (@execstr)
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag, @indexname
END
CLOSE indexes
DEALLOCATE indexes
DROP TABLE #fraglist
GO'
EXEC sp_MSforeachdb @command
===
error:
A fatal scripting error occurred.
Incorrect syntax was encountered while parsing GO.
April 4, 2012 at 9:54 pm
Take out the line with the GO just before the EXEC command. There appears to be an extraneous character after the GO, plus it ends the batch and your @command variable in the EXEC will be undefined (read, needing to be declared).
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply