56,679
社区成员
发帖
与我相关
我的任务
分享
DECLARE @what varchar(800)
SET @what='900002' --要搜索的字符串
DECLARE @sql varchar(8000)
DECLARE TableCursor CURSOR LOCAL FOR
SELECT sql='IF EXISTS ( SELECT 1 FROM ['+o.name+'] WHERE ['+c.name+'] LIKE ''%'+@what+'%'' ) PRINT ''所在的表及字段:['+o.name+'].['+c.name+']'''
FROM syscolumns c JOIN sysobjects o ON c.id=o.id
-- 175=char 56=int 可以查 select * from sys.types
WHERE o.xtype='U' AND c.status>=0 AND c.xusertype IN (175, 239, 231, 167 )
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @sql
WHILE @@FETCH_STATUS=0
BEGIN
EXEC( @sql )
FETCH NEXT FROM TableCursor INTO @sql
END
CLOSE TableCursor
-- 删除游标引用
DEALLOCATE TableCursor
DECLARE @what varchar(800)
SET @what='900002' -- 要搜索的字符串
DECLARE @sql varchar(8000)
DECLARE TableCursor CURSOR LOCAL FOR
SELECT sql='IF EXISTS ( SELECT 1 FROM ['+o.name+'] WHERE ['+c.name+'] LIKE ''%'+@what+'%'' ) PRINT ''所在的表及字段:['+o.name+'].['+c.name+']'''
FROM syscolumns c JOIN sysobjects o ON c.id=o.id
-- 175=char 56=int 可以查 select * from sys.types
WHERE o.xtype='U' AND c.status>=0 AND c.xusertype IN (175, 239, 231, 167 )
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @sql
WHILE @@FETCH_STATUS=0
BEGIN
EXEC( @sql )
FETCH NEXT FROM TableCursor INTO @sql
END
CLOSE TableCursor
-- 删除游标引用
DEALLOCATE TableCursor
报错信息:
[SQL] DECLARE @what varchar(800)
SET @what='900002' -- 要搜索的字符串
DECLARE @sql varchar(8000)
DECLARE TableCursor CURSOR LOCAL FOR
SELECT sql='IF EXISTS ( SELECT 1 FROM ['+o.name+'] WHERE ['+c.name+'] LIKE ''%'+@what+'%'' ) PRINT ''所在的表及字段:['+o.name+'].['+c.name+']'''
FROM syscolumns c JOIN sysobjects o ON c.id=o.id
-- 175=char 56=int 可以查 select * from sys.types
WHERE o.xtype='U' AND c.status>=0 AND c.xusertype IN (175, 239, 231, 167 )
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @sql
WHILE @@FETCH_STATUS=0
BEGIN
EXEC( @sql )
FETCH NEXT FROM TableCursor INTO @sql
END
CLOSE TableCursor
-- 删除游标引用
DEALLOCATE TableCursor
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE @what varchar(800)
SET @what='900002' -- 要搜索的字符串
DECLA' at line 1
DELIMITER $$
DROP PROCEDURE IF EXISTS `ProTest`$$
CREATE PROCEDURE `ProTest`(in what varchar(500))
begin
declare NewID int;-- 游标给值
declare $sql varchar(10000); -- SQL语句
declare fetchSeqOk int;
/*创建游标*/
declare ProTest_Cursor cursor for
-- 此处写游标查询语句
select ID from test;
declare continue handler for not found set fetchSeqOk = 1;
open ProTest_Cursor; -- 打开游标
repeat
fetch ProTest_Cursor into NewID;-- 游标循环
if fetchSeqOk <> 1 then
-- SQL 语句拼接
set $sql = concat( 'select * from test where iname like %',what,'%');-- 语句拼接, concat:语句拼接,mysql不能用加号,加号相当于数值相加。
-- 执行语句
set @sql = $sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
end if;
until fetchSeqOk = 1
end repeat;
close ProTest_Cursor; -- 关闭游标
end$$
DELIMITER ;