SQL- Identify nullable values

I am relatively new to SQL queries.

I 开发者_如何学运维have a large number of tables in my SQL Database ( over 1500 )

My question is as follows:

I need to identify columns which are nullable from all the tables which have default values?

How can I go about it for all the tables?

Any help or tutorial for the same would be also very helpful.

Thank you

You can use information_schema to get this data, the columns "COLUMN_DEFAULT" and "IS_NULLABLE" will give you what you need.

SELECT *
FROM information_schema.columns c with (Nolock)

Use the self-describing features of SQL Server :-

SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS 
WHERE IS_NULLABLE = 'YES'
OR COLUMN_DEFAULT IS NOT NULL

SELECT
    OBJECT_NAME(c.object_id), *
FROM
    sys.columns c
    JOIN
    sys.default_constrainst dc ON c.columnid = dc.parent_column_id AND c.object_id = dc.parent_object_id
WHERE
    c.is_nullable = 1