Below code sample will generate scripts for updating nullable columns of all tables in a database that has string NULL with NULL
USE [AdventureWorks2012]
GOUSE [AdventureWorks2012]
SET NOCOUNT ON
DECLARE @query NVARCHAR(MAX),
@table_count INT,
@column_count INT,
@tablename VARCHAR(100),
@Columnname VARCHAR(100),
@i INT = 1,
@j INT = 1
DECLARE @MyTableVar TABLE(Number INT IDENTITY(1,1),
Table_list VARCHAR(200));
DECLARE @MyColumnVar TABLE(Number INT IDENTITY(1,1),
Column_list VARCHAR(200));
INSERT INTO @MyTableVar
SELECT name FROM sys.tables
WHERE TYPE = 'U' AND SCHEMA_NAME(SCHEMA_ID) = 'HumanResources'
SELECT @table_count = MAX(Number) from @MyTableVar
WHILE @i <= @table_count
BEGIN
SELECT @tablename = Table_list FROM @MyTableVar WHERE Number = @i
INSERT @MyColumnVar
SELECT C.name
FROM SYS.columns C
INNER JOIN SYS.tables T ON T.object_id = C.object_id
INNER JOIN SYS.types TY ON TY.user_type_id = C.user_type_id AND TY.system_type_id = C.system_type_id
WHERE SCHEMA_NAME(T.SCHEMA_ID) = 'HumanResources' AND OBJECT_NAME(T.OBJECT_ID) = @tablename AND T.type = 'U'
AND C.is_nullable = 1
ORDER BY C.column_id
SELECT @column_count = MAX(Number) FROM @MyColumnVar
WHILE @j <= @column_count
BEGIN
SELECT @Columnname = Column_list FROM @MyColumnVar WHERE Number = @j
SET @query = 'UPDATE ['+@tablename+'] SET ['+@Columnname+'] = NULL WHERE ['+@Columnname +'] = ''NULL'''
SET @j = @j + 1
PRINT @query
--EXEC (@query)
END
SET @i = @i + 1
END
--Note:Schema wise above code will generate update scripts.
Reference :
http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/31d20dd1-b940-43d8-a834-7a222f472040
http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/a9ac9d4c-cfe7-47e7-86ad-fbc1db482d72
No comments:
Post a Comment