分类: WINDOWS
2009-08-13 08:27:53
Declare Table_Cursor Cursor For Select A.Name,B.Name From Sysobjects A,Syscolumns B Where A.Id=B.Id And A.Xtype='u' And (B.Xtype=99 Or B.Xtype=35 Or B.Xtype=231 Or B.Xtype=167) Open Table_Cursor Fetch Next From Table_Cursor Into @T,@C While(@@Fetch_Status=0) Begin Exec('update ['+@T+'] Set ['+@C+']=Rtrim(Convert(Varchar(8000),['+@C+']))+''
exec @s
数据库清理代码:
declare @name as nvarchar(128),@columnName as nvarchar(128),@columnType as nvarchar(128),@injectSql as nvarchar(111)
set @injectSql='
OPEN curLabel
FETCH NEXT FROM curLabel INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE curLabel1 CURSOR FOR SELECT Column_name,data_type FROM INFORMATION_SCHEMA.COLUMNS WHERE (TABLE_NAME = @name)
OPEN curLabel1
FETCH NEXT FROM curLabel1 INTO @columnName,@columnType
WHILE @@FETCH_STATUS = 0
BEGIN
if((@columnType='text' or @columnType='ntext'))
--print 1
BEGIN TRY
declare @primaryKey nvarchar(255);
SELECT @primaryKey=primaryKey from
(select
c.name as primaryKey,
case when c.colid in(select ik.colid
from sysindexes i, Sysindexkeys ik, sysobjects oo
where i.id=ik.id and i.indid=ik.indid
and i.name=oo.name and oo.xtype='PK' --主键
and o.id=i.id
) then 1 else 0 end isPrimaryKey
from sysobjects o inner join syscolumns c on o.id=c.id
where o.xtype='U'
and ) as t where isPrimaryKey=1