October 8, 2007 at 3:47 am
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.DataType
TADOQuery.Fields.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.
October 8, 2007 at 4:32 am
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"
October 8, 2007 at 5:09 am
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
October 8, 2007 at 9:20 am
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.
October 8, 2007 at 10:01 am
Hi, that worked - thanks very much. I wonder if you know how to do the same thing in MS Access.
October 8, 2007 at 10:02 am
Much appreciated.
October 9, 2007 at 4:08 am
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.:(
October 9, 2007 at 6:53 am
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 http://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')
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply