How do I extract Column info for a particular column?

Question

How do I extract Column info for a particular column?

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

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

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

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)

janine.rawnsley Ten Centuries Points: 1056 More actions · Answer 2

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 😉

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

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)

Jeff Moden SSC Guru Points: 1004304 More actions · Answer 4

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)

janine.rawnsley Ten Centuries Points: 1056 More actions · Answer 5

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