Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Macola File Sizes SQL

Status
Not open for further replies.

chrism2278

IS-IT--Management
Oct 25, 2005
24
US

I know I have asked this question before...just misplaced the answer.

What is the easiest way to see (table) file sizes in Macola SQL. PSQL is easy because I can just sort by size and view the directory in windows explorer.

Want to periodically make sure files are being purged.

Thanks
 
Run this query against the DB you wish to calculate file size.

SET NOCOUNT ON

/*DATABASE TABLE SPY SCRIPT

Micheal Soelter

1/24/03

DESCRIPTION

Returns TABLE Size Information

SORTING USAGE

@Sort bit VALUES

0 = Alphabetically BY TABLE name

1 = Sorted BY total space used by TABLE

*/

DECLARE @cmdstr varchar(100)

DECLARE @Sort bit

SELECT @Sort = 1 /* Edit this value FOR sorting options */

/* DO NOT EDIT ANY CODE BELOW THIS LINE */

--Create Temporary Table

CREATE TABLE #TempTable

( [Table_Name] varchar(50),

Row_Count int,

Table_Size varchar(50),

Data_Space_Used varchar(50),

Index_Space_Used varchar(50),

Unused_Space varchar(50)

)

--Create Stored Procedure String

SELECT @cmdstr = 'sp_msforeachtable ''sp_spaceused "?"'''

--Populate Tempoary Table

INSERT INTO #TempTable EXEC(@cmdstr)

--Determine sorting method

IF @Sort = 0



BEGIN

--Retrieve Table Data and Sort Alphabet

-- ically

SELECT * FROM #TempTable ORDER BY cast(substring(Table_Size, 1, charindex('k', table_size) - 1) as int)


END

ELSE



BEGIN

/*Retrieve TABLE Data AND Sort BY the size OF the Table*/

SELECT * FROM #TempTable ORDER BY row_count DESC

END

--Delete Temporay Table

DROP TABLE #TempTable

 
Another way, and maybe easier for you is to use SQL Enterprise manager.

Once you have a connection to your server select the databse you wish to look at.

Right click on the database, under the popup menu go down to view and choose task pad.

On the first tab on the right you will see a visual representation of the size of the actual SQL files, any maintenance plans in place and the last time they were run etc. Remember that SQL unlike Pervasive is one databse file with a locking db, not one file for each table.

The second tab in the window on the right will show you a host of sQL information including the size of the tables, the number of records, the indexes, how much space the index is taking etc.




Andy Baldwin

"Testing is the most overlooked programming language on the books!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top