October 9, 2003 at 12:45 am
i have got about 80 tables and going to increase in future,but acc to project req.
i have to put some character in place of null in columns which contains null
so plz suggest me the way where i can send table as a parameter and which check the columns and update it.
or send some other soln.
thanks
October 9, 2003 at 2:26 am
you could write a stored proecure that uses the the syscolumns table to get a list of columns for a particular table
get the results of this query into a cursor and loop through it....
for each column in the loop you need to do a update query, somthing like...
update
set [column name value from cursor] = 'your value for nulls' where [column name value from cursor] is null
October 9, 2003 at 2:39 am
It might also be a good idea to change DDL for those columns to NOT NULL, maybe with a DEFAULT <blabla>
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
October 10, 2003 at 8:44 am
Below is an example. You can make it faster by moving the alter table to an outer cursor and the column to an inner cursor, so the outer cursor handles the table name / triggers and the inner cursor handles the column selection / updates.
USE NORTHWIND
go
SET NOCOUNT ON
GO
Declare @TableName Varchar(255),
@ColumnNameVarchar(255)
DECLARE TableCursor CURSOR FOR
select object_name(sc.id) as TableName, sc.name as ColumnName
from syscolumns as sc
where objectproperty(sc.id, 'isusertable') = 1
and sc.xtype in (231, 239, 167, 175) -- select * from systypes
-- to see datatypes. This only does replacements on
-- Varchar, Char, NChar, and NVarchar Columns
ORDER BY TableName, ColumnName
OPEN TableCursor
FETCH NEXT FROM TableCursor into @TableName, @ColumnName
WHILE @@FETCH_STATUS = 0
BEGIN
Print 'Processing: ' + @TableName + ' Column: ' + @ColumnName
-- Replace PlaceValueHere with whatever you would like the null values to be replaced with
-- or leave as '''' for empty set
Execute('ALTER TABLE ' + @TableName + ' DISABLE TRIGGER ALL
UPDATE ' + @TableName + '
SET ' + @ColumnName + ' = ''PlaceValueHere''
where ' + @ColumnName + ' is null
ALTER TABLE ' + @TableName + ' ENABLE TRIGGER ALL')
FETCH NEXT FROM TableCursor into @TableName, @ColumnName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply