April 9, 2008 at 10:06 am
Hi everyone, I'm hoping you can help me with a stored procedure I'm trying to create which I'm sure is possible but is just beyond my current capabilities. My problem is that I receive files from lots of different sources and I've inherited a few databases that contain '' (i.e nothing) rather than null so when a user runs a select query for 'not null' records containing '' are selected instead of only records populated with data. We currently work around it using >'0' instead of not null but ocaissionally mistakes are made.
Basically most of my tables(about 40) contain between 100 and 300 columns so typing out update queries for each field in each table is going to be a bit of a time consuming task.
What I need the procedure to do is analyse a table and automatically generate update queries for every field contained within that table to find '' and update it to null.
April 10, 2008 at 5:17 am
DECLARE@Schema SYSNAME,
@Table SYSNAME
SELECT@Schema = 'dbo',
@Table = 'Servers'
DECLARE@sql NVARCHAR(MAX)
SET@sql = 'UPDATE ' + QUOTENAME(@Schema) + '.' + QUOTENAME(@Table) + ' SET'
SELECT@sql = @sql + ' ' + QUOTENAME(COLUMN_NAME) + ' = CASE WHEN ' + QUOTENAME(COLUMN_NAME) + ' > ''0'' THEN ' + QUOTENAME(COLUMN_NAME) + ' ELSE NULL END,'
FROM(
SELECTCOLUMN_NAME
FROMINFORMATION_SCHEMA.COLUMNS
WHERETABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'Servers'
) AS d
SET@sql = LEFT(@SQL, LEN(@SQL) - 1)
PRINT@sql
--EXEC@sql
N 56°04'39.16"
E 12°55'05.25"
April 10, 2008 at 5:50 am
Thanks Peso,
That works fine apart from a couple of problems
1.There's an error in the code(I managed to correct it):-
DECLARE @Schema SYSNAME,
@Table SYSNAME
SELECT @Schema = 'dbo',
@Table = 'Servers'
DECLARE @sql NVARCHAR(MAX)
SET @sql = 'UPDATE ' + QUOTENAME(@Schema) + '.' + QUOTENAME(@Table) + ' SET'
SELECT @sql = @sql + ' ' + QUOTENAME(COLUMN_NAME) + ' = CASE WHEN ' + QUOTENAME(COLUMN_NAME) + ' > ''0'' THEN ' + QUOTENAME(COLUMN_NAME) + ' ELSE NULL END,'
FROM (
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo' /* this should be @schema*/
AND TABLE_NAME = 'Servers' /* this should be @table*/
) AS d
SET @sql = LEFT(@SQL, LEN(@SQL) - 1)
PRINT @sql
--EXEC @sql
2. The script doesn't fit into an nvarchar(max) as there are hundreds of fields in the tables.
Is there any way round this?
April 11, 2008 at 10:44 am
You could try something like this...
select 'update '+t.table_name+' set '+column_name+
' = null where 0 = len(ltrim(rtrim('+column_name+')))'
from information_schema.columns c
inner join information_schema.tables t
on c.table_name = t.table_name
where t.table_type = 'BASE TABLE'
It will generate a (rather long) list of individual column updates. You can intersperse begin and end transaction statements to help with logging all these updates.
Steve G.
April 11, 2008 at 2:06 pm
chris.f (4/10/2008)
2. The script doesn't fit into an nvarchar(max) as there are hundreds of fields in the tables.Is there any way round this?
NVARCHAR(MAX) can hold 1 billion characters. I do not think this is the issue here.
N 56°04'39.16"
E 12°55'05.25"
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply