January 5, 2012 at 9:44 pm
Hello There,
Im using the following to retreive all the tables and columns from a database
SELECT s.name AS SchemaName, t.name AS TableName, c.name AS ColumnName, sys.types.name AS DataType
FROM sys.columns AS c INNER JOIN
sys.tables AS t ON c.object_id = t.object_id INNER JOIN
sys.schemas AS s ON t.schema_id = s.schema_id INNER JOIN
sys.types ON c.system_type_id = sys.types.system_type_id
WHERE (c.is_identity = 0)
ORDER BY TableName
I then want to be able to LOOP through the result set and for every column do an update on the actual Table and columns in that table
Something like.....
Update {TableName}
Set {ColumnName} =
Case DataType 'Int' then 0 else Null end,
Case DataType 'varchar' then '' else Null end,
Case DataType 'tinyInt' then 0 else Null end.......
Where {ColumnName} is NULL
Im trying to find a global way to update all database tables in a database and update any records that have a null value in a column.
I can do this in a .net application but was hoping there is a way to do it using SQL script.
Many thanks
SQL 2005/2008/2008R2
January 5, 2012 at 11:02 pm
Hi,
I hope this could help you..
DECLARE @TabName VARCHAR(500)
DECLARE @ColName VARCHAR(500)
DECLARE @DataType VARCHAR(100)
DECLARE @DySQL NVARCHAR(MAX)
DECLARE CUR_UPDATE_TABLE CURSOR FAST_FORWARD FOR
SELECT s.name,t.name,c.name, sys.types.name FROM sys.columns AS c
INNER JOIN sys.tables AS t ON c.object_id = t.object_id
INNER JOIN sys.schemas AS s ON t.schema_id = s.schema_id
INNER JOIN sys.types ON c.system_type_id = sys.types.system_type_id
WHERE (c.is_identity = 0)
OPEN CUR_UPDATE_TABLE
FETCH NEXT FROM CUR_UPDATE_TABLE INTO @SchName,@TabName,@ColName,@DataType
WHILE @@FETCH_STATUS =0
BEGIN
SET @DySQL=''
IF (@DataType='int' OR @DataType='tinyint')
BEGIN
SET @DySQL='UPDATE '+@SchName+'.'+@TabName+' SET '+@ColName+'=0 WHERE '+@ColName+' IS NULL'
END
ELSE IF @DataType='varchar'
BEGIN
SET @DySQL='UPDATE '+@SchName+'.'+@TabName+' SET '+@ColName+'='''' WHERE '+@ColName+' IS NULL'
END
EXEC (@DySQL)
FETCH NEXT FROM CUR_UPDATE_TABLE INTO @SchName,@TabName,@ColName,@DataType
END
CLOSE CUR_UPDATE_TABLE
DEALLOCATE CUR_UPDATE_TABLE
January 5, 2012 at 11:19 pm
Thanks very much for your help...Ill give it a try
Cheers
Andre
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply