27,579
社区成员
发帖
与我相关
我的任务
分享缺省值批量处理
以前在设计数据库的时候，有好多列没有做默认值处理，导致有很多null值，带来很多不变
有什么批处理办法把那些没有默认值加上默认值呢？
比如：varchar或char等默认值改成空格，数字类型的改成0.
有什么批处理办法把那些没有默认值加上默认值呢？
比如：varchar或char等默认值改成空格，数字类型的改成0.
...全文
请发表友善的回复…
发表回复
qizhengsheng 2009-04-16
- 打赏
- 举报
if object_id(aa)is not null
drop table aa
go
create table aa(a int,b varchar(20))
insert into aa select 1,'22'
insert into aa(a) select 2
insert into aa(a) select 3
insert into aa(b) select 'bb'
declare @t table(id int identity(1,1),tbname varchar(256), colname varchar(256),xtype varchar(20))
insert into @t
select a.name,b.name ,c.name
from sysobjects a
inner join syscolumns b on a.id=b.id
inner join systypes c on b.xusertype = c.xusertype
where a.xtype='u' and c.name in ('varchar','int')
--select * from @t
declare @i int
set @i=1
declare @tbname varchar(256),@colname varchar(256),@xtype varchar(20),@sql nvarchar(4000)
while @i <= (select MAX(id) from @t)
begin
select @tbname=tbname,@colname=colname,@xtype = xtype from @t where id=@i
set @sql = 'alter table '+@tbname+' add constraint ' + 'd_' + @colname + ' default '
if @xtype = 'int'
begin
set @sql = @sql + ' 0 '
end
else if @xtype = 'varchar'
begin
set @sql = @sql + ''''''
end
set @sql = @sql + ' for ' + @colname
exec(@sql)
set @i = @i + 1
end
参考火鸟和
http://topic.csdn.net/u/20090219/22/bbdf717c-c076-4bae-93a6-0e607d10244b.html?seed=1404125293 的方法，
再来问下，假如有的列上有df了，会报错，有什么办法解决呢？
kangbo818 2009-04-16
- 打赏
- 举报
up
qizhengsheng 2009-04-16
- 打赏
- 举报
Perfect
htl258_Tony 2009-04-16
- 打赏
- 举报
if object_id('aa')is not null
drop table aa
go
create table aa(a int,b varchar(20))
insert into aa select 1,'22'
insert into aa(a) select 2
insert into aa(a) select 3
insert into aa(b) select 'bb'
go
select * from aa
declare @t table(id int identity(1,1),tbname varchar(256), colname varchar(256),xtype varchar(20))
insert into @t
select a.name,b.name ,c.name
from sysobjects a
inner join syscolumns b on a.id=b.id
inner join systypes c on b.xusertype = c.xusertype
where a.xtype='u'
and c.name in ('varchar','int')
and b.status<>0x80 --去掉自增列
and not exists --过滤掉原来已存在默认值的列
(select 1
from
(select
(select name from sysobjects where id=c.id) 表名,
(select name from syscolumns where cdefault=a.id) 字段名
from sysobjects b,syscolumns c,syscomments a
where b.xtype='d'
and a.id=b.id
and b.parent_obj=c.id
and a.colid=c.colid
) t
where a.name=t.表名
and b.name=t.字段名)
--select * from @t
declare @i int
set @i=1
declare @tbname varchar(256),@colname varchar(256),@xtype varchar(20),@sql nvarchar(4000)
while @i <= (select MAX(id) from @t)
begin
select @tbname=tbname,@colname=colname,@xtype = xtype from @t where id=@i
set @sql = 'alter table ['+@tbname+'] add constraint ' + 'df_' + replace(@tbname,'-','') +'_'+ replace(@colname,'-','') + ' default '
if @xtype = 'int'
begin
set @sql = @sql + ' 0 '
end
else if @xtype = 'varchar'
begin
set @sql = @sql + ''''''
end
set @sql = @sql + ' for [' + @colname +']'
exec(@sql)
set @i = @i + 1
end根据HEROWANG 的提示,也不能包括标识列,再改一下.目前以这个为主.
qizhengsheng 2009-04-16
- 打赏
- 举报
htl258，影子。我顶。要多少分啊？说！
哈哈
哈哈
幸运的意外 2009-04-16
- 打赏
- 举报
感觉需要分几步进行。首先是吧表结构中添加默认值约束，之后就是对表中已存在的记录更新一下规定的默认值。
yygyogfny 2009-04-16
- 打赏
- 举报
学习~
让你望见影子的墙 2009-04-16
- 打赏
- 举报
mark 25楼的，另外再给一个相对比较简单。
设置所有数字型字段默认值
DECLARE hCForEach CURSOR GLOBAL
FOR
SELECT --前面部分是更新表中数据NULL的为0
N'UPDATE '+QUOTENAME(o.name)
+N' SET '+QUOTENAME(c.name)
+N'=0 WHERE '+QUOTENAME(c.name)
+N' IS NULL
--设置默认值处理
ALTER TABLE '+QUOTENAME(o.name)
+N' ADD DEFAULT 0 FOR '+QUOTENAME(c.name)
FROM sysobjects o,syscolumns c,systypes t
WHERE o.id=c.id
AND OBJECTPROPERTY(o.id,N'IsUserTable')=1
AND c.xusertype=t.xusertype
AND(t.name IN(N'decimal','numeric','real','float') --如果要修改为所有字段的话，这块就不要了
OR t.name LIKE '%money'
OR t.name LIKE '%int')
AND c.status<>0x80 --标识列(IDENTITY)不能设置
AND NOT EXISTS( --已经设置了默认值的不再设置
SELECT * FROM syscomments
WHERE id=c.cdefault)
EXEC sp_MSforeach_Worker @command1=N'?'
天马冰河 2009-04-16
- 打赏
- 举报
SQL code用Sql语句updatetabset字段=值
直接在表设计器里面加上默认值！
或者用sql语句添加默认值
直接在表设计器里面加上默认值！
或者用sql语句添加默认值
htl258_Tony 2009-04-16
- 打赏
- 举报
--Modify.
----htl258(Tony) 2009/04/16 02:53
select
(select name from sysobjects where id=c.id) 表名,
(select name from syscolumns where cdefault=a.id) 字段名,
b.name 默认值名,
a.[text] 默认值
from
syscomments a,
sysobjects b,
syscolumns c
where b.xtype='d'
and a.id=b.id
and b.parent_obj=c.id
and a.colid=c.colid
/*
表名 字段名 默认值名 默认值
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
titles type DF__titles__type__00551192 ('UNDECIDED')
titles pubdate DF__titles__pubdate__023D5A04 (getdate())
jobs job_desc DF__jobs__job_desc__1273C1CD ('New Position - title not formalized yet')
employee job_id DF__employee__job_id__1B0907CE (1)
employee job_lvl DF__employee__job_lv__1CF15040 (10)
employee pub_id DF__employee__pub_id__1DE57479 ('9952')
employee hire_date DF__employee__hire_d__1FCDBCEB (getdate())
authors address d_address ('')
authors au_fname d_au_fname ('')
authors au_lname d_au_lname ('')
authors city d_city ('')
discounts discounttype d_discounttype ('')
employee fname d_fname ('')
employee lname d_lname ('')
authors phone DF__authors__phone__78B3EFCA ('UNKNOWN')
publishers country DF__publisher__count__7D78A4E7 ('USA')
(16 行受影响)
*/htl258_Tony 2009-04-16
- 打赏
- 举报
--以系统自带pubs数据库为例.这个语句可以查出所有默认值的相关信息.
----htl258(Tony) 2009/04/16 01:50
select
(select name from sysobjects where id=c.id) 表名,
c.name 字段名,
b.name 默认值名,
a.[text] 默认值
from
syscomments a,
sysobjects b,
syscolumns c
where b.xtype='d'
and a.id=b.id
and b.parent_obj=c.id
and a.colid=c.colid
/*
表名 字段名 默认值名 默认值
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
titles title_id DF__titles__type__00551192 ('UNDECIDED')
titles title_id DF__titles__pubdate__023D5A04 (getdate())
jobs job_id DF__jobs__job_desc__1273C1CD ('New Position - title not formalized yet')
employee emp_id DF__employee__job_id__1B0907CE (1)
employee emp_id DF__employee__job_lv__1CF15040 (10)
employee emp_id DF__employee__pub_id__1DE57479 ('9952')
employee emp_id DF__employee__hire_d__1FCDBCEB (getdate())
authors au_id DF__authors__phone__78B3EFCA ('UNKNOWN')
publishers pub_id DF__publisher__count__7D78A4E7 ('USA')
(9 行受影响)
*/htl258_Tony 2009-04-16
- 打赏
- 举报
这个语句可以查出哪些表哪些字段存在默认值.(原创)
select (select name from sysobjects where id=c.id) 表名,c.name 字段名 from sysobjects b,syscolumns c,syscomments a where b.xtype='d' and a.id=b.id and b.parent_obj=c.id and a.colid=c.colidhtl258_Tony 2009-04-16
- 打赏
- 举报
增加了排除已存在默认值的对应表名的字段名
htl258_Tony 2009-04-16
- 打赏
- 举报
if object_id(aa)is not null
drop table aa
go
create table aa(a int,b varchar(20))
insert into aa select 1,'22'
insert into aa(a) select 2
insert into aa(a) select 3
insert into aa(b) select 'bb'
declare @t table(id int identity(1,1),tbname varchar(256), colname varchar(256),xtype varchar(20))
insert into @t
select a.name,b.name ,c.name
from sysobjects a
inner join syscolumns b on a.id=b.id
inner join systypes c on b.xusertype = c.xusertype
where a.xtype='u' and c.name in ('varchar','int')
and not exists(select 1 from
(select (select name from sysobjects where id=c.id) 表名,c.name 字段名 from sysobjects b,syscolumns c,syscomments a where b.xtype='d' and a.id=b.id and b.parent_obj=c.id and a.colid=c.colid) t
where a.name=t.表名 and b.name=t.字段名)
select * from sysobjects where id =1748201278
select * from syscolumns where id in(1748201278,1956202019)
declare @i int
set @i=1
declare @tbname varchar(256),@colname varchar(256),@xtype varchar(20),@sql nvarchar(4000)
while @i <= (select MAX(id) from @t)
begin
select @tbname=tbname,@colname=colname,@xtype = xtype from @t where id=@i
set @sql = 'alter table '+@tbname+' add constraint ' + 'd_' + @colname + ' default '
if @xtype = 'int'
begin
set @sql = @sql + ' 0 '
end
else if @xtype = 'varchar'
begin
set @sql = @sql + ''''''
end
set @sql = @sql + ' for ' + @colname
exec(@sql)
set @i = @i + 1
endqizhengsheng 2009-04-15
- 打赏
- 举报
大家幸苦了
加点分是应该的
加点分是应该的
htl258_Tony 2009-04-15
- 打赏
- 举报
[Quote=引用 21 楼 qizhengsheng 的回复:]
ls的好强大
等明天加点分，在等等
[/Quote]楼主好人..
ls的好强大
等明天加点分，在等等
[/Quote]楼主好人..
qizhengsheng 2009-04-15
- 打赏
- 举报
ls的好强大
等明天加点分，在等等
等明天加点分，在等等
yygyogfny 2009-04-15
- 打赏
- 举报
[Quote=引用 18 楼 qizhengsheng 的回复:]
ls好像可行
[/Quote]
呵呵,试了,是可行的
ls好像可行
[/Quote]
呵呵,试了,是可行的
yygyogfny 2009-04-15
- 打赏
- 举报
大概是这样~~~
declare @tbName nvarchar(100)
declare curTB cursor for
select name from sysobjects where type = 'u'
open curTB
fetch next from curtb into @tbName
while @@fetch_status =0
begin
declare cur cursor for
select c.name,t.name from sysobjects o
inner join syscolumns c on o.id = c.id
inner join systypes t on c.xusertype = t.xusertype
where o.type = 'u' and o.name='te'
declare @colname nvarchar(50)
declare @coltype nvarchar(50)
declare @sql nvarchar(4000)
open cur
fetch next from cur into @colname,@coltype
while @@fetch_status =0
begin
set @sql = 'alter table '+ @tbname + ' add constraint ' + 'd_' + @colname + ' default '
if @coltype = 'int'
begin
set @sql = @sql + ' 0 '
end
else if @coltype = 'nvarchar'
begin
set @sql = @sql + ''''''
end
set @sql = @sql + ' for ' + @colname
print(@sql)
fetch next from cur into @colname,@coltype
end
close cur
deallocate cur
fetch next from curtb into @tbName
end
close curtb
deallocate curtb
qizhengsheng 2009-04-15
- 打赏
- 举报
ls好像可行
加载更多回复（17）
