February 23, 2012 at 8:17 am
Hi,
I am planning to get more diskspace for my sqlserver box.
Before that i would like do some statistics on space usage of my db. so that i can give a report to get more disk volume.
i need to a code which can iterate through out of my db and
result has to be inserted one table
how can use
sp_spaceused 'table'
February 23, 2012 at 8:20 am
This is what I use to get my inventory:
Declare @DbName sysname
Select @DbName = db_name()
--'DDBAStatistics'
declare @DbId int
Select @DbId = db_id(@DbName)
select @DbName as Requested_DbName
, @DbId as Requested_DbId
, db_id() as Current_DbId
, object_id('Provide specific object name if needed') as T_SSC_ObjectID
Select quotename(object_schema_name(object_id, @DbId)) + '.' + quotename(object_name(object_id, @DbId)) as TbName
, Page_count * 8 / 1024 as IxSizePages_MB
, *
from SYS.DM_DB_INDEX_PHYSICAL_STATS(@DbId, NULL, NULL, NULL, 'detailed')
order by IxSizePages_MB desc
, avg_fragmentation_in_percent desc
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 24, 2012 at 12:10 pm
check below link to Find Table Usage
http://www.sqlservercentral.com/scripts/Miscellaneous/31553/
February 24, 2012 at 5:16 pm
hmm, not sure I'd use the index physical stats dmv to get table usages, this should work though
selectobject_name(p.object_id) as TableName
, p.rows
, a.total_pages * 8 as TotalKB
, a.used_pages * 8 as UsedKB
, a.data_pages * 8 as DataKB
from sys.objects o inner join sys.partitions p on o.object_id = p.object_id
inner join sys.allocation_units a
on
case a.type
when 1 then p.hobt_id
when 3 then p.hobt_id
when 2 then p.partition_id
end = a.container_id
where o.is_ms_shipped <> 1
order by p.rows desc
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply