SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Show Contig SQL Server 7
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

elwoos
Flowing Fount of Yak Knowledge

United Kingdom
2050 Posts

Posted - 05/20/2005 :  08:14:25  Show Profile  Reply with Quote
After some of the recent discussions on Index fragmentation I've produced this code to do a show contig on all the tables in a specific database on SQL Server 7.

It can probably be made more generic for those interested


CREATE PROCEDURE dbo.sproc_SHOWCONTIG 
AS
CREATE TABLE #Tables
(
 Table_ID INT IDENTITY(1, 1) NOT NULL,
 TableName SYSNAME NOT NULL
)

-- Put all the (user) table names into at temporary table - in order so that the output is in order
INSERT INTO #Tables (TableName) SELECT NAME FROM Shire2.dbo.sysobjects where type = 'U' order by NAME

DECLARE @Current as SYSNAME 
--DECLARE @SQL as nvarchar(4000)
DECLARE @ID int


WHILE EXISTS (SELECT 1 FROM #Tables)
	BEGIN
--print 'in BEGIN'
		-- get current table into a variable name		
		SET @CURRENT =  (SELECT TOP 1 TableName FROM #Tables)
--print 'Current = ' + @current
		SET @ID = OBJECT_ID(@CURRENT)
		DBCC SHOWCONTIG (@ID)
--		EXEC sp_executesql @SQL
		DELETE FROM #Tables WHERE TableName = @CURRENT
	END



GO


A sarcasm detector, what a great idea.

Kristen
Test

United Kingdom
22431 Posts

Posted - 05/26/2005 :  01:54:24  Show Profile  Reply with Quote
I use one which reindexes/defrags based on whether the CONTIG is over/under 80% based on:

http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=444

Kristen
Go to Top of Page

elwoos
Flowing Fount of Yak Knowledge

United Kingdom
2050 Posts

Posted - 05/26/2005 :  02:54:57  Show Profile  Reply with Quote
EEEK!! Cursors! Spawn of the DEVIL I tell you

Cheers Kristen

steve

A sarcasm detector, what a great idea.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.36 seconds. Powered By: Snitz Forums 2000