January 13, 2006 at 12:34 pm
Hello, I am trying to export the results from showcontig into a table, but my table never gets populated. I have also run into the issue where I insert the statement ALL_INDEXES, I get the following error:
Server: Msg 195, Level 15, State 4, Line 2
'ALL_INDEXES' is not a recognized option.
We use sql2000, which tells me that this should be supported. Me and my team are a little stumped. Any ideas would be great!!
thanks inadvanced.
-- Declare variables
USE Northwind
drop table fraglist
go
SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr VARCHAR (255)
DECLARE @objectid INT
DECLARE @indexid INT
DECLARE @frag DECIMAL
DECLARE @maxfrag DECIMAL
DECLARE @sql VARCHAR(255)
-- Decide on the maximum fragmentation to allow
SELECT @maxfrag = 10.0
-- Declare cursor
DECLARE tables CURSOR FOR
SELECT OBJECT_ID(TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
-- Create the 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 the cursor
OPEN tables
-- Loop through all the tables in the database
FETCH NEXT
FROM tables
INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
INSERT INTO fraglist
EXEC ('DBCC SHOWCONTIG ( '+ @tablename + ')
WITH TABLERESULTS, NO_INFOMSGS, ALL_INDEXES')
FETCH NEXT
FROM tables
INTO @tablename
END
select * from fraglist
-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables
January 13, 2006 at 12:56 pm
What is your database compatibiliy level set to ?
sp_dbcmptlevel 'NorthWind'
January 13, 2006 at 1:29 pm
The current compatibility level is 70.
January 13, 2006 at 1:32 pm
SQL 2K is version 80.
That means you are running the database in Sql Server 7.0 compatibility mode, which means SQL2K features aren't available.
January 13, 2006 at 1:35 pm
thank you very much!
EDIT: I just went back and did a little reading and thought that 7 supported showcontig but you needed to search on id rather than name? I know that not all of the options (ALL_INDEXES, FAST, etc...) were supported but shouldn't showcontig still work?
so with removing the ALL_INDEXES I am able to see the table outputed in the results but with no data. any ideas here?
thanks again.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply