3

I have a large database w/ a bunch of tables and columns are mixed some allowing NULL while others not allowing NULL..

I just recently decided to STANDARDIZE my methods and USE NULL for all empty fields etc.. therefore i need to set ALL COLUMNS in ALL my tables to allow NULL (except for primaries ofcourse)

I can whip up a php code to loop this , but i was wondering if there's a quick way to do it via SQL?

regards

BrownChiLD
  • 3,545
  • 9
  • 43
  • 61

1 Answers1

0

You can use meta data from system tables to determine your tables, columns, types etc. And then using that, dynamically build a string that contains your UPDATE SQL, with table and column names concatented in to it. This is then executed.

I've recently posted a solution that allowed the OP to search through columns looking for those that contain a particular value. In lieu of anyone providing a more complete answer, this should give you some clues about how to approach this (or at least, what to research). You'd need to either provide table names, or join to them, and then do something similar as this except you'd be checking type, not value (and the dynamic SQL you build would build an update, not a select).

I will be in a position to help you with your specific scenario further in a few hours... If by then you've had no luck with this (or other answers) then I'll provide something more complete then.

EDIT: Just realised you've tagged this as mySql... My solution was for MS SQL Server. The principals should be the same (and hence I'll leave this answer up as i think youll find it usefull), assuming MySql allows you to query its metadata, and execute dynamically generated SQL commands.

SQL Server - Select columns that meet certain conditions?

Community
  • 1
  • 1
Sepster
  • 4,800
  • 20
  • 38
  • thanks.. although this seems more complicated than just coding up a php crawl/loop on all tables/columns.. – BrownChiLD Mar 28 '13 at 08:55
  • @brownChiLD, fair call and i don't disagree. But would you have accepted just "No." as an answer to your question "_i was wondering if there's a quick way to do it via SQL?_" :-). But seriously, remember this question will be looked at by others in future who may get here via eg google, wondering how to do this or something like it in SQL eg because they have some constraint meaning it MUST be done in SQL, so this answer is of value in that regard. And it may be more straight forward to use this approach in mySql than my MSSQL example. Please post back with your PHP solution when completed? – Sepster Mar 28 '13 at 11:12
  • haha .. good point. Anywho I'd whip up PHP for this but I realized it's not a good idea to be using NULLs on all tables... so i'm abandoning the transition haha. But this was a "good to know" thing though.. so thanks all the same! – BrownChiLD Mar 29 '13 at 02:21