Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2007

    Unanswered: How do i know nullable columns from sys tables

    I have written following query to get the tablename, columnname, columntype, columnlength, precision, scale, status (value 128 tells it is identity column) . The issue i have is that, iam unable to know if a column is nullable or not. How do i get that information. Please help....

    select as tablename,, as coltype,b.length as collength,b.status as status
    from sysobjects a, syscolumns b, systypes c
    where in ('ADVERTISER','DISCOUNT','AVAILABLE_RESOURCE') and and b.type=c.type and b.usertype=c.usertype and a.type='U'
    order by,b.colid

    select as tablename, as columnname, as coltype,
    b.length as collength,b.status as status,
    convert(varchar,b.prec) as precision,convert(varchar,b.scale) as scale
    from sysobjects a, syscolumns b, systypes c
    where a.type='U' and and
    order by,b.colid

  2. #2
    Join Date
    Aug 2003
    if the status & 8 is non zero for the corresponding entry in syscolumns then it is nullable. if it (status & 8 ) results in zero then it is not nullable.

  3. #3
    Join Date
    Jan 2016

    Lightbulb Select query to check if column is nullable

    As adurga meant, status&8 will indicate the nullability of the column, so you can use following query to check if column is nullable:
      name as column_name,
      (case when (status&8) != 0
            then 'Y'
            else 'N' end) as is_nullable

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts