开发者

SQL- Identify nullable values

开发者 https://www.devze.com 2023-04-09 23:59 出处:网络
I am relatively new to SQL queries. I 开发者_如何学运维have a large number of tables in my SQL Database ( over 1500 )

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
0

精彩评论

暂无评论...
验证码 换一张
取 消