CODEWITHSUNDEEP
mysqlsqlsql-server
Torrm
Torrm

Reputation: 153

SQL - Select rows that contain a NULL value in a non-nullable column

I'm hoping somebody can help me with a script / query, the target DB is mySQL.

The database I am working with does not conform to it's own constraints and is in the process of being moved to MS SQL. What I am looking to find is a query that can be run against a table which looks for rows that contain a null value in a column that does not allow nulls, which in turn will assist with SSIS DFT debugging times.

Many thanks.

Upvotes: 0

Views: 1283

Answers (1)

Haytem BrB
Haytem BrB

Reputation: 1499

Try:

SELECT group_concat(`COLUMN_NAME`) as myList 
FROM `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE `TABLE_SCHEMA`='yourdatabasename' 
    AND `TABLE_NAME`='yourtablename'
--  AND `IS_NULLABLE`='NO'
into @colname;
SET @query = CONCAT('SELECT ',@colname,' FROM yourtablename');
PREPARE stmt FROM @query;
EXECUTE stmt;

Upvotes: 1

Related Questions