Table size in DB
-
Does anyone know how to get specific table size and the ratio percent or actual MB of all tables in a particular DB?
-
did you try sp_spaceused ? (check bol)
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
-
No I didn't thanks this seems to give me what I need.
-
create table #spacedetails (name varchar(100),totrows bigint,reserved varchar(30), data varchar(30),index_size varchar(30), unused varchar(30))
insert into #spacedetails
exec sp_MSForeachtable @command1 = "sp_spaceused '?'"
select name,totrows, replace(data,'KB','') as 'Data in KB',
replace(Index_Size,'KB','') as 'Index Size in KB' from #spacedetails order by name
drop table #spacedetails
You can use this if you want.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
You can get your required information using following pretty simple steps:
1. Open enterprise manger
2. Select the required database
3. Select TaskPad option from view menu
4. And view the detail table info (from TableInfo tab)
Thanks,
Irfan Baig.
-
Try this script. I believe I found it on this site a while ago and works great.
BTW this is a GREAT site!
SET NOCOUNT ON
CREATE TABLE #TBLSize
(Tblname varchar(80),
TblRows int,
TblReserved varchar(80),
TblData varchar(80),
TblIndex_Size varchar(80),
TblUnused varchar(80))
DECLARE @DBname varchar(80)
DECLARE @tablename varchar(80)
SELECT @DBname = DB_NAME(DB_ID())
PRINT 'User Table size Report for (Server / Database): ' + @@ServerName + ' / ' + @DBName
PRINT ''
PRINT 'By Size Descending'
DECLARE TblName_cursor CURSOR FOR
SELECT NAME
FROM sysobjects
WHERE xType = 'U'
OPEN TblName_cursor
FETCH NEXT FROM TblName_cursor
INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #tblSize(Tblname, TblRows, TblReserved, TblData, TblIndex_Size, TblUnused)
EXEC Sp_SpaceUsed @tablename
-- Get the next author.
FETCH NEXT FROM TblName_cursor
INTO @tablename
END
CLOSE TblName_cursor
DEALLOCATE TblName_cursor
SELECT CAST(Tblname as Varchar(30)) 'Table',
CAST(TblRows as Varchar(14)) 'Row Count',
CAST(LEFT(TblReserved, CHARINDEX(' KB', TblReserved)) as int) 'Total Space (KB)',
CAST(TblData as Varchar(14)) 'Data Space',
CAST(TblIndex_Size as Varchar(14)) 'Index Space',
CAST(TblUnused as Varchar(14)) 'Unused Space'
FROM #tblSize
Order by 'Total Space (KB)' Desc
PRINT ''
PRINT 'By Table Name Alphabetical'
/*SELECT CAST(Tblname as Varchar(30)) 'Table',
CAST(TblRows as Varchar(14)) 'Row Count',
CAST(LEFT(TblReserved, CHARINDEX(' KB', TblReserved)) as int) 'Total Space (KB)',
CAST(TblData as Varchar(14)) 'Data Space',
CAST(TblIndex_Size as Varchar(14)) 'Index Space',
CAST(TblUnused as Varchar(14)) 'Unused Space'
FROM #tblSize
Order by 'Table'
*/
DROP TABLE #TblSize
-
This script should do what you want.
Script to analyze table space usage
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61762
-
Browse the scripts section of this site, there's several that might do the trick for you, or at least give you the framework to write something that does exactly what you want.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font] -
Ack! A cursor?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Please post your non-cursor solution.
-
Look at the code for sp_spaceused to get the exact. But here is a quick, and fairly accurate way.
SELECT so.Name, SUM(dpages)*8/1024. pagesMB, SUM(Reserved)*8/1024. reservedMB
FROM sysobjects so, sysindexes si
where so.type='u'
and so.id=si.id
group by so.name
order by so.name
Brian
-
O-ki-do-ki
DBCC UPDATEUSAGE(0)
--===== "Space Used on Sterioids" -- Created by Jeff Moden -- If "UnusedKB" is negative, it's likely you need to run DBCC UpdateUsage on the table. -- If the RowModCtr is high (contains number of rows inserted/updated/deleted sinse last stats update) -- you might want to run UPDATE STATISICS on those tables. SELECT DBName = DB_NAME(), Owner = USER_NAME(so.UID), TableName = so.Name, TableID = so.ID, MinRowSize = MIN(si.MinLen), MaxRowSize = MAX(si.XMaxLen), ReservedKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Reserved ELSE 0 END) * pkb.PageKB, DataKB = SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.DPages ELSE 0 END) * pkb.PageKB + SUM(CASE WHEN si.IndID IN ( 255) THEN ISNULL(si.Used,0) ELSE 0 END) * pkb.PageKB, IndexKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Used ELSE 0 END) * pkb.PageKB - SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.DPages ELSE 0 END) * pkb.PageKB - SUM(CASE WHEN si.IndID IN ( 255) THEN ISNULL(si.Used,0) ELSE 0 END) * pkb.PageKB, UnusedKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Reserved ELSE 0 END) * pkb.PageKB - SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Used ELSE 0 END) * pkb.PageKB, Rows = SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.Rows ELSE 0 END), RowModCtr = MIN(si.RowModCtr), HasTextImage = MAX(CASE WHEN si.IndID IN ( 255) THEN 1 ELSE 0 END), HasClustered = MAX(CASE WHEN si.IndID IN ( 1 ) THEN 1 ELSE 0 END) FROM dbo.SysObjects so, dbo.SysIndexes si, (--Derived table finds page size in KB according to system type SELECT Low/1024 AS PageKB --1024 is a binary Kilo-byte FROM Master.dbo.spt_Values WHERE Number = 1 --Identifies the primary row for the given type AND Type = 'E' --Identifies row for system type ) pkb WHERE si.ID = so.ID AND si.IndID IN (0, --Table w/o Text or Image Data 1, 255) --Table w/ Text or Image Data AND so.XType = 'U' --User Tables AND PERMISSIONS(so.ID) <> 0 GROUP BY so.Name, so.UID, so.ID, pkb.PageKB ORDER BY ReservedKB DESC
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
This script that i have posted here is a non cursor script from the user side. But intern it calls a server side cursor but it executes faster than your user cursor. and gives details wanted.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
>>but it executes faster than your user cursor
Who's "user cursor" solution are you talking about, Sugesh?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Jeff, I am speaking about the one posted by Charlie.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply