Finding Total Number of Rows, Columns in a Database and Tables Sizes

3:24 AM
Here is the Script:

USE Northwind 
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


Output:


1 comments:

{ Lucy } at: June 24, 2012 at 8:15 PM said...

I like to make friends with you,haha.


----------------------------------------------------------------------------------------------------------------------------------------
Rc Helicopter|Mini Rc Helicopter|Rc Helicopters

Post a Comment