March 1, 2009 at 12:12 am
I've looked, but can't find the procedure I developed. It's not too difficult, just copy the sp_who2 code to query analyzer, and make the changes to display the data you want.
I do find it interesting that many of the system sp's in SQL 2000 seem to use cursors and loops. 194 of the obejcts in master information_schema.routines contain the word cursor, while 45 contain the word loop. Maybe you need to go talk to MS about RBAR, Jeff!
March 1, 2009 at 9:32 am
Ross McMicken (3/1/2009)
I've looked, but can't find the procedure I developed. It's not too difficult, just copy the sp_who2 code to query analyzer, and make the changes to display the data you want.I do find it interesting that many of the system sp's in SQL 2000 seem to use cursors and loops. 194 of the obejcts in master information_schema.routines contain the word cursor, while 45 contain the word loop. Maybe you need to go talk to MS about RBAR, Jeff!
Heh... I noticed the same thing, Ross. Apparently, MS has the same problems that everyone else does... nasty schedules and people that can think in a Set Based fashion. sp_Space used is one of my favorite gripes.
Thanks for the feedback. And, oh by the way, here's how I "fixed" sp_SpaceUsed... I put it on some Set Based "sterioids"... 😀
/**********************************************************************************************************************
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 do 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)
--===== 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
Change is inevitable... Change for the better is not.
March 1, 2009 at 10:04 am
there you go Jeff; Call Microsoft and tell them that the premiere RE-BAR avoidance specialist is offering to rewrite any of their 194 stored procs at the everyday low price of $1,000 each...or a 50% discount to re-write all of them as a group; a few weeks work for you and you can treat yourself to some new toys.
they may not like your code, what with all your code being heavily commented, explained and readable...not really up to typical MS standards..but what the hey.
Lowell
March 1, 2009 at 11:09 am
Thanks, Lowell... that's an awesome compliment. They'd probably just make the same excuses as everyone else, though... things like "it's good enough for what we want to do" or "it only needs to handle a couple of rows".
Not a bad idea, though... maybe I'll send them my version of sp_SpaceUsed and tell them that, for the right price, rewrites are available on a regular basis.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply