mysql所有表查询
所有表和字段:
获取以下信息
Field :字段名
Type:字段类型
Collation:字符集 (mysql 5.0以上有)
Null :是否可以为NULL
Key:索引(PRI,unique,index)
Default:缺省值
Extra:额外(是否 auto_increment)
Privileges:权限
Comment:备注(mysql 5.0以上有)
SELECT t.TABLE_NAME,t.COLUMN_NAME FROM information_schema.columns t WHERE TABLE_SCHEMA='dayangben' AND DATA_TYPE='varchar'
字符所在表:
查询所有表和字段,不包括视图:
SELECT t.TABLE_NAME,t.COLUMN_NAME FROM information_schema.columns t WHERE t.TABLE_SCHEMA='dayangben' AND t.DATA_TYPE='varchar' and t.TABLE_NAME !='lbg'
AND NOT EXISTS(
SELECT 1 from information_schema.tables where table_schema='dayangben' and table_type='view' AND TABLE_NAME=t.TABLE_NAME)
升级版:
call p_findstr('git_jeeshop','测试1',@a);
select @a;
DROP procedure IF EXISTS p_findstr;
create procedure p_findstr(in databasenameVARCHAR(30),IN findstrVARCHAR(30),OUT sqlstrVARCHAR(30000))
begin
/*定义游标遍历时,作为判断是否遍历完全部记录的标记*/
declare no_more,recordcountinteger DEFAULT 0;
DECLARE table_name,column_name,v_sqlVARCHAR(300);
DECLARE t_sqlVARCHAR(30000)DEFAULT '';
/*--定义游标名字为C_RESULT */
DECLARE C_RESULTCURSOR FOR
SELECT t.TABLE_NAME,t.COLUMN_NAMEFROM information_schema.columns tWHERE t.TABLE_SCHEMA=databasenameAND t.DATA_TYPE='varchar'
AND NOT EXISTS(SELECT 1 from information_schema.tables v where v.table_schema=databasenameand v.table_type='view' AND v.TABLE_NAME=t.TABLE_NAME);
/*--声明当游标遍历完全部记录后将标志变量置成某个值*/
DECLARE CONTINUE HANDLERFOR NOT FOUND SET no_more=1;
# deallocate prepare stmt; /*--释放掉预处理段*/
OPEN C_RESULT; /*--打开之前定义的游标*/
REPEAT /* --循环语句的关键词*/
FETCH C_RESULTINTO table_name,column_name; /*--取出每条记录并赋值给相关变量,注意顺序*/
if LENGTH(table_name)>=1 then
set v_sql= concat('select count(1) into @recordcount from `',table_name,'` where ',column_name,' like''%',findstr,'%''');
set @v_sql=v_sql;
prepare stmt from @v_sql;
EXECUTE stmt;
deallocate prepare stmt;
#获取动态SQL语句返回值
set recordcount = @recordcount;
IF recordcount>0 THEN
# set v_sql= concat('insert into ',sumtable,'(table_name,column_name) values(''',table_name,''',''',column_name,''')');
set v_sql= concat('select ',column_name,' from `',table_name,'`;\n');
set t_sql=concat(t_sql,v_sql);
# prepare stmta from @v_sql2; /*--预处理需要执行的动态SQL,其中stmt是一个变量*/
# EXECUTE stmta; #执行SQL语句
#deallocate prepare stmta;
end if;
end if;
UNTIL no_more=1 END REPEAT; /* --循环语句结束*/
CLOSE C_RESULT;
select t_sql;
set sqlstr=t_sql; /* --关闭游标*/
END
文章来源: blog.csdn.net,作者:网奇,版权归原作者所有,如需转载,请联系作者。
原文链接:blog.csdn.net/jacke121/article/details/54486539
- 点赞
- 收藏
- 关注作者
评论(0)