14.3.1 设置所有数字型字段的默认值.sql

SQLServer2000常用函数大全(经典)

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'?'