Single table format

Question

Single table format

SQL Galaxy

One Orange Chip

Points: 29472
More actions
September 16, 2009 at 5:42 am

#135025

Hi,
exec sp_MSForEachDB 'Use ? SELECT name AS ''Name of File'', size/128.0 -CAST(FILEPROPERTY(name, ''SpaceUsed'' ) AS int)/128.0 AS ''Available Space In MB'' FROM .SYSFILES'
The above result display are in sperate table format, is there way to convert to one table format.
like this
Name of File Available Space In MB
------------ ---------------------
Filename1 .125000
Filename2 1225
filename3 4578
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 1

Sure... and a bit more to boot...

--_______________________________________________________________________________________________________________________

/**********************************************************************************************************************

Purpose:

Returns a single result set similar to sp_Space used for all user tables at once.

Notes:

1. May be used as a view, stored procedure, or table-valued funtion.

2. Must comment out 1 "Schema" in the SELECT list below prior to use. See the adjacent comments for more info.

Revision History:

Rev 00 - 22 Jan 2007 - Jeff Moden

- Initital creation for SQL Server 2000

Rev 01 - 11 Mar 2007 - Jeff Moden

- Add automatic page size determination for future compliance

Rev 02 - 05 Jan 2008 - Jeff Moden

- Change "Owner" to "Schema" in output. Add optional code per Note 2 to find correct schema name

**********************************************************************************************************************/

--===== Ensure that all row counts, etc is up to snuff

-- Obviously, this will not work in a view or UDF and should be removed if in a view or UDF. External code should

-- execute the command below prior to retrieving from the view or UDF.

DBCC UPDATEUSAGE(0) WITH COUNT_ROWS, NO_INFOMSGS

--===== Return the single result set similar to what sp_SpaceUsed returns for a table, but more

SELECT DBName = DB_NAME(),

--SchemaName = SCHEMA_NAME(so.UID), --Comment out if for SQL Server 2000

SchemaName = USER_NAME(so.UID), --Comment out if for SQL Server 2005

TableName = so.Name,

TableID = so.ID,

MinRowSize = MIN(si.MinLen),

MaxRowSize = MAX(si.XMaxLen),

ReservedKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Reserved ELSE 0 END) * pkb.PageKB,

DataKB = SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.DPages ELSE 0 END) * pkb.PageKB

+ SUM(CASE WHEN si.IndID IN ( 255) THEN ISNULL(si.Used,0) ELSE 0 END) * pkb.PageKB,

IndexKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Used ELSE 0 END) * pkb.PageKB

- SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.DPages ELSE 0 END) * pkb.PageKB

- SUM(CASE WHEN si.IndID IN ( 255) THEN ISNULL(si.Used,0) ELSE 0 END) * pkb.PageKB,

UnusedKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Reserved ELSE 0 END) * pkb.PageKB

- SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Used ELSE 0 END) * pkb.PageKB,

Rows = SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.Rows ELSE 0 END),

RowModCtr = MIN(si.RowModCtr),

HasTextImage = MAX(CASE WHEN si.IndID IN ( 255) THEN 1 ELSE 0 END),

HasClustered = MAX(CASE WHEN si.IndID IN ( 1 ) THEN 1 ELSE 0 END)

FROM dbo.SysObjects so,

dbo.SysIndexes si,

(--Derived table finds page size in KB according to system type

SELECT Low/1024 AS PageKB --1024 is a binary Kilo-byte

FROM Master.dbo.spt_Values

WHERE Number = 1 --Identifies the primary row for the given type

AND Type = 'E' --Identifies row for system type

) pkb

WHERE si.ID = so.ID

AND si.IndID IN (0, --Table w/o Text or Image Data

1, --Table with clustered index

255) --Table w/ Text or Image Data

AND so.XType = 'U' --User Tables

AND PERMISSIONS(so.ID) 0

GROUP BY so.Name,

so.UID,

so.ID,

pkb.PageKB

ORDER BY ReservedKB DESC

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 2

Jeff Moden Thanks for your reply ..

It is a good script. For find out unused spaces for all tables.

I could executed this script, but i am getting afried on my production database, Because

On a production database, there is a 277GB database, when I run

this script it indicates a very high quanity of unused space. The

database has been shrunk & free space sent to the OS. Why is this

value so high, what can I do to reclaim the space?

Please find the attachement xls sheet, here i stored script output data.

Thnaks in advance

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 3

ananda.murugesan (9/17/2009)
Jeff Moden Thanks for your reply ..
It is a good script. For find out unused spaces for all tables.
I could executed this script, but i am getting afried on my production database, Because
On a production database, there is a 277GB database, when I run
this script it indicates a very high quanity of unused space. The
database has been shrunk & free space sent to the OS. Why is this
value so high, what can I do to reclaim the space?
Please find the attachement xls sheet, here i stored script output data.
Thnaks in advance

According to your attachment, I only see about 5 gig that hasn't been used. The presence of the negative numbers here and there indicate that you may have to run DBCC UPDATEUSAGE on those tables.

What would bug me more than the 5 gig of unused space would be all of the tables with no rows in them at all. What are they for and why do they exist?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 4

Hi Jeff Moden Thanks for your reply ..

One of my client installed this software and configured database, this is totaly web based application, once installed this s/w still they are not given any support at database side, all data has been images and text format, so everyday 1gb data has inserted in database.

As you suggested to me, I could executed DBCC updateusage 4 tables which were having negative values in unusedKB and IndexKB, after executing DBCC updateusage( dbname,tablename), it was really reduced size on index and unused space those tables.

Could you suggest me, some of them have huge size in index and unused space but there is no negative values. If i could execute DBCC updateusage( dbname,tablename) remaining table, what could be result it? here i have attached result of after exectuing DBCC.

Thanks & regards

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 5

DBCC UPDATEUSAGE does nothing more than update the sysIndexes table with the latest information. It may take a while to run but it will cause no harm.

As for not having a maintenance in the future on something where 1GB will be added each day, well, that's just an insane expectation on the part of management. I'd say daily maintenance will be need every day including careful monitoring of growth, backups, index maintenance, etc, etc, etc.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)