INFORMATIONSCHEMA nullable

13 Jul 2006 5:05 PM

Klaus Wiesel

Hello

using INFORMATIONSCHEMA.COLUMNS I get IS_NULLABLE as varchar ("NO"/"YES")

1. Is this always english (constants NO and YES) or does it depend on
settings ?
2. Is there a way to get a boolean from this column (language save cast)?
3. Is there an alternative source (except sp_columns)?

Regards
Klaus Wiesel

13 Jul 2006 5:15 PM

SQL Menace

3) SELECT COLUMNPROPERTY( OBJECT_ID('authors'),'contract','AllowsNull')

1 = TRUE
0 = FALSE
NULL = Invalid input

Denis the SQL Menace
http://sqlservercode.blogspot.com/

Klaus Wiesel wrote:
Show quote

> Hello
>
> using INFORMATIONSCHEMA.COLUMNS I get IS_NULLABLE as varchar ("NO"/"YES")
>
> 1. Is this always english (constants NO and YES) or does it depend on
> settings ?
> 2. Is there a way to get a boolean from this column (language save cast)?
> 3. Is there an alternative source (except sp_columns)?
>
> Regards
> Klaus Wiesel

13 Jul 2006 5:15 PM

Roy Harvey

On Thu, 13 Jul 2006 19:05:30 +0200, Klaus Wiesel
<klaus.wie***@infor.com> wrote:

>Hello
>
>using INFORMATIONSCHEMA.COLUMNS I get IS_NULLABLE as varchar ("NO"/"YES")
>
>1. Is this always english (constants NO and YES) or does it depend on
>settings ?

Not according to Books on Line documentation of
INFORMATION_SCHEMA.COLUMNS: "Nullability of the column. If this column
allows for NULL, this column returns YES. Otherwise, NO is returned."

>2. Is there a way to get a boolean from this column (language save cast)?

You could use CASE, though I don't see the point of doing so.

>3. Is there an alternative source (except sp_columns)?

In 2000 and earlier there is syscoolumns. In 2005 there is
sys.columns.

>
>Regards
>Klaus Wiesel

Roy Harvey
Beacon Falls, CT

13 Jul 2006 5:17 PM

Aaron Bertrand [SQL Server MVP]

It's helpful if you specify the version of SQL Server you are using.

If you are using SQL Server 2005 you can use sys.columns.is_nullable.

SELECT is_nullable
FROM sys.columns
WHERE [object_id] = OBJECT_ID('dbo.table_name')
AND [name] = 'column_name';

If you are in 2000, you can use:

SELECT COLUMNPROPERTY
(
    OBJECT_ID('dbo.table_name'),
    'column_name',
    'AllowsNull'
);

Show quote

"Klaus Wiesel" <klaus.wie***@infor.com> wrote in message
news:%23f9RN6ppGHA.1140@TK2MSFTNGP05.phx.gbl...
> Hello
>
> using INFORMATIONSCHEMA.COLUMNS I get IS_NULLABLE as varchar ("NO"/"YES")
>
> 1. Is this always english (constants NO and YES) or does it depend on
> settings ?
> 2. Is there a way to get a boolean from this column (language save cast)?
> 3. Is there an alternative source (except sp_columns)?
>
> Regards
> Klaus Wiesel

14 Jul 2006 8:27 AM

Klaus Wiesel

Aaron, thanks for your support

That means there is no way to get this piece of info that works for both
versions of sql server?

Regards
Klaus Wiesel

Aaron Bertrand [SQL Server MVP] wrote:
Show quote

> It's helpful if you specify the version of SQL Server you are using.
>
> If you are using SQL Server 2005 you can use sys.columns.is_nullable.
>
> SELECT is_nullable
> FROM sys.columns
> WHERE [object_id] = OBJECT_ID('dbo.table_name')
> AND [name] = 'column_name';
>
> If you are in 2000, you can use:
>
> SELECT COLUMNPROPERTY
> (
>     OBJECT_ID('dbo.table_name'),
>     'column_name',
>     'AllowsNull'
> );
>
>
>
>
> "Klaus Wiesel" <klaus.wie***@infor.com> wrote in message
> news:%23f9RN6ppGHA.1140@TK2MSFTNGP05.phx.gbl...
>> Hello
>>
>> using INFORMATIONSCHEMA.COLUMNS I get IS_NULLABLE as varchar ("NO"/"YES")
>>
>> 1. Is this always english (constants NO and YES) or does it depend on
>> settings ?
>> 2. Is there a way to get a boolean from this column (language save cast)?
>> 3. Is there an alternative source (except sp_columns)?
>>
>> Regards
>> Klaus Wiesel
>
>

14 Jul 2006 12:49 PM

Aaron Bertrand [SQL Server MVP]

COLUMNPROPERTY will work in SQL Server 2000 and 2005 (as will syscolumns).

In 2005, I think sys.columns is the better place to get this information,
but that's just a personal preference.

A

Show quote

"Klaus Wiesel" <klaus.wie***@infor.com> wrote in message
news:ODMgb9xpGHA.1140@TK2MSFTNGP05.phx.gbl...
> Aaron, thanks for your support
>
> That means there is no way to get this piece of info that works for both
> versions of sql server?
>
> Regards
> Klaus Wiesel
>
>
> Aaron Bertrand [SQL Server MVP] wrote:
>> It's helpful if you specify the version of SQL Server you are using.
>>
>> If you are using SQL Server 2005 you can use sys.columns.is_nullable.
>>
>> SELECT is_nullable
>> FROM sys.columns
>> WHERE [object_id] = OBJECT_ID('dbo.table_name')
>> AND [name] = 'column_name';
>>
>> If you are in 2000, you can use:
>>
>> SELECT COLUMNPROPERTY
>> (
>>     OBJECT_ID('dbo.table_name'),
>>     'column_name',
>>     'AllowsNull'
>> );
>>
>>
>>
>>
>> "Klaus Wiesel" <klaus.wie***@infor.com> wrote in message
>> news:%23f9RN6ppGHA.1140@TK2MSFTNGP05.phx.gbl...
>>> Hello
>>>
>>> using INFORMATIONSCHEMA.COLUMNS I get IS_NULLABLE as varchar
>>> ("NO"/"YES")
>>>
>>> 1. Is this always english (constants NO and YES) or does it depend on
>>> settings ?
>>> 2. Is there a way to get a boolean from this column (language save
>>> cast)?
>>> 3. Is there an alternative source (except sp_columns)?
>>>
>>> Regards
>>> Klaus Wiesel
>>