Post reply

Identifying the NULL Value Columns In a particular Table

  • July 20, 2011 at 4:14 am

    #244533

    Hi Frds,

    I have a table consists of around 200 Columns. I would like to find out the NULL Value Columns.

    How do I make it? Please let me know ASAP.

    Thanks In Advance,

    Sandeep IVS

  • July 20, 2011 at 7:09 am

    #1355565

    Do you want to see which columns allow Nulls? You could script the table's create statement out and look at the DDL where the column allows NULLs. That should be pretty straight-forward; in SSMS right click on the table, Script Table, CREATE TO,

    Or do you want to see which columns actually have Nulls? You could create a simple SSIS package using the Data Profiling Task and profile the particular table for "Column Null Ratio Profile Request". This creates an .xml file that will show you all columns and the % of Null records in each column when looked at in the viewer (C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DataViewer.exe)

    HTH,

    Rob

  • July 20, 2011 at 8:01 am

    #1355627

    Try this simple query:

    SELECT

    [TABLE_CATALOG],

    [TABLE_SCHEMA],

    [COLUMN_NAME],

    [COLUMN_DEFAULT],

    [DATA_TYPE],

    [IS_NULLABLE]

    FROM INFORMATION_SCHEMA.COLUMNS

    WHERE [TABLE_NAME] = 'TableName'

    AND [IS_NULLABLE] = 'YES'

    Edited: Changed IS_NULLABLE = 'Yes'

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply