null设置.sql

很好的sql 文档,可能邦你成为sql 高手

/*--NULL设置

	设置当前数据库中,所有不允许NULL的列为允许NULL

--邹建 2005.05(引用请保留此信息)--*/
	

DECLARE hCForEach CURSOR GLOBAL
FOR
select --o.name,c.name,t.name,
	N'alter table '+quotename(o.name)
	+N' alter column '+quotename(c.name)
	+N' '+quotename(t.name)
	+CASE 
		WHEN t.name IN (N'decimal',N'numeric')
		THEN N'('+CAST(c.prec as varchar)+N','+CAST(c.scale as varchar)+N')'
		WHEN t.name IN (N'float')
			OR t.name like N'%char'
			OR t.name like N'%binary'
		THEN N'('+CAST(c.prec as varchar)+N')'
		ELSE N'' END
	+N' NULL'
from sysobjects o,syscolumns c,systypes t
where o.xtype='U' and o.status>=0
	and o.id=c.id
	and c.xusertype=t.xusertype
	and t.name not in(N'text',N'image',N'ntext',N'timestamp')
	and columnproperty(c.id,c.name,N'AllowsNull')=0
	and columnproperty(c.id,c.name,N'IsComputed')=0
	and columnproperty(c.id,c.name,N'IsIdentity')=0
	and columnproperty(c.id,c.name,N'IsRowGuidCol')=0
	and not exists(
		select * from sysobjects 
		where parent_obj=o.id 
			and xtype='pk' 
			and name in (
				select name from sysindexes idx,sysindexkeys idxk
				where idx.indid not in(0,255)
					and idx.id=idxk.id and idx.indid=idxk.indid
					and idxk.id=c.id and idxk.colid=c.colid))
EXEC sp_msforeach_worker '?'