Re: De-Nullifying Columns?
From: Steve Kass (skass_at_drew.edu)
Date: 12/09/04
- Next message: David Gugick: "Re: Stored procedure takes 15 minutes to complete when body finishes in <2 sec"
- Previous message: bill_morgan_3333: "Re: Eliminate Duplicate Records"
- In reply to: localhost: "De-Nullifying Columns?"
- Next in thread: Mike Epprecht (SQL MVP): "Re: De-Nullifying Columns?"
- Reply: Mike Epprecht (SQL MVP): "Re: De-Nullifying Columns?"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 08 Dec 2004 23:17:43 -0500
If it were me, I'd try to convince the higher-ups not to do this. More than
likely, NULL makes more sense than a non-NULL "special" value, and
if any of these nullable columns with NULL values is declared as a foreign
key referencing something else, you simply won't be able to do this without
adding the special value to the referenced table, further making a mess of
the data.
If you really have to do this, you can at least generate the queries you
need this way:
use tempdb
go
create function dbo.Special(
@type sysname
) returns sql_variant as begin
return case @type
when 'datetime' then cast(cast('17540101' as datetime) as sql_variant)
when 'int' then cast(cast(-1 as int) as sql_variant)
-- etc.
end
end
go
-- repeat for each database where you need to do this
select
'UPDATE '+t+' SET
'+c+' = cast(tempdb.dbo.Special('''+d+''') as '+d+')
WHERE '+c+' IS NULL'
FROM (
SELECT
QUOTENAME(C.TABLE_NAME) AS t,
QUOTENAME(C.COLUMN_NAME) AS c,
C.DATA_TYPE AS d
FROM INFORMATION_SCHEMA.COLUMNS AS C
JOIN INFORMATION_SCHEMA.TABLES AS T
ON T.TABLE_NAME = C.TABLE_NAME
AND T.TABLE_TYPE = N'BASE TABLE'
AND C.IS_NULLABLE = 'Yes'
) N
Steve Kass
Drew University
localhost wrote:
>Here's some tricky SQL, looking for any guidance.
>
>I have inherited the support for 4 different MSSQL 2000 databases,
>each with a large number of tables, each table has 1-n columns that
>allow Nulls. I am tasked with getting all of the Null values out.
>
>I am comfortable with writing TSQL to query for all tables from the
>database and the columns from each table (unless someone can point me
>to a canned slick proc that's already out there). Where I need the
>most help is with determining the type in the column and then
>replacing the Null with a special value - for example, all n/var/char
>columns get '---' , all int/bigint/currency get '-1', all datetime get
>1754.01.01, etc.
>
>Any pointers appreciated. Thanks!
>
>
>
>
- Next message: David Gugick: "Re: Stored procedure takes 15 minutes to complete when body finishes in <2 sec"
- Previous message: bill_morgan_3333: "Re: Eliminate Duplicate Records"
- In reply to: localhost: "De-Nullifying Columns?"
- Next in thread: Mike Epprecht (SQL MVP): "Re: De-Nullifying Columns?"
- Reply: Mike Epprecht (SQL MVP): "Re: De-Nullifying Columns?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
