user1027076 user1027076 - 7 months ago 6
SQL Question

SQL table - get count No. of mandatory columns in a table

How to get ( Count ) the of No. of mandatory columns in a table?

Answer

I assume you mean NOT NULL, to do this in SQL Server you could do:

SELECT sys.tables.name, c.name
FROM sys.tables
INNER JOIN sys.columns c ON c.object_id = sys.tables.object_id
WHERE COLUMNPROPERTY(OBJECT_ID(sys.tables.name),c.name,'AllowsNull') IS NOT NULL

This retrieves all mandatory columns for each table in the database. You could modify this query by specifying just one table.

SELECT COUNT(name) FROM sys.columns
WHERE OBJECT_ID = OBJECT_ID('Client') 
AND COLUMNPROPERTY(OBJECT_ID('Client'),name,'AllowsNull') IS NOT NULL

Would COUNT the mandatory columns for the Client table.