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
精彩评论