0

Our database is running on SQL Server 2012. I'd like to null out some unwanted data, rather than just delete the row. I can't null out all the columns in the table because some have "not null" defined. So, rather than manually specifying all the columns by name that allow null values in my update statement, I was hoping to find a way to either filter to it automatically, or try/catch based on whether it is nullable or errors out somehow. That way I can reuse this on any given table. Any ideas?

I can see that sys.columns has a is_nullable and is_computed column that I can query, I just don't know how to wrap it all together in my update statement to say "Null out any column in this table that allows nulls".

Edit: just for example, I was testing with the AdventureWorks2014 database. I can run the below query to see which columns allow nulls and are not computed:

select name as 'column' 
from sys.columns 
where is_nullable = 1 
  and is_computed = 0 
  and object_id = OBJECT_ID('HumanResources.Employee')

Also, I can run the following query to null out some columns that I manually specify:

update [AdventureWorks2014].[HumanResources].[Employee]
set OrganizationNode = NULL

I just don't know how to combine them.

anothermh
  • 9,815
  • 3
  • 33
  • 52
jdsa44
  • 1
  • 2
  • Welcome to stackoverflow. Please take a minute to take the [tour], especially [ask].Welcome to stackoverflow. Please take a minute to take the [tour], especially How to [Ask], and [edit] your question accordingly. Hint: In stackoverflow we like to see what you've tried so far. – Zohar Peled Nov 13 '18 at 19:50
  • 1
    You will query `sys.columns` and from that output you will create your UPDATE statement. Then you will, independently, run that UPDATE statement you have crafted. You can write the UPDATE from the `sys.columns` output by hand, or concatenate the output in your sql to write an `UPDATE` sql statement, or you can have a script write it from the output (or stored procedure). Either way, it will be a two step process. If you get stuck, google "tsql Dynamic SQL sys.columns" and you will run into plenty of examples – JNevill Nov 13 '18 at 19:57
  • 2
    Possible duplicate of [Dynamic SQL to generate column names?](https://stackoverflow.com/questions/2554826/dynamic-sql-to-generate-column-names) – JNevill Nov 13 '18 at 19:58
  • Good day, It seems to me in first glance that your approach is wrong from the basic. First you need to ask yourself why is it so important to change the values into NULL.There are a lot of parameters which should be taken into consideration when designing the right solution. For example there is different if the column is fixed length size or not. You should ask yourself why keep the row if it has no useful data in it. In most cases like this the best approach is use a simple column type bit which used to mark a row that is virtually deleted while the data is in the table – Ronen Ariely Nov 13 '18 at 21:10
  • Hi Ronen. I agree, except that in this case we have to purge some of the data out of the table (not all columns) without breaking the app. If I delete the row altogether the app may barf. If I just flag it as deleted it is still in the db and the legal ramifications remain. :\ – jdsa44 Nov 13 '18 at 22:13
  • Hi JNevill. I think you're probably right. I haven't yet been able to figure out that two step process, but that's probably due to my lack of experience with dynamic sql sys.columns. :\ Thanks for the pointer though. – jdsa44 Nov 14 '18 at 14:10

1 Answers1

0

Worth a try using system view information_schema.columns.

select *
, 'update ' + TABLE_SCHEMA + '.' + TABLE_NAME + ' set ' + COLUMN_NAME + ' = null'
from 
INFORMATION_SCHEMA.COLUMNS where IS_NULLABLE = 'YES'
AhmedHuq
  • 459
  • 1
  • 4
  • 13