Find size of all tables in a database
-
Comments posted to this topic are about the item Find size of all tables in a database
-
I think while this is a good script...the build it report for max storage used by tables is faster if you needed a quick look..
of course the query can be twiked...to see size in mb or gb etc..but then I guess the query used by build in report can be captured by profier and twiked too..
any ideas!!:-P
-
good script, but it's not suit SQLServer2005;
-
it works fine for me using it on 2005...post your errors..
-
The same can be done using sp_msforeachtable
See below script
---------------------------------------------------------------------
Create table #tmptblsize
(table_name varchar(50),
rows int,
reserved varchar(50),
actualdatasize varchar(50),
indexsize varchar(50),
unused varchar(50))
EXEC sp_MSforeachtable 'insert into #tmptblsize exec sp_spaceused ''?'''
select * from #tmptblsize
drop table #tmptblsize
--------------------------------------------------------------------
Chandu
-
That is the same error I see on our 2005 server.
Msg 15009, Level 16, State 1, Procedure sp_spaceused, Line 62
The object 'ComplaintCodes' does not exist in database 'CFS' or is invalid for this operation.
-
Hi,
I used your script and it failed on AdventureWorks2008R2.
The script supplied by chandu.ade works fine. I'm using it on more environments.
Thanks
Igor Micev,My blog: www.igormicev.com
-
To get the scrip to work with AdventureWorks2008R2 I changed the insert into @v_Table to:
Insert Into @v_Table
Select Table_Schema + '.' + Table_Name From Information_Schema.Tables Where Table_Type = 'BASE TABLE'
Unfortunatley sp_spaceused doesn't return the schema!
-
Hi All,
I'm gettting this error from Suba' script.
Errors:
Msg 208, Level 16, State 1, Line 11
Invalid object name 'Information_Schema.Tables'.
Please advise.
Thanks.
-
Hi Peter,
Information_Schema.Tables is a system view. It is available as part of all sql server databases. Kindly let me as to which version of sql server you are using.
-
tweaked your code little to include size in MB and also for all schemas.
Declare @v_TableName Varchar(100)
Declare @v_Table Table (Table_Name Varchar(100))
Declare @v_TableSize Table (
Table_Name Varchar(100),
rows BigInt,
Reserved Varchar(50),
ActualDataSize Varchar(50),
IndexSize Varchar(50),
Unused Varchar(50)
)
Insert Into @v_Table
Select Table_Schema + '.' + Table_Name From Information_Schema.Tables Where Table_Type = 'BASE TABLE'
Select @v_TableName = Table_Name From @v_Table
While Exists (Select 1 From @v_Table)
Begin
Insert Into @v_TableSize exec sp_spaceused @v_TableName
Delete From @v_Table Where Table_Name = @v_TableName
Select @v_TableName= Table_Name From @v_Table
End
--Select replace(ActualDataSize, ' KB', ''), * From @v_TableSize Order By rows Desc
Select *, Cast(round(convert(decimal(10,2), replace(ActualDataSize, ' KB', ''))/1024.00,2) as decimal(10,2)) as 'Size in MB' From @v_TableSize Order By convert(bigint, replace(ActualDataSize, ' KB', '')) Desc
Go
-
suba.sathyanathan 40131 (10/18/2012)
Hi Peter,Information_Schema.Tables is a system view. It is available as part of all sql server databases. Kindly let me as to which version of sql server you are using.
I get the same error, if you're defaulted into a database with a case sensitive collation, you'll need all caps, ie., SELECT * FROM INFORMATION_SCHEMA.TABLES.
-
Cheers for the script, I made a few adjustments to include the schema in the table Name (we have some databases which have multiple schemas). Also made a change to display the information as integers in GB and ordered by Total data consumed of indexes and table size.
Incase anyone has any use for my changes here's my version.
DECLARE @v_TableName VARCHAR(1500)
DECLARE @v_Table TABLE
(
Table_Name VARCHAR(1500)
)
DECLARE @v_TableSize TABLE
(
Table_Name VARCHAR(1000) ,
rows BIGINT ,
Reserved VARCHAR(500) ,
ActualDataSize VARCHAR(500) ,
IndexSize VARCHAR(500) ,
Unused VARCHAR(500)
)
INSERT INTO @v_Table
SELECT TABLE_SCHEMA + '.' + Table_Name
FROM Information_Schema.Tables
WHERE Table_Type = 'BASE TABLE'
SELECT @v_TableName = Table_Name
FROM @v_Table
WHILE EXISTS ( SELECT 1
FROM @v_Table )
BEGIN
INSERT INTO @v_TableSize
EXEC sp_spaceused @v_TableName
DELETE FROM @v_Table
WHERE Table_Name = @v_TableName
SELECT @v_TableName = Table_Name
FROM @v_Table
END
SELECT Table_Name ,
rows ,
(CAST(REPLACE(reserved, 'KB', '') AS INT)/1000) AS 'Total Used GB' ,
(CAST (REPLACE(ActualDataSize, 'KB', '') AS INT)/1000) AS 'Table Data GB' ,
(CAST (REPLACE(IndexSize, 'KB', '') AS INT)/1000) AS 'Index Data GB' ,
(CAST(REPLACE(unused, 'KB', '') AS INT)/1000) AS 'Unused Allocation'
FROM @v_TableSize
ORDER BY 'Total Used GB' DESC
Go
-
DECLARE @v_TableSize TABLE (
Table_Name VARCHAR(100)
,ROWS BIGINT
,Reserved VARCHAR(50)
,ActualDataSize VARCHAR(50)
,IndexSize VARCHAR(50)
,Unused VARCHAR(50)
)
INSERT INTO @v_TableSize
EXEC sys.sp_MSforeachtable 'sp_spaceused ''?'''
SELECT *
FROM @v_TableSize
i think this is enough
Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url] -
Regardless of which method used, you should really look up the data types & sizes in BOL so you can properly declare the columns / variables ...
for INFORMATION_SCHEMA.TABLES:
TABLE_SCHEMA nvarchar(128)
TABLE_NAME sysname
for sp_spaceused:
name nvarchar(128)
rows char(11)
reserved varchar(18)
data varchar(18)
index_size varchar(18)
unused varchar(18)
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply