--_______________________________________________________________________________________________________________________
/**********************************************************************************************************************
 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

--===== Create and populate a 1,000,000 row test table.
     -- Column "RowNum" has a range of 1 to 100,000 unique numbers
     -- Column "SomeInt" has a range of 1 to 50,000 non-unique numbers
     -- Column "SomeLetters2" has a range of "AA" to "ZZ" non-unique 2 character strings
     -- Column "SomeMoney has a range of 0.0000 to 99.9999 non-unique numbers
     -- Column "SomeDate" has a range of  >=01/01/2000 and <01/01/2010 non-unique date/times
     -- Column "SomeCSV" contains 'Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10'
     --        for all rows.
     -- Column "SomeHex12" contains 12 random hex characters (ie, 0-9,A-F)
     -- Jeff Moden

 SELECT TOP 1000000
        SomeID       = IDENTITY(INT,1,1),
        SomeInt      = ABS(CHECKSUM(NEWID()))%50000+1,
        SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID()))%26+65)
                     + CHAR(ABS(CHECKSUM(NEWID()))%26+65),
        SomeCSV      = CAST('Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10' AS VARCHAR(80)),
        SomeMoney    = CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS MONEY),
        SomeDate     = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME),
        SomeHex12    = RIGHT(NEWID(),12)
   INTO dbo.JBMTest
   FROM Master.dbo.SysColumns t1,
        Master.dbo.SysColumns t2 --Lack of join criteria makes this a CROSS-JOIN

--===== A table is not properly formed unless a Primary Key has been assigned
     -- Takes about 1 second to execute.
  ALTER TABLE dbo.JBMTest
        ADD PRIMARY KEY CLUSTERED (SomeID)

SELECT COUNT(*) FROM dbo.JBMTest