Finding nullable columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Finding nullable columns

Author

Topic

gautham.gn
Starting Member

19 Posts

Posted - 2013-11-21 : 07:30:02

I have a requirement that.,
I have 4 particular columns (crt_dt,upd_dt,entity_active and user_idn) in many of the tables in my database. Now i have to find all the tables having four columns mentioned above and cases are
1) if the column is nullable., then it should result 'Y'
2) if the column is not nullable., then it should result 'N'
3)if column is not present., then it should display '-'

Thanks in advance.,
Gautham

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-21 : 07:40:40

[code]
SELECT t.TABLE_NAME,t1.COLNAME, CASE WHEN c.IS_NULLABLE = 'Yes' THEN 'Y' WHEN c.IS_NULLABLE = 'No' THEN 'N' WHEN c.COLUMN_NAME IS NULL THEN '-' END
FROM INFORMATION_SCHEMA.TABLES t
CROSS JOIN (SELECT 'crt_dt' AS ColName UNION ALL
SELECT 'upd_dt' UNION ALL
SELECT 'entity_active' UNION ALL
SELECT 'user_idn'
)t1
LEFT JOIN INFORMATION_SCHEMA.COLUMNS c
ON c.TABLE_NAME = t.TABLE_NAME
AND c.SCHEMA_NAME = t.SCHEMA_NAME
AND c.COLUMN_NAME =t1.COLNAME
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

gautham.gn
Starting Member

19 Posts

Posted - 2013-11-21 : 07:46:04

Thanks dude., But I want the columns as Table_name, crt_dt_is_nullable,upd_dt_is_nullable,entity_active_is_nullable,user_idn_is_nullable.. I want these columns in the result set.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-21 : 08:50:17

use pivot logic


SELECT TABLE_NAME,
COALESCE([crt_dt],'-') AS crt_dt_is_nullable,
COALESCE([upd_dt],'-') AS upd_dt_is_nullable,
COALESCE([entity_active],'-') AS entity_active_is_nullable,
COALESCE([user_idn],'-') AS user_idn_is_nullable
FROM
(
SELECT t.TABLE_NAME,t1.COLNAME, CASE WHEN c.IS_NULLABLE = 'Yes' THEN 'Y' WHEN c.IS_NULLABLE = 'No' THEN 'N' WHEN c.COLUMN_NAME IS NULL THEN NULL END AS IS_NULLABLE
FROM INFORMATION_SCHEMA.TABLES t
CROSS JOIN (SELECT 'crt_dt' AS ColName UNION ALL
            SELECT 'upd_dt' UNION ALL
            SELECT 'entity_active' UNION ALL
            SELECT 'user_idn'
           )t1
LEFT JOIN INFORMATION_SCHEMA.COLUMNS c
ON c.TABLE_NAME = t.TABLE_NAME
AND c.SCHEMA_NAME = t.SCHEMA_NAME
AND c.COLUMN_NAME =t1.COLNAME
)m
PIVOT (MAX(IS_NULLABLE) FOR COLNAME IN ([crt_dt],[upd_dt],[entity_active],[user_idn]))n

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

gautham.gn
Starting Member

19 Posts

Posted - 2013-11-21 : 23:31:45

Thanks dude.. Worked perfect..:)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-22 : 05:38:49

cool

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources