循环更新该数据库中所有表的所有非主键列为某一值代码,MSSQL代码库,德仔网

代码语言

代码分类

【MSSQL】循环更新该数据库中所有表的所有非主键列为某一值

作者:Dezai.CN / 发布于2011/11/22/ 517

<div>
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: LEAMON
-- Create date: 2011-06-10
-- Description: 循环更新该数据库中所有表的所有非主键列为某一值(存储类型为int/bigint/varchar/decimail)
-- =============================================
CREATE PROCEDURE [dbo].[P_UPALLTAB_LEAMON]
AS
BEGIN
SET NOCOUNT ON;

DECLARE @SQL VARCHAR(2000),
@TABLENAME VARCHAR(20),
@TABLEID INT,
@COLUNM VARCHAR(20)

DECLARE CUR_TABLE CURSOR FOR
SELECT ID,[NAME] FROM SYSOBJECTS WHERE XTYPE='U' ORDER BY [NAME]
OPEN CUR_TABLE
FETCH NEXT FROM CUR_TABLE INTO @TABLEID,@TABLENAME
WHILE @@FETCH_STATUS=0
BEGIN


DECLARE CUR_COLUMN CURSOR FOR
SELECT [NAME] FROM SYSCOLUMNS WHERE XTYPE IN('56','167','106','127') AND COLORDER <> '1' AND ID=@TABLEID
OPEN CUR_COLUMN
FETCH NEXT FROM CUR_COLUMN INTO @COLUNM
WHILE @@FETCH_STATUS=0
BEGIN

SET @SQL='UPDATE '+@TABLENAME+' SET '+@COLUNM+' = NULL WHERE '+@COLUNM+' IN (''-999'',''-95'')'
EXEC (@SQL)

FETCH NEXT FROM CUR_COLUMN INTO @COLUNM
END
CLOSE CUR_COLUMN
DEALLOCATE CUR_COLUMN

FETCH NEXT FROM CUR_TABLE INTO @TABLEID,@TABLENAME
END
CLOSE CUR_TABLE
DEALLOCATE CUR_TABLE

PRINT '更新作业完成'
END

--执行：EXEC P_UPALLTAB_LEAMON
-----------------------------------------------------------------------------
<strong>附：sql系统表syscolumns中 xtype 所有值对应的类型名称</strong>





<strong>
<pre id="best-answer-content" class="reply-text mb10">
xtype    类型</pre>
<pre id="best-answer-content" class="reply-text mb10">
======
34 image
35 text
36 uniqueidentifier
48 tinyint
52 smallint
56 int
58 smalldatetime
59 real
60 money
61 datetime
62 float
98 sql_variant
99 ntext
104 bit
106 decimal
108 numeric
122 smallmoney
127 bigint
165 varbinary
167 varchar
173 binary
175 char
189 timestamp
231 sysname
231 nvarchar
239 nchar</pre>
</strong>



</div>

试试其它关键字

同语言下

可能有用的

Dezai.CN贡献的其它代码(4037)