DBCC ShowContig With TableResults

I've looked through BOL, and tried all the samples I could find, but I can't seem to get it to work. I need either the results from ShowContig or more specifically the Scan Density number. I need this in a table or a recordset so I can get to it from an .exe. So far I haven't been able to get this to work.

This needs to work with both SQL 7.0 & 2000. Some of the options aren't avaible under 7.0, and the parameters differ slightly.

My code runs it, but doesn't put the results into the temp table.

Does anyone know the way to simply get the Scan Density value without using ShowContig?

Here's the code I've got so far:
-- SQL
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)

INSERT INTO #fraglist
(ObjectName,
ObjectId,
IndexName,
IndexId,
Lvl,
CountPages,
CountRows,
MinRecSize,
MaxRecSize,
AvgRecSize,
ForRecCount,
Extents,
ExtentSwitches,
AvgFreeBytes,
AvgPageDensity,
ScanDensity,
BestCount,
ActualCount,
LogicalFrag,
ExtentFrag)

EXEC ('DBCC SHOWCONTIG (270624007, 2)
WITH TABLERESULTS, NO_INFOMSGS')
Select * from #fraglist
Drop table #fraglist

-- Output
DBCC SHOWCONTIG scanning 'tbInventory' table...
Table: 'tbInventory' (270624007); index ID: 2, database ID: 5
LEAF level scan performed.
- Pages Scanned........................: 65
- Extents Scanned.......................: 9
- Extent Switches.......................: 8
- Avg. Pages per Extent..................: 7.2
- Scan Density [Best Count:Actual Count]......: 100.00% [9:9]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 11.11%
- Avg. Bytes Free per Page................: 112.8
- Avg. Page Density (full)................: 98.61%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

(0 row(s) affected)

ObjectName ObjectId IndexName IndexId Lvl CountPages CountRows MinRecSize MaxRecSize AvgRecSize ForRecCount Extents ExtentSwitches AvgFreeBytes AvgPageDensity ScanDensity BestCount ActualCount LogicalFrag ExtentFrag
---------------------------------------------------------------------------- ---- ---------------------------------------------------------------------------- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----- ---- ----- ------- ---- ---- ------- -------

(0 row(s) affected)

Thanks!Hi

I ran the script that you posted on SQL Server 2000, however I changed the DBCC command to :

EXEC ('DBCC SHOWCONTIG (''authors'') WITH TABLERESULTS, NO_INFOMSGS')

Since I don't have your table, I used authors in Pubs. This worked fine:

ObjectName ObjectId IndexName
----- ---- ------
authors 1977058079 UPKCL_auidind

I didn't include the whole output, it just wraps around.

For SQL Server 7.0 you'll need to do some coding:

set nocount on

create table #Density(line varchar(80))
create table #contig (tb varchar(40), value float, description varchar(30))

declare @.cmd varchar(250)
declare @.tb sysname
declare @.id int

declare tb_cur cursor
for
select table_name,
object_id(table_name)
from Information_Schema.tables
where table_type = 'base table'

open tb_cur

fetch next from tb_cur into @.tb, @.id

while @.@.fetch_status = 0 begin
select @.cmd = 'use ' + db_name()
+ ' insert #Density EXECUTE master..xp_cmdshell ''isql -E -Snike -dSDMT -Q"dbcc showcontig (' + convert(varchar(100),@.id) + ')"'''

exec (@.cmd)

insert #contig
SELECT @.tb,
CONVERT(FLOAT,REPLACE(SUBSTRING(line, PATINDEX('%: %', line) + 2, 6),'%','')),
CONVERT(VARCHAR(30),
CASE
WHEN line LIKE '%Pages Scanned%' THEN 'Pages Scanned'
WHEN line LIKE '%Extents Scanned%' THEN 'Extents Scanned'
WHEN line LIKE '%Extent Switches%' THEN 'Extent Switches'
WHEN line LIKE '%Avg. Pages per %' THEN 'Avg. Pages per Extent'
WHEN line LIKE '%Scan Density%Best%' THEN 'Scan Density'
WHEN line LIKE '%Logical Scan%' THEN 'Logical Scan Fragmentation'
WHEN line LIKE '%Extent Scan%' THEN 'Extent Scan Fragmentation'
WHEN line LIKE '%Avg. Bytes Free%' THEN 'Avg. Bytes Free per Page'
WHEN line LIKE '%Avg. Page Density%' THEN 'Avg. Page Density (full)'
ELSE Line
END)
FROM #Density
WHERE line LIKE '%- %'

truncate table #Density
fetch next from tb_cur into @.tb, @.id
end
close tb_cur
deallocate tb_cur

select *
from #contig

drop table #Density
drop table #contig
go

It's not the fastest code but it works.|||I've been working with your code (thank you!) and also doing some other testing. This is interesting...

-- SQL2000
EXEC ('DBCC SHOWCONTIG (''authors'') WITH TABLERESULTS, NO_INFOMSGS')

Produces 1 row of data, looks like you did a select top 1 * from a table.

-- SQL7
EXEC ('DBCC SHOWCONTIG (117575457) WITH TABLERESULTS, NO_INFOMSGS')

or
dbcc showcontig (117575457, 1) with TableResults

Produces:
DBCC SHOWCONTIG scanning 'authors' table...
Table: 'authors' (117575457); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 6008.0
- Avg. Page Density (full)................: 25.77%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

It doesn't look like SQL7 is honoring the TableResults parm.

Unfortunately the example you provided (which does work) runs extremely slow on our server. Not sure exactly why, none of it looks that complicated. I'm guessing it's because we are shelling out to isql??|||Is there any way to use OpenRowSet with this DBCC Command?