Total columns in a table

Question

Post reply

Total columns in a table

Nick-76393

Valued Member

Points: 59
More actions
January 9, 2008 at 9:09 am

#377903

Hi all
I hope this does not sound like a daft question.
I am trying to find out how many columns I have in each table within a database and also how much space each one takes up.
I.E if the column is a varchar(20) and the next is a varchar(200) then the total would be 220.
So I am not looking for the amount of data in each column just how big each column is.
I hope you understand what I am trying to find out.
Thanks in advance
Nick

Viewing 8 posts - 1 through 8 (of 8 total)

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

Try something like this:

select

so.name tablename,

sc.name columnname,

sc.colorder columnorder,

sc.length columnsize

from syscolumns sc

inner join sysobjects so on sc.id=so.id

where so.xtype='u'

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

With the varchars, the total space used on disk is 2 + number of characters in the field.

so a varchar(200) that has in it 'abc' only takes 5 bytes to store, not 200.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

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

True enough... but for planning purposes, it does count towards the max of 8060...

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

Nick-76393 Valued Member Points: 59 More actions · Answer 4

Thanks Matt I will try that tomorrow.

Thanks Jeff you hit the nail on the head as to the reason I need the infomation.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

If you want to have both aspects covered - here's the extra info you might need:

select

so.name tablename,

sc.name columnname,

sc.colorder columnorder,

sc.length columnsize,

st.name columnType,

st.variable columnVarLength,

sc.isnullable columnIsNullable

from syscolumns sc

inner join sysobjects so on sc.id=so.id

inner join systypes st on sc.xusertype=st.xusertype

where so.xtype='u'

Between the variable length flag and the nullable flag - you can then determine if the additional space is present.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Lowell SSC Guru Points: 323522 More actions · Answer 6

I was in a similar thread a while back, where there were really three different measures that we discussed:

max defined rowsize of the table

the actual max rowsize of the existing data.

the potentially largest size of the existing data:

there's some really good snippets there from a lot of contributors:

http://www.sqlservercentral.com/Forums/Topic385732-9-1.aspx

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1004595 More actions · Answer 7

Heh... don't forget about DBCC SHOWCONTIG WITH TABLERESULTS

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

Total columns in a table

Cookies on SQLServerCentral