SQLSERVER存储过程查找所有数据表中某列存在空值

ALTER PROCEDURE [MDM].[PRO_CHK_COLUMNS_VALUES]

AS

BEGIN

SET NOCOUNT ON;

DECLARE @list_column_name nvarchar(50)

DECLARE @sql nvarchar(1000)

DECLARE @StrSQL VARCHAR(1000)

　DECLARE @count int

DECLARE @name nvarchar(200)

DECLARE cur_table CURSOR FOR --定义游标

SELECT (table_schema+'.'+table_name) as tableName FROM INFORMATION_SCHEMA.TABLES where table_schema !='QHBAC'

--OPEN cur_table

fetch next from cur_table into @name --把提取操作的列数据放到局部变量中 

PRINT @name

while(@@fetch_status=0) --游标读取下一条数据是否成功 

BEGIN

　　declare cur cursor for --定义游标

    select name from sys.columns  where object_id=OBJECT_ID(''+@name+'')

    open cur--打开游标  

fetch next from cur into @list_column_name --把提取操作的列数据放到局部变量中  

while(@@fetch_status=0) --游标读取下一条数据是否成功  

begin

   SET @sql='select @count = COUNT(1) from '+@name +' where '+@list_column_name +' like ''% %'''

   exec sp_executesql @sql,N'@count nvarchar(50) out ',@count out 

if @count > 0

begin 

SET @StrSQL='select ' +@list_column_name+ ' from '+@name +' where '+@list_column_name +' like ''% %'''  

PRINT @StrSQL

BREAK

--SET @StrSQL='UPDATE ' +@name +' SET ' + @list_column_name + ' = '+ 'ltrim(rtrim(' + @list_column_name + '))'

--EXEC(@StrSQL)

end

--读取下一条数据记录放到局部变量中，变量的数目必须与游标选择列表中的列的数目一致

  fetch next from cur into  @list_column_name   

end    

close cur--关闭游标  

deallocate cur--删除游标 

--读取下一条数据记录放到局部变量中，变量的数目必须与游标选择列表中的列的数目一致

fetch next from cur_table into  @name  

END

close cur_table--关闭游标  

deallocate cur_table--删除游标

END

转载于:https://www.cnblogs.com/liuzhuqing/p/7480130.html