locked
Not Null RRS feed

  • Question

  • I need to find all the columns from all tables that have NOT NULL constaint of my database. Is there any script?

    Thanks in advance...

    Friday, August 3, 2012 3:33 PM

Answers

  • select * from INFORMATION_SCHEMA.COLUMNS where IS_NULLABLE = 'NO'

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Friday, August 3, 2012 3:41 PM
  • Figured it out...

    
    
    
    

    Select a.Name AS "Table Name", b.Name AS "Column Name", b.IsNullable

    FROM dbo.sysobjects a INNER JOIN dbo.syscolumns b ON a.id = b.id

    Where a.xtype  = 'U'

    AND b.isnullable = 0

    • Marked as answer by KP_SQL Friday, August 3, 2012 4:03 PM
    Friday, August 3, 2012 3:50 PM

All replies

  • select * from INFORMATION_SCHEMA.COLUMNS where IS_NULLABLE = 'NO'

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Friday, August 3, 2012 3:41 PM
  • Figured it out...

    
    
    
    

    Select a.Name AS "Table Name", b.Name AS "Column Name", b.IsNullable

    FROM dbo.sysobjects a INNER JOIN dbo.syscolumns b ON a.id = b.id

    Where a.xtype  = 'U'

    AND b.isnullable = 0

    • Marked as answer by KP_SQL Friday, August 3, 2012 4:03 PM
    Friday, August 3, 2012 3:50 PM
  • You can try this as well.

    SELECT T.name, C.name, * FROM sys.columns C LEFT JOIN sys.tables T ON C.object_id = T.object_id WHERE is_nullable = 0

    AND

    [type] = 'U'

    ORDER BY T.name, C.name


    Hope this answers your question.


    Service Delivery & Support Management


    Friday, August 3, 2012 3:54 PM