1.提取某数据库中所有用户表的所有列：
SELECT TOP 100 * FROM syscolumns c WHERE c.id IN (SELECT id FROM sysobjects WHERE xtype='u')
2.提取某数据库中所有用户表的所有主键列：
SELECT TOP 100 * FROM syscolumns c WHERE c.id IN (SELECT id FROM sysobjects WHERE xtype='u') and colorder =1
3.提取某数据库中所有用户表的所有非主键列：
SELECT TOP 100 * FROM syscolumns c WHERE c.id IN (SELECT id FROM sysobjects WHERE xtype='u') and colorder <> 1
循环更新方法如下：
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
-----------------------------------------------------------------------------
附：sql系统表syscolumns中 xtype 所有值对应的类型名称
xtype 类型
====== 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
----------------------------------------------------------
以下为mysql方法:
-- =============================================
-- Author: LEAMON
-- Create date: 2011-06-13
-- =============================================
use test;
drop procedure if exists P_UPALLTAB_LEAMON;
CREATE PROCEDURE P_UPALLTAB_LEAMON()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE cTbl varchar(64);
DECLARE cCol varchar(64);
DECLARE cur1 CURSOR FOR
select TABLE_NAME,COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA='test' AND DATA_TYPE in ('varchar','int','BIGINT','decimal') AND ORDINAL_POSITION <> 1 order by TABLE_NAME;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
set @sqlDrop='';
OPEN cur1;
FETCH cur1 INTO cTbl, cCol;/*得到表名及列名*/
WHILE done = 0 DO
set @x=0;
set @sql=concat('select 1 into @x from ',cTbl,' where ',cCol,' in (''-999'',''-95'',''-99'',''-97'') limit 1');
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
if @x<>0 then
set @sqlupdate=concat('update `',cTbl,'` set `',cCol,'` = ''null'' where `' ,cCol,'` in (''-999'',''-95'',''-99'',''-97'') ;');
PREPARE stmt1 FROM @sqlupdate;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
end if ;
set done = 0;
FETCH cur1 INTO cTbl, cCol;
END WHILE;
CLOSE cur1;
END
--执行： CALL P_UPALLTAB_LEAMON( )
--备注：mysql更新为null 的字段，仅varchar为更新为null；其余三个数值字段会自动更新为0。