0

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.

Torrm
  • 153
  • 1
  • 14
  • Does the table actually have a constraint? What have you tried so far? – Tom H Jan 29 '16 at 15:42
  • No, several individual columns do but some of the data does not abide by the constraints. Currently I am having to alter the constrains based on errors when trying to insert the data into the new database, as the new database will enforce constraints. – Torrm Jan 29 '16 at 15:45
  • How are you determining which columns are supposed to allow NULLs. Your question seems very unclear to me. – Tom H Jan 29 '16 at 15:47
  • As @TomH already commented, Your question is vaque and the reason to move isn't actually the real problem. Maybe it was implemented wrong and you should look into that first before jumping to a whole other db server. – davejal Jan 29 '16 at 15:48
  • Several columns in the source database are set to not allow null values, however the dataset contains null values regardless. The new database is based on the source database but will not allow data to be inserted that doesn't conform which is why I am looking for a quick way to highlight the problematic columns. I apoligise if I am not explaining this very well – Torrm Jan 29 '16 at 15:51
  • @davejal You are correct, but moving to MS SQL is part of the solution. – Torrm Jan 29 '16 at 15:51
  • Check if there is any constrains associated with the table. If non nullable is your business logic, then there is no choice other than checking is null – Sanu Antony Jan 29 '16 at 15:52
  • @SanuAntony Thanks, I can confirm that no constraints are associated with the table. Non nullable is the business logic however the data that already exists needs to be retained, so first the data needs to be moved to the new database at which point a decision can be made. My question is focused around locating a script that can highlight these problematic columns. – Torrm Jan 29 '16 at 15:56
  • If the database isn't "conforming to it's own constraints", then they aren't constraints; they're suggestions. There are always zero NULL values in a column that does not allow NULLs. If that count goes above zero, the column allows nulls. – Brian Stork Jan 29 '16 at 15:58
  • @BrianStork Thanks for the explanation, you are correct. Any suggestions on a query? – Torrm Jan 29 '16 at 16:02
  • How many tables are you talking about here? and how often will you use this script, I'm assuming a one time thing. The `null` problem is described [here](http://stackoverflow.com/q/18908309/3664960) and [here](http://stackoverflow.com/q/15905420/3664960) pretty good. – davejal Jan 29 '16 at 16:04
  • It would be a one time thing but there is next to one hundred tables and some contain many columns which is why a script that did not require me to manually enter column names would be much more efficient – Torrm Jan 29 '16 at 16:09
  • @davejal Thank you for the links, they accurately explain what has probably happened. – Torrm Jan 29 '16 at 16:15
  • @all is this something that can really happen in MySQLand? – KarmaEDV Jan 29 '16 at 16:23

1 Answers1

1

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;
Haytem BrB
  • 1,528
  • 3
  • 16
  • 23
  • Is there a way to do this which does not involve manually entering column names? Thanks for your assistance – Torrm Jan 29 '16 at 16:12
  • Column names are dynamically selected here, no manual entering is involved. However this works only for one table, if you need to do this for multiple tables you will need to use the same logic, to select table names dynamically. – Haytem BrB Jan 29 '16 at 16:13
  • Thanks a lot I will give it a go! – Torrm Jan 29 '16 at 16:16
  • Welcome, please notice that "`IS_NULLABLE`='NO' " is commented in the query. As I (and we) didn't understand what are you trying to achieve, so I gave you all elements to write your own script. – Haytem BrB Jan 29 '16 at 16:19
  • Thanks, however I am getting a syntax error on line 1, the problem appears to be with ', ') expecting a closing parenthesis. Do you have any ideas? – Torrm Jan 29 '16 at 16:22