How do I extract Column info for a particular column?

  • sp_MSforeachtable is nothing more than a cursor/RBAR on steroids...

    --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)

  • Jeff Moden (6/25/2008)


    sp_MSforeachtable is nothing more than a cursor/RBAR on steroids...

    Probably no different to calling sp_SapceUsed in your own cursor though?

    Anyway I was just challenging myself to see if I could get it working 😉

  • Here's an example of what I'm talking about... go look at the nightmare code behind sp_MsForEachTable and sp_SpaceUsed... both can be replaced with this relatively simple and very fast code...

    --===== "Space Used on Sterioids"

    -- If "UnusedKB" is negative, it's likely you need to run DBCC UpdateUsage on the table.

    -- If the RowModCtr is high (contains number of rows inserted/updated/deleted sinse last stats update)

    -- you might want to run UPDATE STATISICS on those tables.

    -- Jeff Moden

    SELECT DBName = DB_NAME(),

    Owner = USER_NAME(so.UID),

    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,

    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)

  • Darned forum... please replace the smiley faces in the code with right parenthesis...

    --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)

  • Nice. I should really look into the system tables at some point..

Viewing 5 posts - 16 through 19 (of 19 total)

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