Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Getting Allow Nulls information Expand / Collapse
Author
Message
Posted Monday, October 08, 2007 3:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 09, 2007 1:54 PM
Points: 3, 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.
Post #407903
Posted Monday, October 08, 2007 4:32 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 1:27 AM
Points: 2,366, Visits: 1,837
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"
Post #407909
Posted Monday, October 08, 2007 5:09 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 26, 2012 5:26 AM
Points: 1,367, Visits: 1,585
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
Post #407919
Posted Monday, October 08, 2007 9:20 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 21, 2012 5:26 AM
Points: 34, 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.
Post #408050
Posted Monday, October 08, 2007 10:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 09, 2007 1:54 PM
Points: 3, Visits: 6
Hi, that worked - thanks very much. I wonder if you know how to do the same thing in MS Access.
Post #408077
Posted Monday, October 08, 2007 10:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 09, 2007 1:54 PM
Points: 3, Visits: 6
Much appreciated.
Post #408078
Posted Tuesday, October 09, 2007 4:08 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 21, 2012 5:26 AM
Points: 34, 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.
Post #408361
Posted Tuesday, October 09, 2007 6:53 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 12:23 PM
Points: 25, Visits: 1,334
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')



Post #408438
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse