January 9, 2008 at 9:09 am
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
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
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
January 10, 2008 at 1:56 am
True enough... but for planning purposes, it does count towards the max of 8060...
--Jeff Moden
Change is inevitable... Change for the better is not.
January 10, 2008 at 8:35 am
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
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
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
January 30, 2008 at 3:58 pm
Heh... don't forget about DBCC SHOWCONTIG WITH TABLERESULTS
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy