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

    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 a.name as tablename, b.name,c.name as coltype,b.length as collength,b.status as status
    from sysobjects a, syscolumns b, systypes c
    where a.name in ('ADVERTISER','DISCOUNT','AVAILABLE_RESOURCE') and a.id=b.id and b.type=c.type and b.usertype=c.usertype and a.type='U'
    order by a.name,b.colid

    select a.name as tablename, b.name as columnname,c.name 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
    a.id=b.id and
    b.usertype*=c.usertype
    order by a.name,b.colid

  2. #2
    Join Date
    Aug 2003
    Posts
    43
    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
    Posts
    1

    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:
    Code:
    SELECT
      name as column_name,
      (case when (status&8) != 0
            then 'Y'
            else 'N' end) as is_nullable
    from
       syscolumns

Posting Permissions

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