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

  • How to find no of columns in database table

    Reply
  • Suresh Kumar
    May 16, 2014 12:47 pm

    great sir…. keep it up

    Reply
  • i have 50 rows , i want to display 5-7 records only?
    How to write the sql query?

    Reply
  • HI,

    Is it possible to use aggregate function with (case,when,END) condition use in pivot?

    like this (PIVOT (count(Shift_Description) FOR Detail_Date IN (‘ + @cols + ‘)) AS Pvt’)

    Below example :-

    SELECT
    DISTINCT Detail_Date INTO #Dates
    FROM
    Detail
    — WHERE Detail_Date between @StartDate AND @EndDate
    ORDER BY Detail_Date

    DECLARE @cols NVARCHAR(4000)
    SELECT @cols = COALESCE(@cols + ‘,[‘ + CONVERT(varchar, Detail_Date, 106)
    + ‘]’,'[‘ + CONVERT(varchar, Detail_Date, 106) + ‘]’)
    FROM #Dates
    ORDER BY Detail_Date

    DECLARE @qry NVARCHAR(4000)
    SET @qry =
    ‘SELECT Fname AS F,’ + @cols + ‘ FROM
    (SELECT Fname, Detail_Date,Shift_Description
    FROM Detail)p
    PIVOT (count(Shift_Description) FOR Detail_Date IN (‘ + @cols + ‘)) AS Pvt’
    print +@qry
    EXEC(@qry)

    f possible, can you please suggestion how to use it?

    Reply
  • Dipak Patilkhede
    January 1, 2016 5:19 pm

    HI I AM DIPAK

    INPUT

    Id Name
    1 A
    2 A
    3 B
    4 C
    5 D

    OUTPUT

    NAME COUNT
    A 2
    B 1
    C 1
    D 1

    I WANT TO KNOW SQL QUERY FOR THE ABOVE TABLE

    Reply
  • 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