October 10, 2011 at 5:26 pm
I found a series of scripts to check table frgamentation, but one of the stored procedures has some parsing errors...issue with brackets ect.. with the stored procedure below. Looking for help to make this run.
Here is the stored procedure:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE USP_DBshowcontig_single_db @name varchar(50)
AS
SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @dbname VARCHAR(20)
DECLARE @sql VARCHAR(1000)
DECLARE @inserttable VARCHAR(3200)
-- Create the table
CREATE TABLE #DBFRAGMENT (
ObjectName VARCHAR (50),
ObjectId INT,
IndexName VARCHAR (100),
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)
create table #tablename (table_name varchar(400))
--DECLARE DB Cursor
DECLARE databases CURSOR FOR
SELECT NAME
FROM MASTER.DBO.SYSDATABASES
WHERE NAME = @NAME
--Open the cursor
OPEN databases
FETCH NEXT FROM databases INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql = 'SELECT TABLE_NAME = NAME FROM ' + @dbname + '..SYSOBJECTS WHERE XTYPE =' + '''' + 'U' + ''''
print @sql
insert into #tablename exec(@sql)
-- Declare cursor
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM #tablename
-- Open the cursor
OPEN tables
-- Loop through all the tables in the database
FETCH NEXT FROM tables INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @TABLENAME
-- Do the showcontig of all indexes of the table
INSERT INTO #DBFRAGMENT
EXEC ('USE ' + @dbname + ' DBCC SHOWCONTIG (''' + @tablename + ''') WITH TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT FROM tables INTO @tablename
END
set @inserttable ='INSERT INTO ADMINDB.DBO.INDEX_INFO_'+@NAME+'(ObjectName,
ObjectId,
IndexName,
IndexId,
Lvl,
CountPages,
CountRows,
MinRecSize,
MaxRecSize,
AvgRecSize,
ForRecCount,
Extents,
ExtentSwitches,
AvgFreeBytes,
AvgPageDensity,
ScanDensity,
BestCount,
ActualCount,
LogicalFrag,
ExtentFrag)
select ObjectName,
ObjectId,
IndexName,
IndexId,
Lvl,
CountPages,
CountRows,
MinRecSize,
MaxRecSize,
AvgRecSize,
ForRecCount,
Extents,
ExtentSwitches,
AvgFreeBytes,
AvgPageDensity,
ScanDensity,
BestCount,
ActualCount,
LogicalFrag,
ExtentFrag
FROM #DBFRAGMENT where ltrim(rtrim(#DBFRAGMENT.indexname))<> ''''
--PRINT @INSERTTABLE
EXEC (@inserttable)
-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables
delete from #tablename
delete from #DBFRAGMENT
FETCH NEXT FROM databases INTO @dbname
END
CLOSE databases
DEALLOCATE databases
drop table #tablename
--Delete the temporary table
DROP TABLE #DBFRAGMENT
GO
SET QUOTED_IDENTIFIER OFF
GO
Appreciate help
October 10, 2011 at 5:32 pm
Gah, there's no way I'm letting that loose on one of my development servers to test it. 🙂 Almost all my instances are overwhelmed.
What's the actual error you're getting?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 10, 2011 at 11:05 pm
October 11, 2011 at 2:57 pm
Thanks for the reply. I gave it a try but no change. I'm getting Server: Msg 105, Level 15, State 1 on Unclosed quotation mark before the character string '(ObjectName,...
which is at:
set @inserttable ='INSERT INTO ADMINDB.DBO.INDEX_INFO_'+@NAME+'(ObjectName,
October 11, 2011 at 3:21 pm
Well It looks like it is around the variable @Name.
When looking for what that variable is...
DECLARE databases CURSOR FOR
SELECT NAME
FROM MASTER.DBO.SYSDATABASES
WHERE NAME = @NAME
Just curious...what is the point of that cursor???
Regardless the error message is pretty clear what the problem is.
FROM #DBFRAGMENT where ltrim(rtrim(#DBFRAGMENT.indexname))<> ''''
should be (you are missing the closing quotation).
FROM #DBFRAGMENT where ltrim(rtrim(#DBFRAGMENT.indexname))<> '''''
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 11, 2011 at 3:24 pm
Change line:
FROM #DBFRAGMENT where ltrim(rtrim(#DBFRAGMENT.indexname))<> ''''' -- ADD A SINGLE QUOTE
To have another single quote. When I pasted this in an editor, the next line with the EXEC was still red. You need to close your INSERT statment in dynamic SQL.
October 11, 2011 at 4:06 pm
Great, thanks that did the trick!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply