Few useful commands

-- Rebuild Indexes
exec sp_msforeachtable 'DBCC DBREINDEX (''?'','' '',90)'

##################################################################
##################################################################
##################################################################

-- check space on all tables

dbcc updateusage(0) with count_rows


create table #tablerows(
tabname varchar(50),
rc varchar(50),
reserved varchar(50),
data varchar(50),
indexes varchar(50),
unused varchar(50)
)

insert into #tablerows exec sp_msforeachtable 'sp_spaceused ''?'''

select
tabname,
sum(cast(rc as int)) as rows,
sum(cast(replace(reserved,' KB','') as int)) as reserved,
sum(cast(replace(data,' KB','') as int)) as data,
sum(cast(replace(indexes,' KB','') as int)) as indexes,
sum(cast(replace(unused,' KB','') as int)) as unused
from #tablerows
group by tabname
order by 4 desc

drop table #tablerows


##################################################################
##################################################################
##################################################################


-- display tables with low scan density

-- 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)


insert into #fraglist exec sp_msforeachtable 'dbcc showcontig (''?'') with tableresults'

select * from #fraglist order by ScanDensity

drop table #fraglist

Hi,<br />sp_msforeachtable stored procedure is undocumented, so its not recommended by Microsoft to be used by anyone[<img src='/community/emoticons/emotion-2.gif' alt='' />][<img src='/community/emoticons/emotion-1.gif' alt='' />].<br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by skeezwiz</i><br /><br />-- Rebuild Indexes<br />exec sp_msforeachtable 'DBCC DBREINDEX (''?'','' '',90)'<br /><br />##################################################################<br />##################################################################<br />##################################################################<br /><br />-- check space on all tables<br /><br />dbcc updateusage(0) with count_rows<br /><br /><br />create table #tablerows(<br />tabname varchar(50), <br />rc varchar(50), <br />reserved varchar(50), <br />data varchar(50), <br />indexes varchar(50), <br />unused varchar(50)<br />)<br /><br />insert into #tablerows exec sp_msforeachtable 'sp_spaceused ''?'''<br /><br />select <br />tabname,<br />sum(cast(rc as int)) as rows, <br />sum(cast(replace(reserved,' KB','') as int)) as reserved, <br />sum(cast(replace(data,' KB','') as int)) as data, <br />sum(cast(replace(indexes,' KB','') as int)) as indexes, <br />sum(cast(replace(unused,' KB','') as int)) as unused <br />from #tablerows<br />group by tabname<br />order by 4 desc<br /><br />drop table #tablerows<br /><br /><br />##################################################################<br />##################################################################<br />##################################################################<br /><br /><br />-- display tables with low scan density<br /><br />-- Create the table<br />CREATE TABLE #fraglist (<br /> ObjectName CHAR (255),<br /> ObjectId INT,<br /> IndexName CHAR (255),<br /> IndexId INT,<br /> Lvl INT,<br /> CountPages INT,<br /> CountRows INT,<br /> MinRecSize INT,<br /> MaxRecSize INT,<br /> AvgRecSize INT,<br /> ForRecCount INT,<br /> Extents INT,<br /> ExtentSwitches INT,<br /> AvgFreeBytes INT,<br /> AvgPageDensity INT,<br /> ScanDensity DECIMAL,<br /> BestCount INT,<br /> ActualCount INT,<br /> LogicalFrag DECIMAL,<br /> ExtentFrag DECIMAL)<br />insert into #fraglist exec sp_msforeachtable 'dbcc showcontig (''?'') with tableresults'<br />select * from #fraglist order by ScanDensity<br />drop table #fraglist<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">

Ranitjain,

I know it is not polite to make a joke about small mistakes people make when using a foreign language, but this one is really funny:

quote:its not recommended to be used by Microsoft

Agreed: Microsoft should not use undocumented stored procedures.

[<img src='/community/emoticons/emotion-2.gif' alt='' />][<img src='/community/emoticons/emotion-2.gif' alt='' />][<img src='/community/emoticons/emotion-2.gif' alt='' />][<img src='/community/emoticons/emotion-2.gif' alt='' />]<br />it's not because of foreign language, its when you are a bit distant[<img src='/community/emoticons/emotion-4.gif' alt='' />]