SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Getting Allow Nulls information


Getting Allow Nulls information

Author
Message
bmcallister
bmcallister
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 6
I am comparing two databases. When a field is missing from one I generate it in the other. In Delphi I use code such as the following to find the type and size of a field from a query result:



TADOQuery.Fields[i].DataType

TADOQuery.Fields[i].Size



As none of the available properties seem to correspond to Allow Nulls I don't know how to find out whether a field allows nulls.
ChiragNS
ChiragNS
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4197 Visits: 1865
Hi



Did not understand you clearly but still...



If you want to look whether a particular column allows nulls you can query the sys.columns or information_schema.columns views. Both these views have the is_nullable column which shows whether column allows nulls.

"Keep Trying"
Andras Belokosztolszki
Andras Belokosztolszki
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2963 Visits: 1585
Chirag is right, you need to get this information by querying the system views. The result you are getting is the result of a query. Whether a returned column is nullable does not really make sense in this context.

e.g. if your query is (select 1 as a) is "a" nullable? The datatype can be sent back to you, and this is the reason you do not see a "IsNullable" property. I.e. the query may get the data from a number of tables, may aggregate result, may contain constants, etc. It is there to consume, but nullability is a constraint, and is used when you set values.





Regards,

Andras



Andras Belokosztolszki, MCPD, PhD
GoldenGate Software
Daniel Smith-480684
Daniel Smith-480684
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 Visits: 49
If you run this:

EXEC sp_help MyTroublesomeTable you'll find five returned recordsets. The second of which details information about the columns within the table. The column Nullable should give you the info you need.



Running EXEC sp_columns MyTroublesomeTable , as its name suggests, focuses purely on the columns in the specified table; returning only one recordset. The columns NULLABLE and IS_NULLABLE could be of interest to you.



Hope this helps, or at least gives you a prod in the right direction.
bmcallister
bmcallister
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 6
Hi, that worked - thanks very much. I wonder if you know how to do the same thing in MS Access.
bmcallister
bmcallister
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 6
Much appreciated.
Daniel Smith-480684
Daniel Smith-480684
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 Visits: 49
I can't really help you with MS Access. Sorry about that.



sp_help and sp_columns are system stored procedures and I don't know if their equivalents exist in Access. Perhaps they do in the later/most recent editions. However, I would guess not.Sad
Feifarek
Feifarek
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 1641
Access appears to require more work-- you will need to use the ADOX type library, which you can import into Delphi ("Install ActiveX Controls" under the Component menu in older versions of Delphi).

You should be able to read the columns and their attributes in the table. When I tried this several years ago (ADO 2.5) the Jet engine didn't report the Nullable property correctly for Access, but newer versions are reported to work correctly.



Column.Properties['Nullable'].Value

or

If (Column.Attributes And adColNullable = 0)

are ways to read the NULL/NOT NULL setting for a field.



I usually refer to www.fulltextsearch.com for help with these issues.



By the way, another way I've checked in SQL Server is with the function COLUMNPROPERTY(OBJECT_ID('TableName'), 'ColumnName', 'AllowsNull')



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search