sql server2000 size of a column 2

[tempo1]

Hi everyone,
I'd like to display sizes of a table using a query such as:

SELECT
SIZEOF(col1),
SIZEOF(col2)
FROM
mytable

can anyone tell me the keyword i should write instead of "sizeof" ?
Thanks

 

[philhege]

Do you want to display designed or actual size? What about non-character fields (int, numeric, etc.)

For character type fields, you can retrieve designed size with the following:

select table_name, column_name, character_maximum_length from information_schema.columns
where table_name = 'My_Table'
AND data_type IN('char','varchar')

HTH,

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Pity the insomniac dyslexic agnostic. He stays up all night, wondering if there really is a dog.

 

[tempo1]

hi,
Thanks a lot in the first place.
Secondly: what if my table is a temporary table (a variable) what if i want to know the size of a parameter?
Yes, i'm talking of character type columns and variables.

 

[philhege]

I don't know if the schema for a table variable is available. Temp table schema is available in tempdb, I believe, but once again, not sure about a table variable.

What parameter are you talking about? A stored procedure or function parameter? You can query INFORMATION_SCHEMA.routines for that information.

 

[ESquared]

May I ask why you need this information and don't already know it in the scope of where you will be running it?

You can't use table variables in dynamic SQL the way you can use temp tables (declare outside, use inside). So why would you want this?

You can see table variables in tempdb but they have names like "#626F38CF" and the object_id function (per my attempt so far and web searches) can't give you the correct objectid like you can with temp tables ala object_id('tempdb.dbo.#temptable').


[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[tempo1]

Hi,
Parameter i'm talking about is something i declared as a parameter as follows:

DECLARE myMaparam VARCHAR(20)

I need to know its size in my code because i want to write titles to a report and underline it. I need to know the length of each column or param to decide the length of the underline. For example: For the tile : "City" i'll add

 INSERT INTO mytable
(
myfield
)
SELECT
REPLICATE('_',LEN('city')) + REPLICATE(' ',LEN(myfield))

Things are a bit more complicated in my code but i simplified it for the sake of presenting clearly (which hopefully i succeeded..) to the members of the forum.
Thanks
thanks

 

[ESquared]

When you said "sizes of a table" we thought you meant "sizes of a table" not "sizes of varchar variables."

Look up the SQL_VARIANT_PROPERTY function in books online and you'll find everything you need to know to accomplish this task.

It'll be something like SQL_VARIANT_PROPERTY(@Variable, 'MaxLength').

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[tempo1]

Thanks a lot.
I later added a question about sizes of variables. At first i thought the same function would do the same thing either on table's columns or variables (sizeof()). Now i see it is not the same thing and a function like "sizeof()" does not exist.

 

[ESquared]

I remember now that with varchar, SQL_VARIANT_PROPERTY(Expression, 'MaxLength') gives the current length rather than the max length. You can always use the information_schema views.

Although I'd want to test if sql_variant_property works when used against a column...

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[tempo1]

Thanks a lot ESquared,
I'm to much into "schema" so i'll need some time to learn what you said.
Thanks again.