Here’s just rowcount but for all tables in all userdbs.
–Run in SQLCMD Mode.
–:Connect SQL1
SET NOCOUNT ON;
USE tempdb;
CREATE TABLE #AllDBRowCounts(DB sysname,SchemaName sysname,TableName sysname,RowCounts bigint);
–:Connect SQL1
SET NOCOUNT ON;
USE tempdb;
CREATE TABLE #AllDBRowCounts(DB sysname,SchemaName sysname,TableName sysname,RowCounts bigint);
INSERT INTO #AllDBRowCounts
EXEC master.sys.sp_MSforeachdb @command1 = N’USE [?];
IF ”?” NOT IN (”master”,”model”,”msdb”,”tempdb”)
BEGIN
SELECT db_name() as DB,
s.Name AS SchemaName,
t.Name AS TableName,
p.rows AS RowCounts
–,CAST(ROUND((SUM(a.used_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Used_MB,
–CAST(ROUND((SUM(a.total_pages) – SUM(a.used_pages)) / 128.00, 2) AS NUMERIC(36, 2)) AS Unused_MB,
–CAST(ROUND((SUM(a.total_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Total_MB
FROM sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
GROUP BY t.Name, s.Name, p.Rows
ORDER BY s.Name, t.Name
END’;
EXEC master.sys.sp_MSforeachdb @command1 = N’USE [?];
IF ”?” NOT IN (”master”,”model”,”msdb”,”tempdb”)
BEGIN
SELECT db_name() as DB,
s.Name AS SchemaName,
t.Name AS TableName,
p.rows AS RowCounts
–,CAST(ROUND((SUM(a.used_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Used_MB,
–CAST(ROUND((SUM(a.total_pages) – SUM(a.used_pages)) / 128.00, 2) AS NUMERIC(36, 2)) AS Unused_MB,
–CAST(ROUND((SUM(a.total_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Total_MB
FROM sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
GROUP BY t.Name, s.Name, p.Rows
ORDER BY s.Name, t.Name
END’;
SELECT * FROM #AllDBRowCounts ORDER BY DB,SchemaName,TableName;
DROP TABLE #AllDBRowCounts;
DROP TABLE #AllDBRowCounts;
DATALENGTH (Transact-SQL); 2 minutes to read; Contributors. All; In this article. APPLIES TO: SQL Server (starting with 2008) Azure SQL Database Azure SQL Data Warehouse Parallel Data Warehouse This function returns the number of bytes used to represent any expression. Mar 16, 2017 SQL Server Determine Physical Size of Table Columns. What version of SQL Server are you using - may be relevant here. – TomTom Mar 11 '10 at 8:54. This is for SQL Server 2005 – andyJ Mar 11 '10 at 9:04. Add a comment. This will summate the datalengths of all the entries of that field in the table - it will not account for overheads.
GO
Is there any built-in function/stored procedure/query which is helpful to retrieve information about the size of
MyTable
in the SQL Server database?Pathum AnjanaPathum Anjana
7 Answers
For single table you can use
For all tables in a database you can use it with
sp_msforeachtable
as follwoingaasim.abdullahaasim.abdullah
If you don't want to write a script, you can also open the much underused 'Object Explorer Details' in SSMS (Shortcut key F7).
From the Top-Level, open the Tables folder to get a list of all the tables in your database.
You may need to customise the columns to see the Space Used. This can be done by right clicking on the header row and choosing the columns you wish to display.
There's plenty more data like this available in Object Explorer Details.
Mark SinkinsonMark Sinkinson
In SSMS right click on Database, select Reports, Standard Reports, Disk Usage by Top Tables.
The report will give you number of rows and kilobytes used per table.
![Field Field](http://1.bp.blogspot.com/-la0hduQFvX0/UksJdKmVZMI/AAAAAAAAASY/SQO-IPh8BLA/s320/Get+field+name,+data+type+and+size+of+database+table+in+sql+server.png)
Sir Swears-a-lotSir Swears-a-lot
Take a look at sys.dm_db_partition_stats (http://msdn.microsoft.com/en-us/library/ms187737.aspx).
There are some sample queries at that link and also at http://blogs.msdn.com/b/cindygross/archive/2010/04/02/dmv-series-sys-dm-db-partition-stats.aspx
You can modify to suit your needs i.e. filter in/out non-clustered indexes. Multiply page count by 8 to get size in KB, then divide by 2^10 (=1024) to convert to MB if required.
sp_spaceused (http://msdn.microsoft.com/en-us/library/ms188776.aspx) will also give table size information.
Andy JonesAndy Jones
To get table size information I like to use following script
AA.SCAA.SC
Go to to database then right click and click on reports then standard reports then disk usage by table. This will give you all the ta les in that database and thier records, data, indexes etc
SQLBenSQLBen
you can use the following script that calculates the volume for each table and another result set of the total per data base
hkravitzhkravitz