Home All Groups Group Topic Archive Search About

spt_datatype_info in sql 2005 ? need length of text field

microsoft.public.sqlserver.programming
Author
16 Jun 2006 8:53 PM
Mike
here is my query in sql server 2000 that returns length of text field as
2147483646:
select c.name,d.length,d.ss_dtype
from syscolumns c
join sysobjects o on c.id=o.id,
master.dbo.spt_datatype_info d,
systypes t
where
o.name='customer' AND
t.xtype = d.ss_dtype AND
c.xusertype = t.xusertype

In sql 2000, the length of a text field used to display 2147483646 but now
in sql 2005 I get 16. I would still like to get 2147483646


the problem is that sql server 2005 does not have spt_datatype_info and also
it appears that systypes no longer has user defined datatypes. I have  user
defined data types in my database.

How can I get this info in sql 2005 based on the above sql 2000 query.

Thanks for your help.

Author
16 Jun 2006 9:50 PM
Aaron Bertrand [SQL Server MVP]
Do you want datalength or number of characters that will fit?

Here is a quick query for system-defined data types (it gets more complex if
you have custom types, either old style or especially new style).




CREATE TABLE dbo.foo
(
    a CHAR(32),
    b VARCHAR(32),
    c NCHAR(32),
    d NVARCHAR(32),
    e VARCHAR(MAX),
    f NVARCHAR(MAX),
    g TEXT,
    h NTEXT
);
GO

SELECT
    c.name,
    [datalength] = CASE
WHEN t.name LIKE '%VARCHAR' THEN
    CASE c.max_length WHEN -1 THEN 2147483646 ELSE c.max_length END
WHEN t.name LIKE '%CHAR' THEN c.max_length
WHEN t.name LIKE '%TEXT' THEN 2147483646
END,
[numchars] = CASE
WHEN t.name LIKE '%VARCHAR' THEN
CASE c.max_length WHEN -1 THEN
CASE c.system_type_id WHEN 167 THEN 2147483646 ELSE 2147483646/2 END
ELSE
CASE c.system_type_id WHEN 167 THEN c.max_length ELSE c.max_length/2 END
END
WHEN t.name = 'CHAR' THEN c.max_length
WHEN t.name = 'NCHAR' THEN c.max_length/2
WHEN t.name = 'TEXT' THEN 2147483646
WHEN t.name = 'NTEXT' THEN 2147483646/2
END
FROM sys.columns c
INNER JOIN sys.types t
ON c.system_type_id = t.system_type_id
WHERE c.object_id = OBJECT_ID('dbo.foo')
AND RIGHT(t.name, 4) IN ('text', 'char')
ORDER BY c.column_id;
GO

DROP TABLE dbo.foo;
GO



Show quote
"Mike" <M***@discussions.microsoft.com> wrote in message
news:5808FDC2-D8B1-485F-8743-A93FD59FEB5D@microsoft.com...
> here is my query in sql server 2000 that returns length of text field as
> 2147483646:
> select c.name,d.length,d.ss_dtype
> from syscolumns c
> join sysobjects o on c.id=o.id,
> master.dbo.spt_datatype_info d,
> systypes t
> where
> o.name='customer' AND
> t.xtype = d.ss_dtype AND
> c.xusertype = t.xusertype
>
> In sql 2000, the length of a text field used to display 2147483646 but now
> in sql 2005 I get 16. I would still like to get 2147483646
>
>
> the problem is that sql server 2005 does not have spt_datatype_info and
> also
> it appears that systypes no longer has user defined datatypes. I have
> user
> defined data types in my database.
>
> How can I get this info in sql 2005 based on the above sql 2000 query.
>
> Thanks for your help.
Author
16 Jun 2006 10:41 PM
Kalen Delaney
What do you mean by new style datatypes? Do you mean CLR Types? You're
right, that would be especially tricky.

If you have any old style udd's at all, this will have a problem because
t.system_type_id will not be unique, and you will  get duplicate rows for
some columns.
The simple fix for that would be to change the join
    ON c.system_type_id = t.system_type_id
to
    ON c.system_type_id = t.user_type_id

t.user_type_id is unique

However, this will still give us problems displaying the user defined type
names and their appropriate lengths.
I think we can fix that by referencing the system_type_id instead of name:

EXEC sp_addtype MYTEXT , TEXT;
EXEC sp_addtype MYCHAR, 'VARCHAR(50)';
GO
CREATE TABLE dbo.foo
(
a CHAR(32),
b VARCHAR(32),
c NCHAR(32),
d NVARCHAR(32),
e VARCHAR(MAX),
f NVARCHAR(MAX),
g TEXT,
h MYTEXT,
i NTEXT,
j MYCHAR
);
GO
SELECT
c.name as column_name, t.name as type_name,
[datalength] = CASE
                        WHEN c.system_type_id in (167, 231) THEN -- varchar,
nvarchar
                                                   CASE c.max_length
                                                              WHEN -1 THEN
2147483646 ELSE c.max_length
                                                    END
                        WHEN c.system_type_id in (175, 239) THEN
c.max_length -- char, nchar
                         WHEN c.system_type_id in (35,99) THEN 2147483646 -- 
text, ntext
END,
[numchars] = CASE
              WHEN c.system_type_id in (167,231) THEN
                                        CASE c.max_length
                                                   WHEN -1 THEN
                                                          CASE
c.system_type_id
                                                                           WHEN
167 THEN 2147483646 ELSE 2147483646/2
                                                          END
                                                    ELSE
                                                            CASE
c.system_type_id
                                                                           WHEN
167 THEN c.max_length ELSE c.max_length/2
                                                           END
                                          END
                WHEN c.system_type_id = 175 THEN c.max_length
                WHEN c.system_type_id = 239 THEN c.max_length/2
                WHEN c.system_type_id = 35 THEN 2147483646
                 WHEN c.system_type_id = 99 THEN 2147483646/2
END
FROM sys.columns c
INNER JOIN sys.types t
ON c.user_type_id = t.user_type_id
WHERE c.object_id = OBJECT_ID('dbo.foo')
AND RIGHT(t.name, 4) IN ('text', 'char')
ORDER BY c.column_id;
GO
DROP TABLE dbo.foo;
GO
EXEC sp_addtype MYTEXT , TEXT
EXEC sp_addtype MYCHAR, 'VARCHAR(50)'
GO
CREATE TABLE dbo.foo
(
a CHAR(32),
b VARCHAR(32),
c NCHAR(32),
d NVARCHAR(32),
e VARCHAR(MAX),
f NVARCHAR(MAX),
g TEXT,
h MYTEXT,
i NTEXT,
j MYCHAR
);
GO
SELECT
c.name as column_name, t.name as type_name,
[datalength] = CASE
WHEN c.system_type_id in (167, 231) THEN -- varchar, nvarchar
CASE c.max_length WHEN -1 THEN 2147483646 ELSE c.max_length END
WHEN c.system_type_id in (175, 239) THEN c.max_length -- char, nchar
WHEN c.system_type_id in (35,99) THEN 2147483646 -- text, ntext
END,
[numchars] = CASE
WHEN c.system_type_id in (167,231) THEN
CASE c.max_length WHEN -1 THEN
CASE c.system_type_id
WHEN 167 THEN 2147483646 ELSE 2147483646/2
END
ELSE
CASE c.system_type_id
WHEN 167 THEN c.max_length ELSE c.max_length/2
END
END
WHEN c.system_type_id = 175 THEN c.max_length
WHEN c.system_type_id = 239 THEN c.max_length/2
WHEN c.system_type_id = 35 THEN 2147483646
WHEN c.system_type_id = 99 THEN 2147483646/2
END
FROM sys.columns c
INNER JOIN sys.types t
ON c.user_type_id = t.user_type_id
WHERE c.object_id = OBJECT_ID('dbo.foo')
AND RIGHT(t.name, 4) IN ('text', 'char')
ORDER BY c.column_id;
GO
DROP TABLE dbo.foo;
GO
EXEC sp_droptype MYTEXT ;
EXEC sp_droptype MYCHAR;
GO
--
HTH
Kalen Delaney, SQL Server MVP


Show quote
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:%23pt8t7YkGHA.1276@TK2MSFTNGP03.phx.gbl...
> Do you want datalength or number of characters that will fit?
>
> Here is a quick query for system-defined data types (it gets more complex
> if you have custom types, either old style or especially new style).
>
>
>
>
> CREATE TABLE dbo.foo
> (
>    a CHAR(32),
>    b VARCHAR(32),
>    c NCHAR(32),
>    d NVARCHAR(32),
>    e VARCHAR(MAX),
>    f NVARCHAR(MAX),
>    g TEXT,
>    h NTEXT
> );
> GO
>
> SELECT
>    c.name,
>    [datalength] = CASE
> WHEN t.name LIKE '%VARCHAR' THEN
>    CASE c.max_length WHEN -1 THEN 2147483646 ELSE c.max_length END
> WHEN t.name LIKE '%CHAR' THEN c.max_length
> WHEN t.name LIKE '%TEXT' THEN 2147483646
> END,
> [numchars] = CASE
> WHEN t.name LIKE '%VARCHAR' THEN
> CASE c.max_length WHEN -1 THEN
> CASE c.system_type_id WHEN 167 THEN 2147483646 ELSE 2147483646/2 END
> ELSE
> CASE c.system_type_id WHEN 167 THEN c.max_length ELSE c.max_length/2 END
> END
> WHEN t.name = 'CHAR' THEN c.max_length
> WHEN t.name = 'NCHAR' THEN c.max_length/2
> WHEN t.name = 'TEXT' THEN 2147483646
> WHEN t.name = 'NTEXT' THEN 2147483646/2
> END
> FROM sys.columns c
> INNER JOIN sys.types t
> ON c.system_type_id = t.system_type_id
> WHERE c.object_id = OBJECT_ID('dbo.foo')
> AND RIGHT(t.name, 4) IN ('text', 'char')
> ORDER BY c.column_id;
> GO
>
> DROP TABLE dbo.foo;
> GO
>
>
>
> "Mike" <M***@discussions.microsoft.com> wrote in message
> news:5808FDC2-D8B1-485F-8743-A93FD59FEB5D@microsoft.com...
>> here is my query in sql server 2000 that returns length of text field as
>> 2147483646:
>> select c.name,d.length,d.ss_dtype
>> from syscolumns c
>> join sysobjects o on c.id=o.id,
>> master.dbo.spt_datatype_info d,
>> systypes t
>> where
>> o.name='customer' AND
>> t.xtype = d.ss_dtype AND
>> c.xusertype = t.xusertype
>>
>> In sql 2000, the length of a text field used to display 2147483646 but
>> now
>> in sql 2005 I get 16. I would still like to get 2147483646
>>
>>
>> the problem is that sql server 2005 does not have spt_datatype_info and
>> also
>> it appears that systypes no longer has user defined datatypes. I have
>> user
>> defined data types in my database.
>>
>> How can I get this info in sql 2005 based on the above sql 2000 query.
>>
>> Thanks for your help.
>
>
Author
18 Jun 2006 4:23 PM
Mike
Thanks for your help.
I have 2 more questions regarding this for learning purpose
1. why do we divide 2147483646 by 2 when it is TEXT and not NTEXT ?
> WHEN c.system_type_id = 35 THEN 2147483646
> WHEN c.system_type_id = 99 THEN 2147483646/2
2. do you think there is any reasoning behind sql team eliminating
2147483646 for TEXT to 16? I am not able to understand why the sql team would
do this.
I think I am missing something here.I would really appreciate your help to
make me understand this concept.

TIA


Show quote
"Kalen Delaney" wrote:

> What do you mean by new style datatypes? Do you mean CLR Types? You're
> right, that would be especially tricky.
>
> If you have any old style udd's at all, this will have a problem because
> t.system_type_id will not be unique, and you will  get duplicate rows for
> some columns.
> The simple fix for that would be to change the join
>     ON c.system_type_id = t.system_type_id
> to
>     ON c.system_type_id = t.user_type_id
>
> t.user_type_id is unique
>
> However, this will still give us problems displaying the user defined type
> names and their appropriate lengths.
> I think we can fix that by referencing the system_type_id instead of name:
>
> EXEC sp_addtype MYTEXT , TEXT;
> EXEC sp_addtype MYCHAR, 'VARCHAR(50)';
> GO
> CREATE TABLE dbo.foo
> (
> a CHAR(32),
> b VARCHAR(32),
> c NCHAR(32),
> d NVARCHAR(32),
> e VARCHAR(MAX),
> f NVARCHAR(MAX),
> g TEXT,
> h MYTEXT,
> i NTEXT,
> j MYCHAR
> );
> GO
> SELECT
> c.name as column_name, t.name as type_name,
> [datalength] = CASE
>                         WHEN c.system_type_id in (167, 231) THEN -- varchar,
> nvarchar
>                                                    CASE c.max_length
>                                                               WHEN -1 THEN
> 2147483646 ELSE c.max_length
>                                                     END
>                         WHEN c.system_type_id in (175, 239) THEN
> c.max_length -- char, nchar
>                          WHEN c.system_type_id in (35,99) THEN 2147483646 -- 
> text, ntext
>  END,
> [numchars] = CASE
>               WHEN c.system_type_id in (167,231) THEN
>                                         CASE c.max_length
>                                                    WHEN -1 THEN
>                                                           CASE
> c.system_type_id
>                                                                            WHEN
> 167 THEN 2147483646 ELSE 2147483646/2
>                                                           END
>                                                     ELSE
>                                                             CASE
> c.system_type_id
>                                                                            WHEN
> 167 THEN c.max_length ELSE c.max_length/2
>                                                            END
>                                           END
>                 WHEN c.system_type_id = 175 THEN c.max_length
>                 WHEN c.system_type_id = 239 THEN c.max_length/2
>                 WHEN c.system_type_id = 35 THEN 2147483646
>                  WHEN c.system_type_id = 99 THEN 2147483646/2
> END
> FROM sys.columns c
> INNER JOIN sys.types t
> ON c.user_type_id = t.user_type_id
> WHERE c.object_id = OBJECT_ID('dbo.foo')
> AND RIGHT(t.name, 4) IN ('text', 'char')
> ORDER BY c.column_id;
> GO
> DROP TABLE dbo.foo;
> GO
> EXEC sp_addtype MYTEXT , TEXT
> EXEC sp_addtype MYCHAR, 'VARCHAR(50)'
> GO
> CREATE TABLE dbo.foo
> (
> a CHAR(32),
> b VARCHAR(32),
> c NCHAR(32),
> d NVARCHAR(32),
> e VARCHAR(MAX),
> f NVARCHAR(MAX),
> g TEXT,
> h MYTEXT,
> i NTEXT,
> j MYCHAR
> );
> GO
> SELECT
> c.name as column_name, t.name as type_name,
> [datalength] = CASE
> WHEN c.system_type_id in (167, 231) THEN -- varchar, nvarchar
> CASE c.max_length WHEN -1 THEN 2147483646 ELSE c.max_length END
> WHEN c.system_type_id in (175, 239) THEN c.max_length -- char, nchar
> WHEN c.system_type_id in (35,99) THEN 2147483646 -- text, ntext
> END,
> [numchars] = CASE
> WHEN c.system_type_id in (167,231) THEN
> CASE c.max_length WHEN -1 THEN
> CASE c.system_type_id
> WHEN 167 THEN 2147483646 ELSE 2147483646/2
> END
> ELSE
> CASE c.system_type_id
> WHEN 167 THEN c.max_length ELSE c.max_length/2
> END
> END
> WHEN c.system_type_id = 175 THEN c.max_length
> WHEN c.system_type_id = 239 THEN c.max_length/2
> WHEN c.system_type_id = 35 THEN 2147483646
> WHEN c.system_type_id = 99 THEN 2147483646/2
> END
> FROM sys.columns c
> INNER JOIN sys.types t
> ON c.user_type_id = t.user_type_id
> WHERE c.object_id = OBJECT_ID('dbo.foo')
> AND RIGHT(t.name, 4) IN ('text', 'char')
> ORDER BY c.column_id;
> GO
> DROP TABLE dbo.foo;
> GO
> EXEC sp_droptype MYTEXT ;
> EXEC sp_droptype MYCHAR;
> GO
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
> news:%23pt8t7YkGHA.1276@TK2MSFTNGP03.phx.gbl...
> > Do you want datalength or number of characters that will fit?
> >
> > Here is a quick query for system-defined data types (it gets more complex
> > if you have custom types, either old style or especially new style).
> >
> >
> >
> >
> > CREATE TABLE dbo.foo
> > (
> >    a CHAR(32),
> >    b VARCHAR(32),
> >    c NCHAR(32),
> >    d NVARCHAR(32),
> >    e VARCHAR(MAX),
> >    f NVARCHAR(MAX),
> >    g TEXT,
> >    h NTEXT
> > );
> > GO
> >
> > SELECT
> >    c.name,
> >    [datalength] = CASE
> > WHEN t.name LIKE '%VARCHAR' THEN
> >    CASE c.max_length WHEN -1 THEN 2147483646 ELSE c.max_length END
> > WHEN t.name LIKE '%CHAR' THEN c.max_length
> > WHEN t.name LIKE '%TEXT' THEN 2147483646
> > END,
> > [numchars] = CASE
> > WHEN t.name LIKE '%VARCHAR' THEN
> > CASE c.max_length WHEN -1 THEN
> > CASE c.system_type_id WHEN 167 THEN 2147483646 ELSE 2147483646/2 END
> > ELSE
> > CASE c.system_type_id WHEN 167 THEN c.max_length ELSE c.max_length/2 END
> > END
> > WHEN t.name = 'CHAR' THEN c.max_length
> > WHEN t.name = 'NCHAR' THEN c.max_length/2
> > WHEN t.name = 'TEXT' THEN 2147483646
> > WHEN t.name = 'NTEXT' THEN 2147483646/2
> > END
> > FROM sys.columns c
> > INNER JOIN sys.types t
> > ON c.system_type_id = t.system_type_id
> > WHERE c.object_id = OBJECT_ID('dbo.foo')
> > AND RIGHT(t.name, 4) IN ('text', 'char')
> > ORDER BY c.column_id;
> > GO
> >
> > DROP TABLE dbo.foo;
> > GO
> >
> >
> >
> > "Mike" <M***@discussions.microsoft.com> wrote in message
> > news:5808FDC2-D8B1-485F-8743-A93FD59FEB5D@microsoft.com...
> >> here is my query in sql server 2000 that returns length of text field as
> >> 2147483646:
> >> select c.name,d.length,d.ss_dtype
> >> from syscolumns c
> >> join sysobjects o on c.id=o.id,
> >> master.dbo.spt_datatype_info d,
> >> systypes t
> >> where
> >> o.name='customer' AND
> >> t.xtype = d.ss_dtype AND
> >> c.xusertype = t.xusertype
> >>
> >> In sql 2000, the length of a text field used to display 2147483646 but
> >> now
> >> in sql 2005 I get 16. I would still like to get 2147483646
> >>
> >>
> >> the problem is that sql server 2005 does not have spt_datatype_info and
> >> also
> >> it appears that systypes no longer has user defined datatypes. I have
> >> user
> >> defined data types in my database.
> >>
> >> How can I get this info in sql 2005 based on the above sql 2000 query.
> >>
> >> Thanks for your help.
> >
> >
>
>
>
Author
18 Jun 2006 6:36 PM
Kalen Delaney
Mike

I'm sorry I did not respond to this fast enough and you felt it necessary to
start a new thread. It was posted over 2 hours ago, and you haven't gotten a
reply yet. Of course the fact that it IS Sunday, and Fathers' Day in the US,
is irrelevant.

1. We divide NTEXT by 2, because each NTEXT character takes 2 bytes. One
output column is showing the length in bytes, the other is showing the
number of characters.

2. I think they wanted to have the length represent the length of the data
stored in the actual data row. When you have a LOB column (text, ntext or
image), by default SQL Server will only store a pointer, which is 16 bytes.
The LOB data itself is stored on other pages, so when trying to figure out
how big your rows are, and how many would fit on a page, it could be
misleading to have the full length of the LOB data.

--
HTH
Kalen Delaney, SQL Server MVP


Show quote
"Mike" <M***@discussions.microsoft.com> wrote in message
news:B8879772-F0EA-4D59-A28F-D9DC99C3254A@microsoft.com...
> Thanks for your help.
> I have 2 more questions regarding this for learning purpose
> 1. why do we divide 2147483646 by 2 when it is TEXT and not NTEXT ?
>> WHEN c.system_type_id = 35 THEN 2147483646
>> WHEN c.system_type_id = 99 THEN 2147483646/2
> 2. do you think there is any reasoning behind sql team eliminating
> 2147483646 for TEXT to 16? I am not able to understand why the sql team
> would
> do this.
> I think I am missing something here.I would really appreciate your help to
> make me understand this concept.
>
> TIA
>
>
> "Kalen Delaney" wrote:
>
>> What do you mean by new style datatypes? Do you mean CLR Types? You're
>> right, that would be especially tricky.
>>
>> If you have any old style udd's at all, this will have a problem because
>> t.system_type_id will not be unique, and you will  get duplicate rows for
>> some columns.
>> The simple fix for that would be to change the join
>>     ON c.system_type_id = t.system_type_id
>> to
>>     ON c.system_type_id = t.user_type_id
>>
>> t.user_type_id is unique
>>
>> However, this will still give us problems displaying the user defined
>> type
>> names and their appropriate lengths.
>> I think we can fix that by referencing the system_type_id instead of
>> name:
>>
>> EXEC sp_addtype MYTEXT , TEXT;
>> EXEC sp_addtype MYCHAR, 'VARCHAR(50)';
>> GO
>> CREATE TABLE dbo.foo
>> (
>> a CHAR(32),
>> b VARCHAR(32),
>> c NCHAR(32),
>> d NVARCHAR(32),
>> e VARCHAR(MAX),
>> f NVARCHAR(MAX),
>> g TEXT,
>> h MYTEXT,
>> i NTEXT,
>> j MYCHAR
>> );
>> GO
>> SELECT
>> c.name as column_name, t.name as type_name,
>> [datalength] = CASE
>>                         WHEN c.system_type_id in (167, 231) THEN -- 
>> varchar,
>> nvarchar
>>                                                    CASE c.max_length
>>                                                               WHEN -1
>> THEN
>> 2147483646 ELSE c.max_length
>>                                                     END
>>                         WHEN c.system_type_id in (175, 239) THEN
>> c.max_length -- char, nchar
>>                          WHEN c.system_type_id in (35,99) THEN
>> 2147483646 --
>> text, ntext
>>  END,
>> [numchars] = CASE
>>               WHEN c.system_type_id in (167,231) THEN
>>                                         CASE c.max_length
>>                                                    WHEN -1 THEN
>>                                                           CASE
>> c.system_type_id
>>
>> WHEN
>> 167 THEN 2147483646 ELSE 2147483646/2
>>                                                           END
>>                                                     ELSE
>>                                                             CASE
>> c.system_type_id
>>
>> WHEN
>> 167 THEN c.max_length ELSE c.max_length/2
>>                                                            END
>>                                           END
>>                 WHEN c.system_type_id = 175 THEN c.max_length
>>                 WHEN c.system_type_id = 239 THEN c.max_length/2
>>                 WHEN c.system_type_id = 35 THEN 2147483646
>>                  WHEN c.system_type_id = 99 THEN 2147483646/2
>> END
>> FROM sys.columns c
>> INNER JOIN sys.types t
>> ON c.user_type_id = t.user_type_id
>> WHERE c.object_id = OBJECT_ID('dbo.foo')
>> AND RIGHT(t.name, 4) IN ('text', 'char')
>> ORDER BY c.column_id;
>> GO
>> DROP TABLE dbo.foo;
>> GO
>> EXEC sp_addtype MYTEXT , TEXT
>> EXEC sp_addtype MYCHAR, 'VARCHAR(50)'
>> GO
>> CREATE TABLE dbo.foo
>> (
>> a CHAR(32),
>> b VARCHAR(32),
>> c NCHAR(32),
>> d NVARCHAR(32),
>> e VARCHAR(MAX),
>> f NVARCHAR(MAX),
>> g TEXT,
>> h MYTEXT,
>> i NTEXT,
>> j MYCHAR
>> );
>> GO
>> SELECT
>> c.name as column_name, t.name as type_name,
>> [datalength] = CASE
>> WHEN c.system_type_id in (167, 231) THEN -- varchar, nvarchar
>> CASE c.max_length WHEN -1 THEN 2147483646 ELSE c.max_length END
>> WHEN c.system_type_id in (175, 239) THEN c.max_length -- char, nchar
>> WHEN c.system_type_id in (35,99) THEN 2147483646 -- text, ntext
>> END,
>> [numchars] = CASE
>> WHEN c.system_type_id in (167,231) THEN
>> CASE c.max_length WHEN -1 THEN
>> CASE c.system_type_id
>> WHEN 167 THEN 2147483646 ELSE 2147483646/2
>> END
>> ELSE
>> CASE c.system_type_id
>> WHEN 167 THEN c.max_length ELSE c.max_length/2
>> END
>> END
>> WHEN c.system_type_id = 175 THEN c.max_length
>> WHEN c.system_type_id = 239 THEN c.max_length/2
>> WHEN c.system_type_id = 35 THEN 2147483646
>> WHEN c.system_type_id = 99 THEN 2147483646/2
>> END
>> FROM sys.columns c
>> INNER JOIN sys.types t
>> ON c.user_type_id = t.user_type_id
>> WHERE c.object_id = OBJECT_ID('dbo.foo')
>> AND RIGHT(t.name, 4) IN ('text', 'char')
>> ORDER BY c.column_id;
>> GO
>> DROP TABLE dbo.foo;
>> GO
>> EXEC sp_droptype MYTEXT ;
>> EXEC sp_droptype MYCHAR;
>> GO
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>>
>>
>> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in
>> message
>> news:%23pt8t7YkGHA.1276@TK2MSFTNGP03.phx.gbl...
>> > Do you want datalength or number of characters that will fit?
>> >
>> > Here is a quick query for system-defined data types (it gets more
>> > complex
>> > if you have custom types, either old style or especially new style).
>> >
>> >
>> >
>> >
>> > CREATE TABLE dbo.foo
>> > (
>> >    a CHAR(32),
>> >    b VARCHAR(32),
>> >    c NCHAR(32),
>> >    d NVARCHAR(32),
>> >    e VARCHAR(MAX),
>> >    f NVARCHAR(MAX),
>> >    g TEXT,
>> >    h NTEXT
>> > );
>> > GO
>> >
>> > SELECT
>> >    c.name,
>> >    [datalength] = CASE
>> > WHEN t.name LIKE '%VARCHAR' THEN
>> >    CASE c.max_length WHEN -1 THEN 2147483646 ELSE c.max_length END
>> > WHEN t.name LIKE '%CHAR' THEN c.max_length
>> > WHEN t.name LIKE '%TEXT' THEN 2147483646
>> > END,
>> > [numchars] = CASE
>> > WHEN t.name LIKE '%VARCHAR' THEN
>> > CASE c.max_length WHEN -1 THEN
>> > CASE c.system_type_id WHEN 167 THEN 2147483646 ELSE 2147483646/2 END
>> > ELSE
>> > CASE c.system_type_id WHEN 167 THEN c.max_length ELSE c.max_length/2
>> > END
>> > END
>> > WHEN t.name = 'CHAR' THEN c.max_length
>> > WHEN t.name = 'NCHAR' THEN c.max_length/2
>> > WHEN t.name = 'TEXT' THEN 2147483646
>> > WHEN t.name = 'NTEXT' THEN 2147483646/2
>> > END
>> > FROM sys.columns c
>> > INNER JOIN sys.types t
>> > ON c.system_type_id = t.system_type_id
>> > WHERE c.object_id = OBJECT_ID('dbo.foo')
>> > AND RIGHT(t.name, 4) IN ('text', 'char')
>> > ORDER BY c.column_id;
>> > GO
>> >
>> > DROP TABLE dbo.foo;
>> > GO
>> >
>> >
>> >
>> > "Mike" <M***@discussions.microsoft.com> wrote in message
>> > news:5808FDC2-D8B1-485F-8743-A93FD59FEB5D@microsoft.com...
>> >> here is my query in sql server 2000 that returns length of text field
>> >> as
>> >> 2147483646:
>> >> select c.name,d.length,d.ss_dtype
>> >> from syscolumns c
>> >> join sysobjects o on c.id=o.id,
>> >> master.dbo.spt_datatype_info d,
>> >> systypes t
>> >> where
>> >> o.name='customer' AND
>> >> t.xtype = d.ss_dtype AND
>> >> c.xusertype = t.xusertype
>> >>
>> >> In sql 2000, the length of a text field used to display 2147483646 but
>> >> now
>> >> in sql 2005 I get 16. I would still like to get 2147483646
>> >>
>> >>
>> >> the problem is that sql server 2005 does not have spt_datatype_info
>> >> and
>> >> also
>> >> it appears that systypes no longer has user defined datatypes. I have
>> >> user
>> >> defined data types in my database.
>> >>
>> >> How can I get this info in sql 2005 based on the above sql 2000 query.
>> >>
>> >> Thanks for your help.
>> >
>> >
>>
>>
>>
Author
19 Jun 2006 12:21 PM
Mike
Kalen,
Sorry about the other thread.
Thanks a lot for the information.  Do you think I could use
character_maximum_length from INFORMATION_SCHEMA.COLUMNS to solve this
problem instead of hard coding the max length?

TIA



Show quote
"Kalen Delaney" wrote:

> Mike
>
> I'm sorry I did not respond to this fast enough and you felt it necessary to
> start a new thread. It was posted over 2 hours ago, and you haven't gotten a
> reply yet. Of course the fact that it IS Sunday, and Fathers' Day in the US,
> is irrelevant.
>
> 1. We divide NTEXT by 2, because each NTEXT character takes 2 bytes. One
> output column is showing the length in bytes, the other is showing the
> number of characters.
>
> 2. I think they wanted to have the length represent the length of the data
> stored in the actual data row. When you have a LOB column (text, ntext or
> image), by default SQL Server will only store a pointer, which is 16 bytes.
> The LOB data itself is stored on other pages, so when trying to figure out
> how big your rows are, and how many would fit on a page, it could be
> misleading to have the full length of the LOB data.
>
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
>
> "Mike" <M***@discussions.microsoft.com> wrote in message
> news:B8879772-F0EA-4D59-A28F-D9DC99C3254A@microsoft.com...
> > Thanks for your help.
> > I have 2 more questions regarding this for learning purpose
> > 1. why do we divide 2147483646 by 2 when it is TEXT and not NTEXT ?
> >> WHEN c.system_type_id = 35 THEN 2147483646
> >> WHEN c.system_type_id = 99 THEN 2147483646/2
> > 2. do you think there is any reasoning behind sql team eliminating
> > 2147483646 for TEXT to 16? I am not able to understand why the sql team
> > would
> > do this.
> > I think I am missing something here.I would really appreciate your help to
> > make me understand this concept.
> >
> > TIA
> >
> >
> > "Kalen Delaney" wrote:
> >
> >> What do you mean by new style datatypes? Do you mean CLR Types? You're
> >> right, that would be especially tricky.
> >>
> >> If you have any old style udd's at all, this will have a problem because
> >> t.system_type_id will not be unique, and you will  get duplicate rows for
> >> some columns.
> >> The simple fix for that would be to change the join
> >>     ON c.system_type_id = t.system_type_id
> >> to
> >>     ON c.system_type_id = t.user_type_id
> >>
> >> t.user_type_id is unique
> >>
> >> However, this will still give us problems displaying the user defined
> >> type
> >> names and their appropriate lengths.
> >> I think we can fix that by referencing the system_type_id instead of
> >> name:
> >>
> >> EXEC sp_addtype MYTEXT , TEXT;
> >> EXEC sp_addtype MYCHAR, 'VARCHAR(50)';
> >> GO
> >> CREATE TABLE dbo.foo
> >> (
> >> a CHAR(32),
> >> b VARCHAR(32),
> >> c NCHAR(32),
> >> d NVARCHAR(32),
> >> e VARCHAR(MAX),
> >> f NVARCHAR(MAX),
> >> g TEXT,
> >> h MYTEXT,
> >> i NTEXT,
> >> j MYCHAR
> >> );
> >> GO
> >> SELECT
> >> c.name as column_name, t.name as type_name,
> >> [datalength] = CASE
> >>                         WHEN c.system_type_id in (167, 231) THEN -- 
> >> varchar,
> >> nvarchar
> >>                                                    CASE c.max_length
> >>                                                               WHEN -1
> >> THEN
> >> 2147483646 ELSE c.max_length
> >>                                                     END
> >>                         WHEN c.system_type_id in (175, 239) THEN
> >> c.max_length -- char, nchar
> >>                          WHEN c.system_type_id in (35,99) THEN
> >> 2147483646 --
> >> text, ntext
> >>  END,
> >> [numchars] = CASE
> >>               WHEN c.system_type_id in (167,231) THEN
> >>                                         CASE c.max_length
> >>                                                    WHEN -1 THEN
> >>                                                           CASE
> >> c.system_type_id
> >>
> >> WHEN
> >> 167 THEN 2147483646 ELSE 2147483646/2
> >>                                                           END
> >>                                                     ELSE
> >>                                                             CASE
> >> c.system_type_id
> >>
> >> WHEN
> >> 167 THEN c.max_length ELSE c.max_length/2
> >>                                                            END
> >>                                           END
> >>                 WHEN c.system_type_id = 175 THEN c.max_length
> >>                 WHEN c.system_type_id = 239 THEN c.max_length/2
> >>                 WHEN c.system_type_id = 35 THEN 2147483646
> >>                  WHEN c.system_type_id = 99 THEN 2147483646/2
> >> END
> >> FROM sys.columns c
> >> INNER JOIN sys.types t
> >> ON c.user_type_id = t.user_type_id
> >> WHERE c.object_id = OBJECT_ID('dbo.foo')
> >> AND RIGHT(t.name, 4) IN ('text', 'char')
> >> ORDER BY c.column_id;
> >> GO
> >> DROP TABLE dbo.foo;
> >> GO
> >> EXEC sp_addtype MYTEXT , TEXT
> >> EXEC sp_addtype MYCHAR, 'VARCHAR(50)'
> >> GO
> >> CREATE TABLE dbo.foo
> >> (
> >> a CHAR(32),
> >> b VARCHAR(32),
> >> c NCHAR(32),
> >> d NVARCHAR(32),
> >> e VARCHAR(MAX),
> >> f NVARCHAR(MAX),
> >> g TEXT,
> >> h MYTEXT,
> >> i NTEXT,
> >> j MYCHAR
> >> );
> >> GO
> >> SELECT
> >> c.name as column_name, t.name as type_name,
> >> [datalength] = CASE
> >> WHEN c.system_type_id in (167, 231) THEN -- varchar, nvarchar
> >> CASE c.max_length WHEN -1 THEN 2147483646 ELSE c.max_length END
> >> WHEN c.system_type_id in (175, 239) THEN c.max_length -- char, nchar
> >> WHEN c.system_type_id in (35,99) THEN 2147483646 -- text, ntext
> >> END,
> >> [numchars] = CASE
> >> WHEN c.system_type_id in (167,231) THEN
> >> CASE c.max_length WHEN -1 THEN
> >> CASE c.system_type_id
> >> WHEN 167 THEN 2147483646 ELSE 2147483646/2
> >> END
> >> ELSE
> >> CASE c.system_type_id
> >> WHEN 167 THEN c.max_length ELSE c.max_length/2
> >> END
> >> END
> >> WHEN c.system_type_id = 175 THEN c.max_length
> >> WHEN c.system_type_id = 239 THEN c.max_length/2
> >> WHEN c.system_type_id = 35 THEN 2147483646
> >> WHEN c.system_type_id = 99 THEN 2147483646/2
> >> END
> >> FROM sys.columns c
> >> INNER JOIN sys.types t
> >> ON c.user_type_id = t.user_type_id
> >> WHERE c.object_id = OBJECT_ID('dbo.foo')
> >> AND RIGHT(t.name, 4) IN ('text', 'char')
> >> ORDER BY c.column_id;
> >> GO
> >> DROP TABLE dbo.foo;
> >> GO
> >> EXEC sp_droptype MYTEXT ;
> >> EXEC sp_droptype MYCHAR;
> >> GO
> >> --
> >> HTH
> >> Kalen Delaney, SQL Server MVP
> >>
> >>
> >> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in
> >> message
> >> news:%23pt8t7YkGHA.1276@TK2MSFTNGP03.phx.gbl...
> >> > Do you want datalength or number of characters that will fit?
> >> >
> >> > Here is a quick query for system-defined data types (it gets more
> >> > complex
> >> > if you have custom types, either old style or especially new style).
> >> >
> >> >
> >> >
> >> >
> >> > CREATE TABLE dbo.foo
> >> > (
> >> >    a CHAR(32),
> >> >    b VARCHAR(32),
> >> >    c NCHAR(32),
> >> >    d NVARCHAR(32),
> >> >    e VARCHAR(MAX),
> >> >    f NVARCHAR(MAX),
> >> >    g TEXT,
> >> >    h NTEXT
> >> > );
> >> > GO
> >> >
> >> > SELECT
> >> >    c.name,
> >> >    [datalength] = CASE
> >> > WHEN t.name LIKE '%VARCHAR' THEN
> >> >    CASE c.max_length WHEN -1 THEN 2147483646 ELSE c.max_length END
> >> > WHEN t.name LIKE '%CHAR' THEN c.max_length
> >> > WHEN t.name LIKE '%TEXT' THEN 2147483646
> >> > END,
> >> > [numchars] = CASE
> >> > WHEN t.name LIKE '%VARCHAR' THEN
> >> > CASE c.max_length WHEN -1 THEN
> >> > CASE c.system_type_id WHEN 167 THEN 2147483646 ELSE 2147483646/2 END
> >> > ELSE
> >> > CASE c.system_type_id WHEN 167 THEN c.max_length ELSE c.max_length/2
> >> > END
> >> > END
> >> > WHEN t.name = 'CHAR' THEN c.max_length
> >> > WHEN t.name = 'NCHAR' THEN c.max_length/2
> >> > WHEN t.name = 'TEXT' THEN 2147483646
> >> > WHEN t.name = 'NTEXT' THEN 2147483646/2
> >> > END
> >> > FROM sys.columns c
> >> > INNER JOIN sys.types t
> >> > ON c.system_type_id = t.system_type_id
> >> > WHERE c.object_id = OBJECT_ID('dbo.foo')
> >> > AND RIGHT(t.name, 4) IN ('text', 'char')
> >> > ORDER BY c.column_id;
> >> > GO
> >> >
> >> > DROP TABLE dbo.foo;
> >> > GO
> >> >
> >> >
> >> >
> >> > "Mike" <M***@discussions.microsoft.com> wrote in message
> >> > news:5808FDC2-D8B1-485F-8743-A93FD59FEB5D@microsoft.com...
> >> >> here is my query in sql server 2000 that returns length of text field
> >> >> as
> >> >> 2147483646:
> >> >> select c.name,d.length,d.ss_dtype
> >> >> from syscolumns c
> >> >> join sysobjects o on c.id=o.id,
> >> >> master.dbo.spt_datatype_info d,
> >> >> systypes t
> >> >> where
> >> >> o.name='customer' AND
> >> >> t.xtype = d.ss_dtype AND
> >> >> c.xusertype = t.xusertype
> >> >>
> >> >> In sql 2000, the length of a text field used to display 2147483646 but
> >> >> now
> >> >> in sql 2005 I get 16. I would still like to get 2147483646
> >> >>
> >> >>
> >> >> the problem is that sql server 2005 does not have spt_datatype_info
> >> >> and
> >> >> also
> >> >> it appears that systypes no longer has user defined datatypes. I have
> >> >> user
> >> >> defined data types in my database.
> >> >>
> >> >> How can I get this info in sql 2005 based on the above sql 2000 query.
> >> >>
> >> >> Thanks for your help.
> >> >
> >> >
> >>
> >>
> >>
>
>
>
Author
19 Jun 2006 2:47 PM
Kalen Delaney
I don't like mixing the ANSI SCHEMA views with the new metadata views, but
that may just be my personal preference. I've actually never like the ANSI
SCHEMA views very much.

I would use an undocumented parameter to COLUMNPROPERTY which I found out
about by looking at the definition of the ANSI SCHEMA VIEW sys.columns:


ColumnProperty(object_id, col_name, 'charmaxlen')

--
HTH
Kalen Delaney, SQL Server MVP


Show quote
"Mike" <M***@discussions.microsoft.com> wrote in message
news:3671A7AB-5085-4DEC-AA5F-31167A7DF447@microsoft.com...
> Kalen,
> Sorry about the other thread.
> Thanks a lot for the information.  Do you think I could use
> character_maximum_length from INFORMATION_SCHEMA.COLUMNS to solve this
> problem instead of hard coding the max length?
>
> TIA
>
>
>
> "Kalen Delaney" wrote:
>
>> Mike
>>
>> I'm sorry I did not respond to this fast enough and you felt it necessary
>> to
>> start a new thread. It was posted over 2 hours ago, and you haven't
>> gotten a
>> reply yet. Of course the fact that it IS Sunday, and Fathers' Day in the
>> US,
>> is irrelevant.
>>
>> 1. We divide NTEXT by 2, because each NTEXT character takes 2 bytes. One
>> output column is showing the length in bytes, the other is showing the
>> number of characters.
>>
>> 2. I think they wanted to have the length represent the length of the
>> data
>> stored in the actual data row. When you have a LOB column (text, ntext or
>> image), by default SQL Server will only store a pointer, which is 16
>> bytes.
>> The LOB data itself is stored on other pages, so when trying to figure
>> out
>> how big your rows are, and how many would fit on a page, it could be
>> misleading to have the full length of the LOB data.
>>
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>>
>>
>> "Mike" <M***@discussions.microsoft.com> wrote in message
>> news:B8879772-F0EA-4D59-A28F-D9DC99C3254A@microsoft.com...
>> > Thanks for your help.
>> > I have 2 more questions regarding this for learning purpose
>> > 1. why do we divide 2147483646 by 2 when it is TEXT and not NTEXT ?
>> >> WHEN c.system_type_id = 35 THEN 2147483646
>> >> WHEN c.system_type_id = 99 THEN 2147483646/2
>> > 2. do you think there is any reasoning behind sql team eliminating
>> > 2147483646 for TEXT to 16? I am not able to understand why the sql team
>> > would
>> > do this.
>> > I think I am missing something here.I would really appreciate your help
>> > to
>> > make me understand this concept.
>> >
>> > TIA
>> >
>> >
>> > "Kalen Delaney" wrote:
>> >
>> >> What do you mean by new style datatypes? Do you mean CLR Types? You're
>> >> right, that would be especially tricky.
>> >>
>> >> If you have any old style udd's at all, this will have a problem
>> >> because
>> >> t.system_type_id will not be unique, and you will  get duplicate rows
>> >> for
>> >> some columns.
>> >> The simple fix for that would be to change the join
>> >>     ON c.system_type_id = t.system_type_id
>> >> to
>> >>     ON c.system_type_id = t.user_type_id
>> >>
>> >> t.user_type_id is unique
>> >>
>> >> However, this will still give us problems displaying the user defined
>> >> type
>> >> names and their appropriate lengths.
>> >> I think we can fix that by referencing the system_type_id instead of
>> >> name:
>> >>
>> >> EXEC sp_addtype MYTEXT , TEXT;
>> >> EXEC sp_addtype MYCHAR, 'VARCHAR(50)';
>> >> GO
>> >> CREATE TABLE dbo.foo
>> >> (
>> >> a CHAR(32),
>> >> b VARCHAR(32),
>> >> c NCHAR(32),
>> >> d NVARCHAR(32),
>> >> e VARCHAR(MAX),
>> >> f NVARCHAR(MAX),
>> >> g TEXT,
>> >> h MYTEXT,
>> >> i NTEXT,
>> >> j MYCHAR
>> >> );
>> >> GO
>> >> SELECT
>> >> c.name as column_name, t.name as type_name,
>> >> [datalength] = CASE
>> >>                         WHEN c.system_type_id in (167, 231) THEN --
>> >> varchar,
>> >> nvarchar
>> >>                                                    CASE c.max_length
>> >>                                                               WHEN -1
>> >> THEN
>> >> 2147483646 ELSE c.max_length
>> >>                                                     END
>> >>                         WHEN c.system_type_id in (175, 239) THEN
>> >> c.max_length -- char, nchar
>> >>                          WHEN c.system_type_id in (35,99) THEN
>> >> 2147483646 --
>> >> text, ntext
>> >>  END,
>> >> [numchars] = CASE
>> >>               WHEN c.system_type_id in (167,231) THEN
>> >>                                         CASE c.max_length
>> >>                                                    WHEN -1 THEN
>> >>                                                           CASE
>> >> c.system_type_id
>> >>
>> >> WHEN
>> >> 167 THEN 2147483646 ELSE 2147483646/2
>> >>                                                           END
>> >>                                                     ELSE
>> >>                                                             CASE
>> >> c.system_type_id
>> >>
>> >> WHEN
>> >> 167 THEN c.max_length ELSE c.max_length/2
>> >>                                                            END
>> >>                                           END
>> >>                 WHEN c.system_type_id = 175 THEN c.max_length
>> >>                 WHEN c.system_type_id = 239 THEN c.max_length/2
>> >>                 WHEN c.system_type_id = 35 THEN 2147483646
>> >>                  WHEN c.system_type_id = 99 THEN 2147483646/2
>> >> END
>> >> FROM sys.columns c
>> >> INNER JOIN sys.types t
>> >> ON c.user_type_id = t.user_type_id
>> >> WHERE c.object_id = OBJECT_ID('dbo.foo')
>> >> AND RIGHT(t.name, 4) IN ('text', 'char')
>> >> ORDER BY c.column_id;
>> >> GO
>> >> DROP TABLE dbo.foo;
>> >> GO
>> >> EXEC sp_addtype MYTEXT , TEXT
>> >> EXEC sp_addtype MYCHAR, 'VARCHAR(50)'
>> >> GO
>> >> CREATE TABLE dbo.foo
>> >> (
>> >> a CHAR(32),
>> >> b VARCHAR(32),
>> >> c NCHAR(32),
>> >> d NVARCHAR(32),
>> >> e VARCHAR(MAX),
>> >> f NVARCHAR(MAX),
>> >> g TEXT,
>> >> h MYTEXT,
>> >> i NTEXT,
>> >> j MYCHAR
>> >> );
>> >> GO
>> >> SELECT
>> >> c.name as column_name, t.name as type_name,
>> >> [datalength] = CASE
>> >> WHEN c.system_type_id in (167, 231) THEN -- varchar, nvarchar
>> >> CASE c.max_length WHEN -1 THEN 2147483646 ELSE c.max_length END
>> >> WHEN c.system_type_id in (175, 239) THEN c.max_length -- char, nchar
>> >> WHEN c.system_type_id in (35,99) THEN 2147483646 -- text, ntext
>> >> END,
>> >> [numchars] = CASE
>> >> WHEN c.system_type_id in (167,231) THEN
>> >> CASE c.max_length WHEN -1 THEN
>> >> CASE c.system_type_id
>> >> WHEN 167 THEN 2147483646 ELSE 2147483646/2
>> >> END
>> >> ELSE
>> >> CASE c.system_type_id
>> >> WHEN 167 THEN c.max_length ELSE c.max_length/2
>> >> END
>> >> END
>> >> WHEN c.system_type_id = 175 THEN c.max_length
>> >> WHEN c.system_type_id = 239 THEN c.max_length/2
>> >> WHEN c.system_type_id = 35 THEN 2147483646
>> >> WHEN c.system_type_id = 99 THEN 2147483646/2
>> >> END
>> >> FROM sys.columns c
>> >> INNER JOIN sys.types t
>> >> ON c.user_type_id = t.user_type_id
>> >> WHERE c.object_id = OBJECT_ID('dbo.foo')
>> >> AND RIGHT(t.name, 4) IN ('text', 'char')
>> >> ORDER BY c.column_id;
>> >> GO
>> >> DROP TABLE dbo.foo;
>> >> GO
>> >> EXEC sp_droptype MYTEXT ;
>> >> EXEC sp_droptype MYCHAR;
>> >> GO
>> >> --
>> >> HTH
>> >> Kalen Delaney, SQL Server MVP
>> >>
>> >>
>> >> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in
>> >> message
>> >> news:%23pt8t7YkGHA.1276@TK2MSFTNGP03.phx.gbl...
>> >> > Do you want datalength or number of characters that will fit?
>> >> >
>> >> > Here is a quick query for system-defined data types (it gets more
>> >> > complex
>> >> > if you have custom types, either old style or especially new style).
>> >> >
>> >> >
>> >> >
>> >> >
>> >> > CREATE TABLE dbo.foo
>> >> > (
>> >> >    a CHAR(32),
>> >> >    b VARCHAR(32),
>> >> >    c NCHAR(32),
>> >> >    d NVARCHAR(32),
>> >> >    e VARCHAR(MAX),
>> >> >    f NVARCHAR(MAX),
>> >> >    g TEXT,
>> >> >    h NTEXT
>> >> > );
>> >> > GO
>> >> >
>> >> > SELECT
>> >> >    c.name,
>> >> >    [datalength] = CASE
>> >> > WHEN t.name LIKE '%VARCHAR' THEN
>> >> >    CASE c.max_length WHEN -1 THEN 2147483646 ELSE c.max_length END
>> >> > WHEN t.name LIKE '%CHAR' THEN c.max_length
>> >> > WHEN t.name LIKE '%TEXT' THEN 2147483646
>> >> > END,
>> >> > [numchars] = CASE
>> >> > WHEN t.name LIKE '%VARCHAR' THEN
>> >> > CASE c.max_length WHEN -1 THEN
>> >> > CASE c.system_type_id WHEN 167 THEN 2147483646 ELSE 2147483646/2 END
>> >> > ELSE
>> >> > CASE c.system_type_id WHEN 167 THEN c.max_length ELSE c.max_length/2
>> >> > END
>> >> > END
>> >> > WHEN t.name = 'CHAR' THEN c.max_length
>> >> > WHEN t.name = 'NCHAR' THEN c.max_length/2
>> >> > WHEN t.name = 'TEXT' THEN 2147483646
>> >> > WHEN t.name = 'NTEXT' THEN 2147483646/2
>> >> > END
>> >> > FROM sys.columns c
>> >> > INNER JOIN sys.types t
>> >> > ON c.system_type_id = t.system_type_id
>> >> > WHERE c.object_id = OBJECT_ID('dbo.foo')
>> >> > AND RIGHT(t.name, 4) IN ('text', 'char')
>> >> > ORDER BY c.column_id;
>> >> > GO
>> >> >
>> >> > DROP TABLE dbo.foo;
>> >> > GO
>> >> >
>> >> >
>> >> >
>> >> > "Mike" <M***@discussions.microsoft.com> wrote in message
>> >> > news:5808FDC2-D8B1-485F-8743-A93FD59FEB5D@microsoft.com...
>> >> >> here is my query in sql server 2000 that returns length of text
>> >> >> field
>> >> >> as
>> >> >> 2147483646:
>> >> >> select c.name,d.length,d.ss_dtype
>> >> >> from syscolumns c
>> >> >> join sysobjects o on c.id=o.id,
>> >> >> master.dbo.spt_datatype_info d,
>> >> >> systypes t
>> >> >> where
>> >> >> o.name='customer' AND
>> >> >> t.xtype = d.ss_dtype AND
>> >> >> c.xusertype = t.xusertype
>> >> >>
>> >> >> In sql 2000, the length of a text field used to display 2147483646
>> >> >> but
>> >> >> now
>> >> >> in sql 2005 I get 16. I would still like to get 2147483646
>> >> >>
>> >> >>
>> >> >> the problem is that sql server 2005 does not have spt_datatype_info
>> >> >> and
>> >> >> also
>> >> >> it appears that systypes no longer has user defined datatypes. I
>> >> >> have
>> >> >> user
>> >> >> defined data types in my database.
>> >> >>
>> >> >> How can I get this info in sql 2005 based on the above sql 2000
>> >> >> query.
>> >> >>
>> >> >> Thanks for your help.
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>
Author
19 Jun 2006 3:10 PM
Aaron Bertrand [SQL Server MVP]
> I would use an undocumented parameter to COLUMNPROPERTY which I found out
> about by looking at the definition of the ANSI SCHEMA VIEW sys.columns:
>
>
> ColumnProperty(object_id, col_name, 'charmaxlen')

Wow, good find Kalen!!!
Author
19 Jun 2006 3:16 PM
Kalen Delaney
Thanks...
Actually, I goofed, it is the ANSI VIEW 'columns' that has this info (not
sys.columns). There are a lot of details in the old system tables (now
accessible as compatibility views) and the ANSI views that are not available
in the new metadata, but you can see how the data is accessed by looking at
the definition of any view:

               select
object_definition(object_id('information_schema.columns'))

I have found about half a dozen undocumented parameters to the various
PROPERTY functions by looking at metadata view definitions.
--
HTH
Kalen Delaney, SQL Server MVP


Show quote
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:eaVZRK7kGHA.3816@TK2MSFTNGP02.phx.gbl...
>> I would use an undocumented parameter to COLUMNPROPERTY which I found out
>> about by looking at the definition of the ANSI SCHEMA VIEW sys.columns:
>>
>>
>> ColumnProperty(object_id, col_name, 'charmaxlen')
>
> Wow, good find Kalen!!!
>

AddThis Social Bookmark Button

Post Other interesting topics
one to (one or zero) relationship
ORDER BY clause with unknown column name
Dyncamic SQL
Second try: Need a little help with a sql query
how to select