SQL: how to know if a field has "Allow nulls" checked or not checked by SQL command

Question

Hi, I want to know which table's field are required or not required so I have to get "Allow nulls" state. How to do that?

Answer 1

I will assume you are talking about SQL Server.

There is a table, INFORMATION_SCHEMA.COLUMNS, that contains meta-data about the columns in the database.

You can do this:

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME

IS_NULLABLE gives you the "Allow Nulls" value used in the designer.

Answer 2

If your in MySQL use the sql command

DESCRIBE Table;

Where table is the name of the table you want to examine

Answer 3

Try this (SQL Server)

select sysobjects.name, syscolumns.name, syscolumns.isnullable
from   sysobjects join syscolumns
   on  sysobjects.id    = syscolumns.id
  and  sysobjects.xtype = 'U'
  and  sysobjects.name  = 'your table name'