Home > SQL > BISQL#5:Analyzing the Size of Database and Size of table

BISQL#5:Analyzing the Size of Database and Size of table

 

Hi Friends,

So how we are going to find out table size information of given database.

Here we go….

  • In this section I am going to explain two queries regarding size
  • Both are related to analyze size of database
  • Its always necessary to find out size of table due to large amount of data

Query 1 : In this we have to first select DATBASE from the SQL server and fire the query and you will information of current size and free space of the selected database

Let say I have selected  master database as shown

image

Now Fire the following query on it :

--Database size information
SELECT Db_name()AS       dbname,
  name      AS     filename,
  size / 128.0 AS  currentsizemb,
  size / 128.0 - CAST(Fileproperty(name,'SpaceUsed') AS INT) / 128.0 AS freespacemb
FROM   sys.database_files
 
We will Get the output in the following format 

image

Query 2 :This query is informative and contains almost all the required information as we required about any table

–Detailed Database size information

DECLARE
@max INT,
@min INT,
@owner NVARCHAR(256),
@table_name NVARCHAR(256),
@sql NVARCHAR(4000)

DECLARE @table TABLE(
ident INT IDENTITY(1,1) PRIMARY KEY,
owner_name NVARCHAR(256),
table_name NVARCHAR(256))

IF (SELECT OBJECT_ID('tempdb..#results')) IS NOT NULL
BEGIN
DROP TABLE #results
END

CREATE TABLE #results(
ident INT IDENTITY(1,1) PRIMARY KEY, --Will be used to update the owner.
table_name NVARCHAR(256),
owner_name NVARCHAR(256),
table_rows INT,
reserved_space NVARCHAR(55),
data_space NVARCHAR(55),
index_space NVARCHAR(55),
unused_space NVARCHAR(55))

--Loop through statistics for each table.
INSERT @table(owner_name, table_name)
SELECT 
su.name, 
so.name
FROM
sysobjects so
INNER JOIN sysusers su ON so.uid = su.uid
WHERE
so.xtype = 'U'

SELECT
@min = 1,
@max = (SELECT MAX(ident) FROM @table)

WHILE @min <= @max
BEGIN

SELECT 
@owner = owner_name,
@table_name = table_name
FROM
@table
WHERE
ident = @min

SELECT @sql = 'EXEC sp_spaceused ''[' + @owner + '].[' + @table_name + ']'''

INSERT #results(table_name, table_rows, reserved_space, data_space, index_space, unused_space)
EXEC (@sql)

UPDATE #results
SET owner_name = @owner
WHERE ident = (SELECT MAX(ident) FROM #results)

SELECT @min = @min + 1
END

SELECT * FROM #results order by table_rows  
Output of the above query is as shown following which have size ,rows info :

image

Hope it Helps!!

 

For more interesting information on SQL we can also look into similar topics such as

· BISQL # 6 :Identifying Running Query and Last Modified Query

· BISQL # 7 :ALL in One Database Hero Script !! Set–I

· BISQL # 8 : Pros and Cons of Money Data Type

· BISQL # 9 :How to Get Answers,Common Question :SQL Server FAQs eBook

· BISQL # 10 :Top 10 Things For SQL Server Performance Part – I

 

Hope you will like this post on Size of Database & Tables.

If you really like reading my blog and understood at least few thing then please don’t forget to subscribe my blog.

If you want daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog: Link Resource Website

                 For More information related to BI World visit my Mentalist Blog

                            Link Resource Blog >> Daily Interesting links

                           SQL Server Mentalist >> SQL Learning Blog

                           Business Intelligence Mentalist >> BI World

                                            Connect With me on

               | FaceBook |Twitter | LinkedIn| Google+ | WordPress | RSS |

                                    Copyright © 2011 – 2012 Vishal Pawar

Advertisements
Categories: SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: