SQL SERVER – Query to find number Rows, Columns, ByteSize for each table in the current database – Find Biggest Table in Database

USE DatabaseName
GO
CREATE TABLE #temp (
table_name sysname ,
row_count INT,
reserved_size VARCHAR(50),
data_size VARCHAR(50),
index_size VARCHAR(50),
unused_size VARCHAR(50))
SET NOCOUNT ON
INSERT #temp
EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT a.table_name,
a.row_count,
COUNT(*) AS col_count,
a.data_size
FROM #temp a
INNER JOIN information_schema.columns b
ON a.table_name collate database_default
= b.table_name collate database_default
GROUP BY a.table_name, a.row_count, a.data_size
ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC
DROP TABLE #temp

Reference: Pinal Dave (http://www.SQLAuthority.com)

SQL Scripts, SQL Stored Procedure
Previous Post
SQL SERVER – Simple Example of Cursor
Next Post
SQL SERVER – Query Analyzer Shortcuts

Related Posts

277 Comments. Leave new

  • Muhammad Junaid Khan
    May 20, 2016 9:42 am

    Hi dave

    Is there any way to Find the Space of All table by filtering the Row.
    Like I have Foreign Key ‘Company ID ‘ In All Table would it be possible if i want to know the space Occupied by Company

    Thanks
    Junaid

    Reply
  • write an SQL query that returns the sum of the numbers in column v.

    For example, given:

    v

    2
    10
    20
    10
    your query should return 42.
    Code please

    Reply
  • How can I get size of a column in a table ? I mean if I want to know, size of some particular column is there any command like sp_spaceused ? I believe sp_Spaceused give size of only table and not particular Column.

    Reply
  • SELECT * FROM TB_NAME
    ORDER BY COLUMN NAME
    OFFSET 4 ROWS FETCH NEXT 3 ROWS ONLY

    Reply

Leave a Reply