SELECT CASE WHEN (GROUPING(sob.name)=1) THEN ‘All_Tables’
ELSE ISNULL(sob.name, ‘unknown’) END AS Table_name,
SUM(sys.length) AS Byte_Length
FROM sysobjects sob, syscolumns sys
WHERE sob.xtype=‘u’ AND sys.id=sob.id
GROUP BY sob.name
WITH CUBE
Reference : Pinal Dave (http://blog.SQLAuthority.com)
-
-
About Pinal Dave
Pinalkumar Dave is Microsoft SQL Server MVP and author of over 800 SQL Server articles. He has six years experience as Principal Database Administrator in MS SQL Server 2008/2005, .NET (C#) and ColdFusion MX. He has a Masters of Science degree in Computer Networks, along with MCDBA, MCAD(.NET) and ColdFusion Advanced MX Certifications.
MVP Profile
-
Blog Stats
- 5,738,576 Readers
-
SQLAuthority Links
My Homepage
My Resume
My Other Blog
SQL Server Mag Articles
--------------------
SQLAuthority
Best Articles
Favorite Articles
SQL Script Bank
Top Downloads
--------------------
SQL Interview Q & A
SQL Coding Standards
SQL FAQ Download
--------------------
SQL Random Article
Search SQLAuthority
Subscribe Email Update
SQLAuthority Feed
Translate SQLAuthority
--------------------
Jobs @ SQLAuthority
Find Your IP
Categories
- About Me (41)
- Best Practices (48)
- Data Warehousing (26)
- Database (242)
- DBA (202)
- Main (67)
- MVP (38)
- Pinal Dave (545)
- Software Development (73)
- SQL (826)
- SQL Add-On (77)
- SQL Authority (826)
- SQL Backup and Restore (50)
- SQL Coding Standards (84)
- SQL Constraint and Keys (46)
- SQL Cursor (63)
- SQL Data Storage (19)
- SQL DateTime (35)
- SQL Documentation (293)
- SQL Download (430)
- SQL Error Messages (361)
- SQL Function (96)
- SQL Humor (21)
- SQL Index (68)
- SQL Interview Questions and Answers (63)
- SQL Joins (356)
- SQL Performance (413)
- SQL Puzzle (10)
- SQL Query (826)
- SQL Scripts (530)
- SQL Security (368)
- SQL Server (826)
- SQL Server DBCC (254)
- SQL Stored Procedure (133)
- SQL Tips and Tricks (826)
- SQL Trigger (40)
- SQL User Group (16)
- SQL Utility (62)
- SQLAuthority (17)
- SQLAuthority Author Visit (38)
- SQLAuthority Book Review (29)
- SQLAuthority News (170)
- SQLAuthority Website Review (18)
- T SQL (826)
- Technology (826)
-
Top Posts
- SQL SERVER - Insert Data From One Table to Another Table - INSERT INTO SELECT - SELECT INTO TABLE
- SQL SERVER - Insert Multiple Records Using One Insert Statement - Use of UNION ALL
- SQL SERVER - 2005 - Create Script to Copy Database Schema and All The Objects - Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects
- SQL SERVER - Import CSV File Into SQL Server Using Bulk Insert - Load Comma Delimited File Into SQL Server
- SQL SERVER - Retrieve Current Date Time in SQL Server CURRENT_TIMESTAMP, GETDATE(), {fn NOW()}
- SQL SERVER - Convert Text to Numbers (Integer) - CAST and CONVERT
- SQL Server Interview Questions and Answers Complete List Download
- SQL SERVER - Restore Database Backup using SQL Script (T-SQL)
- SQL SERVER - Shrinking Truncate Log File - Log Full
- SQL SERVER - 2005 List All Tables of Database
- SQL SERVER - 2008 - Interview Questions and Answers Complete List Download
- SQL SERVER - 2005 NorthWind Database or AdventureWorks Database - Samples Databases
-
Recent Posts
- SQL SERVER - Interesting Observation - Using sqlcmd From SSMS Query Editor
- SQL SERVER - sqlcmd vs osql - Basic Comparison
- SQL SERVER - 2008 - Change Color of Status Bar of SSMS Query Editor
- SQL SERVER - Time Delay While Running T-SQL Query - WAITFOR Introduction
- SQL SERVER - 2008 - 2005 - Find Longest Running Query - TSQL
- SQLAuthority News - Happy New Year - 5 SQL New Year Resolutions
- SQLAuthority News - Recap Year 2008 - Two Most Important Event of My Life
- SQLAuthority Author Visit Report - Tech Meetings - Recession - Job Market - Consolidation of Servers - OS Choice - Server Reboots - Performance Optimization - Backup and Restore
- SQL SERVER - 2008 - Certification Path Complete Download PDF
- SQL SERVER - Fix : Msg 15151, Level 16, State 1, Line 3 Cannot drop the login ‘test’, because it does not exist or you do not have permission
- SQL SERVER - Add Any User to SysAdmin Role - Add Users to System Roles
- SQL SERVER - Fix : Error : Msg 15151, Level 16, State 1, Line 2 Cannot alter the login ’sa’, because it does not exist or you do not have permission
- SQLAuthority Author Visit - Valsad, Daman, Silvassa, Vapi - Tech Meetings
- SQLAuthority News - Merry Christmas - Search SQLAuthority
- SQL SERVER - DISABLE and ENABLE user SA
-
Recent Comments
- Peter Kamenov on SQLAuthority News - Recap Year 2008 - Two Most Important Event of My Life
- Naveen on SQL SERVER - Simple Cursor to Select Tables in Database with Static Prefix and Date Created
- silversurffer on SQL SERVER - Executing Remote Stored Procedure - Calling Stored Procedure on Linked Server
- pg on SQL SERVER - Difference Between EXEC and EXECUTE vs EXEC() - Use EXEC/EXECUTE for SP always
- Vijay Ahire on SQL SERVER - 2005 - Create Script to Copy Database Schema and All The Objects - Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects
- Vijay Ahire on SQL SERVER - 2005 - Create Script to Copy Database Schema and All The Objects - Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects
- ARUN on Contact Me - Archive 2
- Phani on SQL SERVER - 2005 - Database Table Partitioning Tutorial - How to Horizontal Partition Database Table
- Ghnanambal.K on SQL SERVER - Simple Example of Cursor
- MUKESH BHATT on Contact Me - Archive 1
- sizwe on SQL SERVER - 2005 - Export Data From SQL Server 2005 to Microsoft Excel Datasheet
- HaiMT on Contact Me
- Alex Austin on SQL SERVER - Fix : Error : Msg 7311, Level 16, State 2, Line 1 Cannot obtain the schema rowset DBSCHEMA_TABLES_INFO for OLE DB provider SQLNCLI for linked server LinkedServerName
- John on SQL SERVER - Interesting Observation - Using sqlcmd From SSMS Query Editor
- SQL SERVER - Interesting Observation - Using sqlcmd From SSMS Query Editor Journey to SQL Authority with Pinal Dave on SQL SERVER - sqlcmd vs osql - Basic Comparison
Archives
- January 2009
- December 2008
- November 2008
- October 2008
- September 2008
- August 2008
- July 2008
- June 2008
- May 2008
- April 2008
- March 2008
- February 2008
- January 2008
- December 2007
- November 2007
- October 2007
- September 2007
- August 2007
- July 2007
- June 2007
- May 2007
- April 2007
- March 2007
- February 2007
- January 2007
- December 2006
- November 2006
-
Pages
-
Latest Articles
-
pinaldave
- SQL SERVER - Interesting Observation - Using sqlcmd From SSMS Query Editor
- SQL SERVER - sqlcmd vs osql - Basic Comparison
- SQL SERVER - 2008 - Change Color of Status Bar of SSMS Query Editor
- SQL SERVER - Time Delay While Running T-SQL Query - WAITFOR Introduction
- SQL SERVER - 2008 - 2005 - Find Longest Running Query - TSQL
- SQLAuthority News - Happy New Year - 5 SQL New Year Resolutions
- SQLAuthority News - Recap Year 2008 - Two Most Important Event of My Life
- SQLAuthority Author Visit Report - Tech Meetings - Recession - Job Market - Consolidation of Servers - OS Choice - Server Reboots - Performance Optimization - Backup and Restore
- SQL SERVER - 2008 - Certification Path Complete Download PDF
- SQL SERVER - Fix : Msg 15151, Level 16, State 1, Line 3 Cannot drop the login ‘test’, because it does not exist or you do not have permission
-
-
-
Click Cloud
how i can find? please reply
1.What is the current file system size of the database?
2. What is the current size of the tables (used)?
@Sandhu
You can find it through two ways
a) Query Analyzer.
b) SSMS/Enterprise Manager.
a). Query Analyzer :
1. Sp_helpdb — execute this is master database.
Output : you will get information about all databases sizes.
2. sp_help ”database_name” —
Output: gives in detail information about Log and data file, with their sizes.
2. Sp_helpfile ( execute in the database for which you want size). Will give the same information about log and data file of the current database.
b) Enterprize manager: Expand Sql Server - Expand databases- click on database name = right click on database name - view - all task and then you can see in detail information about the size of the log file and also data file. And also you can see how much is filled, how much is empty.
b) in Enterprise Manager - right click database name , click properties, you will see the database size.
c) SSMS (2005) : Right click database select reports- click standard reports - Disk usage. you will see a report and you can see all details about sizes. If you expand the last point ” Disk usage by datafiles” you can see some good information.
d) SSMS (2005) - Right click database name, click properties and you can see database size.
To see the only logspace for all databases. Execute this statement,
DBCC SQLPERF( logspace)
Output: Will give you detail information about Logfile of the database.
your second question about tables:
Please visit this link : http://blog.sqlauthority.com/2008/07/08/sql-server-find-space-used-for-any-particular-table/
Hope this helps.
Thanks
Imran.