Translate

Friday, December 13, 2013

TSQL - Dynamic Update

In a database I needed to search every table that began with 'afx' and find any date field that could be set to NULL and replace any dates that were 1/1/1900 with NULL. Also, the fields couldn't be 'date_from' or 'date_to'. I did a TSQL query to search all tables and fieds and found 83 fields that needed to be changed. This was far too many to do idividualy so I wrote a script based off of my original search to do it.

Here is the TSQL I used:

Declare @TN as varchar(200), @CN as varchar(200), @myValue varchar(30), @SQL as nvarchar(1000)
Declare myCursor Cursor For
Select T.Table_Name, C.Column_Name
From INFORMATION_SCHEMA.TABLES T Inner Join INFORMATION_SCHEMA.COLUMNS C
On T.TABLE_NAME like 'afx%' and T.Table_Schema = C.Table_Schema And T.Table_Name = C.Table_Name
and c.Column_Name <> 'date_from' and c.Column_Name <> 'date_to' and C.IS_NULLABLE = 'YES'
And C.Data_Type = 'datetime'
Open myCursor
Fetch Next From myCursor Into @TN, @CN
While @@Fetch_Status <> -1
Begin
Set @SQL = N'Update ' + @TN + ' set ' + @CN + ' = NULL Where [' + @CN + '] = ''1/1/1900'''
Exec sp_executesql @SQL
Fetch Next From myCursor Into @TN, @CN
End
Close myCursor
Deallocate myCursor

No comments:

Post a Comment

Thank you for commenting!