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
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 freespacembFROM sys.database_files
We will Get the output in the following format
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 :
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
Copyright © 2011 – 2012 Vishal Pawar
-
June 30, 2011 at 10:15 pmList of monthly post of MS BI and SQL blog « (B)usiness (I)ntelligence Mentalist
-
June 30, 2011 at 10:21 pmList of monthly post of MS BI and SQL blog « SQL Server Mentalist
SQL Server Mentalist 