Post reply

Total columns in a table

  • 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

  • January 9, 2008 at 10:32 am

    #766322

    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?

  • January 10, 2008 at 12:20 am

    #766493

    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

  • January 10, 2008 at 1:56 am

    #766508

    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)

  • January 10, 2008 at 8:35 am

    #766688

    Thanks Matt I will try that tomorrow.

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

  • January 10, 2008 at 9:18 am

    #766711

    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?

  • January 30, 2008 at 11:12 am

    #773429

    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!

  • January 30, 2008 at 3:58 pm

    #773564

    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)

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

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