Getting a list of columns that accept null values

[Natrajk]

Hi,

I need to find a way to generate a list of columns that
accept null values for a given table.
The easiest for me would be to do this through a SELECT
statement, but I haven't been able to figure out how.

regards
Natrajk

 

[Tu-Thach]

Natrajk,

Use GetSchemaTable on a data reader. There is a column
on the resulting table called "AllowDBNull" that you can
check to see if the column allows null.

Tu-Thach

 

[Andrew]

I may be re-inventing the wheel but here is an sql statement that can be
used to list all the fields defined in your database. The isnullable field
is what you are looking for I believe.

SELECT TOP 100 PERCENT dbo.sysobjects.name AS TableName,
dbo.syscolumns.name, dbo.syscolumns.length, dbo.systypes.name AS DataType,
dbo.syscolumns.isnullable
FROM dbo.sysobjects INNER JOIN
dbo.syscolumns ON dbo.sysobjects.id =
dbo.syscolumns.id INNER JOIN
dbo.systypes ON dbo.syscolumns.usertype =
dbo.systypes.usertype AND dbo.syscolumns.xusertype = dbo.systypes.xusertype
WHERE (dbo.sysobjects.xtype = 'u')
ORDER BY dbo.sysobjects.name

 

[Andrew]

Oh boy, I have just seen the light ! There is an aweful lot of info
available from those Info Schemas! Thanks for the tip.

 

[Joe Fallon]

The person at MS who recommended that "policy" has qualified it recently
with this comment:

So here is the important clarification. The reason for the message about
"don't directly access system tables" or "don't access fields that are
undocumented or reserved" is to prevent applications from breaking on
version upgrades. It isn't to discourage the use of the system tables for
diagnostic purposes. There are no stability problems with querying the
system tables, and there is generally no desire to hide (even the
undocumented) information from anyone. It's simply that if you go and embed
some check of an undocumented flag in your application, that application may
not work when the next version (or, theoretically, even service pack) comes
out. And that's a bad thing. So the real advice should be "don't put this
in your code". You want to interactively look at this stuff, great. You
want to put it in some version-specific diagnostic toolbag you have, that's
ok too. Just don't put it into application code that you expect others to
rely on. At some point in the (not too distant) future they'll be in for an
unpleasant surprise.

 

[Kathleen Dollard]

Joe,

You're right. I was assuming this was for code.

Kathleen