0

I am looking for a SQL Server script or query which turns Null values to user defined default values for all columns in a table.

I know we can do column by column but my table contains 300 columns.

Thanks in advance..

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
neoo
  • 141
  • 1
  • 2
  • 8
  • Possible duplicate of [How to Alter Mulitple Columns datatype in SQL Server](http://stackoverflow.com/questions/3465560/how-to-alter-mulitple-columns-datatype-in-sql-server) – EoinS Sep 27 '16 at 19:01
  • I would be looking into why your table has 300 columns. That is almost always a sign of a very poor architecture which is generally the result of improper normalization. I think you need to take a long hard look at your table structure before you spend the effort of adding default values. – Sean Lange Sep 27 '16 at 19:14
  • 1
    He may be stuck with one of those 'enterprise' systems that generate huge tables. The issue here is that he is trying to get us write a huge script for him, which I am afraid is out of scope for a Q&A site like this. At least I would not do that. – ajeh Sep 27 '16 at 19:32

1 Answers1

0

I have re-written my query to set the values to 0.

SELECT 'UPDATE [Your Table] SET ' 
        + c.name + ' = COALESCE(' 
        + c.name + ',''0'')'  FROM sys.columns AS c
JOIN sys.tables AS t ON t.object_id = c.object_id
WHERE t.name = '[Your table]'
Arthur D
  • 592
  • 4
  • 10
  • Thanks for quick reply. The script you gave will make default value for column to NULL. But I want all null values in column to '0' – neoo Sep 27 '16 at 19:16
  • @neoo I have rewritten the query. Is this what you were looking for? – Arthur D Sep 27 '16 at 19:24
  • Hey thanks for the script that's exactly what I am looking for. I am able to generate my required query. – neoo Sep 27 '16 at 19:30