findobj

最新推荐文章于 2024-04-17 13:42:36 发布

原创最新推荐文章于 2024-04-17 13:42:36 发布 · 632 阅读

CC 4.0 BY-SA版权

文章标签：

CREATE DEFINER = 'lijiwei'@'192.168.16.33'
PROCEDURE ayst_share.findobj(IN v_object_name VARCHAR(255))
BEGIN

/*
  FUNCTION: 
  该程序的设计目标是，当技术人员输入一个数据库对象（表名、字段名、约束名、索引名、存储过程名等等）时，
  可以智能化显示出用户所输入的对象的相关信息（假如该对象在数据库中存在的话）。以便技术人员快速地获取相关对象
  的定义，方便解决相关问题。

   created date: 2016/9/29 


  */


if (select 1 from information_schema.SCHEMATA where schema_name=v_object_name )
then 
select * from information_schema.SCHEMATA where schema_name=v_object_name    ;
end if ;

if (select 1 from information_schema.ROUTINES where routine_name=v_object_name limit 1) then
select * from information_schema.ROUTINES where routine_name=v_object_name ;
end if ;

if (select 1 from information_schema.tables where table_name=v_object_name limit 1) then
select * from information_schema.tables where table_name=v_object_name ;
end if ;

if (select 1 from information_schema.columns where column_name=v_object_name limit 1 )then
select * from information_schema.columns where column_name=v_object_name ;
  end if ;

if (select 1 from information_schema.table_constraints where constraint_name=v_object_name limit 1) then
select * from information_schema.table_constraints where constraint_name=v_object_name ;
end if ;

if (select 1 from information_schema.TRIGGERS where trigger_name=v_object_name limit 1)then
select * from information_schema.TRIGGERS where trigger_name=v_object_name ;
end if ;

if (select 1 from information_schema.VIEWS where table_name=v_object_name  limit 1)then
select * from information_schema.VIEWS where table_name=v_object_name ;
  end if ;


if (select 1 from information_schema.KEY_COLUMN_USAGE where constraint_name=v_object_name or column_name=v_object_name limit 1 ) then
  select *   from information_schema.KEY_COLUMN_USAGE where constraint_name=v_object_name or column_name=v_object_name ;
end if ;


END