home links tools blog about

« Taming Feature-Creep with VS.NET | Main | IIS ADMIN MMC 6.0 Fails the Usability Test »

November 13, 2003



could that be modified to show table owner info as well? thanks, pm

Michael K. Campbell

Sure. The key to doing that is just to grab the userid from the name of the table. There's a killer SQL Server function: OBJECTPROPERTY that will do it for you. Once that's done, just join against that value to a suitable table (sysusers) and you're good to go. Replace the SELECT * FROM ... (down near the bottom) with this: SELECT u.[name] [owner],* FROM #sizes s INNER JOIN dbo.sysusers u ON u.uid = OBJECTPROPERTY(OBJECT_ID(s.[name]),'OwnerId') ORDER BY CAST(REPLACE(s.data,' KB','') AS int) DESC


thanks! much appreciated...

Michael K. Campbell

A cleaner version of this would be to run the foreachtable sproc call with the following command:

EXECUTE sp_MSforeachtable
@command1 = 'INSERT INTO #sizes EXEC sp_spaceused ''?'''

(which is more stable and easier to read)

SQL Server Hosting

Thanks Michael for writing and posting your code. What a timesaver for those of us using shared hosting!

The comments to this entry are closed.