Does table field allow nulls?
-
Hello,
I'm wondering if there is a statement which will return whether a stated field in a stated table will allow null values or not.
This is for a program I'm writing which creates SQL statements based on a list of table names. However, some of tha tables are not as well designed as I might have hoped with many dodgy entries. I'm writing the program in PHP, and using a program to compile it into an executable, however, PHP (although well suited to the task) seems to have issues identifying the difference between '', and NULL. A request on field information does not tell me whether the field allows NULL or not.
I've spent some time looking through the system tables of my test database trying to find a way to formulate a query which would provide a simple true or false, depending on whether a field is null or not, but having no luck and cant find any info on the web which may help.
Can anyone help?
Thanks for reading this,
Allan.
-
I do not know PHP but if you need a sql query then
select c.isnullable
from sysobjects o
inner join syscolumns c
on c.[id] = o.[id]
and c.[name] = 'column'
where o.[name] = 'table'
or if you want all the columns for a table then
select c.[name],c.isnullable
from sysobjects o
inner join syscolumns c
on c.[id] = o.[id]
where o.[name] = 'table'
Far away is close at hand in the images of elsewhere.
Anon. -
David,
This is perfect. Thanks very much.
I had a quick look in syscolumns but totally missed the isnullable field.
All the best.
-
You can also use the INFORMATION_SCHEMA views to get metadata.
select IS_NULLABLE from information_schema.columns
where table_name = 'table' and column_name = 'column'
This returns 'Yes' or 'No'.
This view is simply looking at syscolumns.isnullable, but Microsoft recommends using INFORMATION_SCHEMA views so you don't have to worry about system table changes in future versions.
Okay, you can stop the cynical laughter now.
-
I guess, nobody will laugh when you can (and should) use the INFORMATION_SCHEMA views.
Anyway, there might be an even easier way to determine this property.
SELECT COLUMNPROPERTY( OBJECT_ID('authors'),'au_lname','AllowsNull')
returns 1, if the the column allow for NULL; 0 when not and NULL when you gave some invalid input
See COLUMNPROPERTY in BOL for more nice and useful properties.
Btw, Dave, php is really fun now that I'm getting acquitant with it
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Nice one Scott
, your right, I thought of that but only after I posted 
Even better Frank, like that one, bow to your superior wisdom
yet again
As for PHP, I suppose I will have to look at it one day, when I get some free time
Far away is close at hand in the images of elsewhere.
Anon. -
Aah, more of this...
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply