Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-01-22 : 10:51:26
|
| Bill writes "I've written an application (exe) that will cycle through all of our servers and all of the databases on each server and perform a reindex on all indexes for all tables. I'd like however to utilize ShowContig to decide whether or not they need it prior to running the reindex as some of our tables have up to 16.5 million rows. I've tried using With TableResults but can't seem to get it to work correctly. 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. 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!" |
|
|
PiecesOfEight
Posting Yak Master
200 Posts |
Posted - 2002-01-22 : 12:42:18
|
| This works fine for me in 2000, but "with tableresults" is new in 2000, so it won't work at all in 7.0. I'm not sure whether there is another approach you can take for 7.0 |
 |
|
|
|
|
|
|
|
Need Scan Density, or DBCC ShowContig output as resultset