sp_spaceused
. Unfortunately this SP does not support iterating over all tables in a database, so we needed to leverage another (undocumented) Stored Procedure sp_msForEachTable
.SET NOCOUNT ON
DBCC UPDATEUSAGE(0)
-- Find DB size.
EXEC sp_spaceused
-- Create a table to counts row and sizes.
CREATE TABLE #tbl
(
[name] NVARCHAR(128),
[rows] CHAR(11),
reserved VARCHAR(18),
data VARCHAR(18),
index_size VARCHAR(18),
unused VARCHAR(18)
)
INSERT #tbl EXEC sp_msForEachTable 'EXEC sp_spaceused ''?'''
SELECT * FROM #tbl
-- # of rows.
SELECT SUM(CAST([rows] AS int)) AS [rows]
FROM #tbl
DROP TABLE #tbl
After executing this, you will get the size of all tables in your database.
When i was working with sql and find the size of all tables in SQL Server database then my sql database becomes damage. I was in big trouble sql database recovery was very important for me. Then i have downloaded the sql database recovery tool to repair the sql database file.
ReplyDelete