通过存储过程，查询表的相关表字段信息（sqlserver,mysql,oracle）

三种数据库对应查询相关表字段信息

1.sqlserver

/*
Creater：Kern
Date：2007-08-03
Function：构建查询语句列字段
Modify：1.增加排序处理,使输出结果与表单列结构一致;2.增加换行处理每8个字段换行一次(2007-08-08 by kern)
Remark：辅助开发存储过程
*/
CREATE procedure dbo.CCS
 @Prefix varchar(10)='', --前缀
 @TableName varchar(100), --表名
 @Select int=0   --是否生成Select语句，参数1为生成，0为不生成
AS

declare
  @ColumName varchar(50),
  @ExecStr varchar(2000),
  @CreateStr varchar(2000),
  @NewLine int

Set @CreateStr=''
--Set @ExecStr='select name into ##Tmp from syscolumns where OBJECTPROPERTY(id, N''IsUserTable'') = 1 and id = object_id(N''[dbo].[' + @TableName + ']'')'
Set @ExecStr='select name into ##Tmp from syscolumns where id = object_id(N''[dbo].[' + @TableName + ']'') order by colid'
execute(@ExecStr)

set @NewLine=1

declare ColumsStrs cursor for
  select name from ##Tmp
open ColumsStrs
fetch next from ColumsStrs into @ColumName
while @@fetch_status=0
begin
  if @Prefix<>'' set @ColumName=@Prefix+'.'+@ColumName
  Set @CreateStr=@CreateStr + @ColumName + ','
  if @NewLine=8
  begin
    set @CreateStr=@CreateStr + convert(varchar,0x0A)
    set @NewLine=0
  end
  set @NewLine = @NewLine + 1
  fetch next from ColumsStrs into @ColumName
end
close ColumsStrs
deallocate ColumsStrs

if @Select=1
begin
  if @Prefix<>''
  begin
    Set @CreateStr='select ' + substring(@CreateStr, 1, len(@CreateStr)-1) + convert(varchar,0x0A) + 'from ' + @TableName + ' ' + @Prefix
  end
  else
  begin
    Set @CreateStr='select ' + substring(@CreateStr, 1, len(@CreateStr)-1) + convert(varchar,0x0A) + 'from ' + @TableName
  end
end

print @CreateStr

drop table ##Tmp
GO

2.mysql

DELIMITER $$;

DROP PROCEDURE IF EXISTS `car_libary`.`css`$$

CREATE DEFINER=`fjfdszj`@`localhost` PROCEDURE `css`(Prefix varchar(10) ,TableName varchar(100),Flagselect int)
begin
 declare  ColumName varchar(50);
 declare  ExecStr varchar(2000);
 declare  CreateStr varchar(2000);
 declare  NewLine int;
      DECLARE done INT DEFAULT 0;
      declare ColumsStrs cursor for select column_name  from Tmp;
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
      CREATE  temporary   TABLE  Tmp(column_name varchar(50));
 Set CreateStr='';
 /*Set ExecStr= concat('select column_name into Tmp from columns where table_name = ', TableName, ' order by ordinal_position' ); */
       PREPARE stmt FROM 'insert  into Tmp select column_name  from information_schema.columns where table_name =? order by ordinal_position' ;
      set @ExecStr=TableName;
      execute stmt using @ExecStr;
 set NewLine=1;
        open ColumsStrs;
        REPEAT
  fetch ColumsStrs into ColumName;
                IF NOT done THEN
            if Prefix<>'' then
                                set ColumName=concat(Prefix,'.',ColumName);
                                end if;
      Set CreateStr=concat(CreateStr , ColumName , ',');
             if  NewLine=8 then
         set CreateStr=concat(CreateStr , CONVERT(0x0A  , CHAR));
         set  NewLine=0;
                                end if;
             set  NewLine = NewLine + 1;
             END IF;
        UNTIL  done
    END REPEAT;
 close ColumsStrs;
       /*deallocate ColumsStrs;*/
 if FlagSelect=1 then
    if Prefix<>'' then
       Set CreateStr=concat('select ' ,substring(CreateStr, 1, length(CreateStr)-1) ,  CONVERT(0x0A  , CHAR) , 'from ' ,TableName , ' ', Prefix);
   else
      Set CreateStr=concat('select ' , substring(CreateStr, 1, length(CreateStr)-1) ,  CONVERT(0x0A  , CHAR) , 'from ' , TableName);
          end if;
         end if;
select CreateStr;
drop temporary table  Tmp;
end$$

DELIMITER ;$$

3.oracle的

create or replace
PROCEDURE      css (prefix VARCHAR2,tablename VARCHAR2,flagselect int)
--创建存储过程查询对应表的相关字段信息
--Author:fjfdszj
--Date:2009-08-05
--参数说明:prefix表的别名，tablename数据库的表名，flagselect是否显示select
--例如 begin
--       hunt.css('a','T_SYSTEM_MESSAGE',1);
--     end;
IS
  v_columname varchar2(50):='';    --字段名
  v_returnstr varchar2(2000):='';  --组成的返回字符串
  v_newline int:=1;              --换行处理
  cursor colum_cur(v_tablename varchar2) is  --申明一个游标处理得到对应表的字段信息
     select column_name from user_tab_columns where table_name=v_tablename;
BEGIN
  open colum_cur(tablename);--打开游标
    loop
       fetch colum_cur into v_columname;--取得每一个字段的名称
       exit when colum_cur%notfound;  --当结束时退出
      
         if  prefix<>' ' then
           v_columname:=prefix||'.'||v_columname;--修改字段是否加前缀
         end if;
         v_returnstr:=v_returnstr||v_columname||',';
         --换行处理
         if  v_newline=8 then
            v_returnstr:=v_returnstr||chr(10);
            v_newline:=1;
         end if;
         v_newline:=1+v_newline;
    end loop;
  close colum_cur;
    --处理前缀是否有select等其他一些信息
    if flagselect=1 then
          v_returnstr:=substr(v_returnstr,1,length(v_returnstr)-1);
          if  prefix<>' ' then
              v_returnstr:='select '||v_returnstr||chr(10)||' '||tablename||' '||prefix ;
          else
              v_returnstr:='select '||v_returnstr||chr(10)||' '||tablename ;        
          end if;
    end if;
  --转化为小写字母输出 
  v_returnstr:=lower(v_returnstr);
  --输出提示结果
  dbms_output.put_line(v_returnstr);
END;

 

附注：本人在前辈基础上，即sqlserver版的代码，迁移到mysql和oracle中应用该方法。

 

引导区文件
/etc/grub.conf

title Asianux Server 3 (2.6.18-8.10AX)（仅支持内存小于4G）
        root (hd0,0)
        kernel /vmlinuz-2.6.18-8.10AX ro root=/dev/VolGroup00/LogVol00
        initrd /initrd-2.6.18-8.10AX.img
title Asianux Server 3-PAE (2.6.18-8.10AXPAE)(支持内存大于4G)
        root (hd0,0)
        kernel /vmlinuz-2.6.18-8.10AXPAE ro root=/dev/VolGroup00/LogVol00
        initrd /initrd-2.6.18-8.10AXPAE.img
title Asianux Server 3-xen (2.6.18-8.10AXxen)（虚拟机）
        root (hd0,0)
        kernel /xen.gz-2.6.18-8.10AX
        module /vmlinuz-2.6.18-8.10AXxen ro root=/dev/VolGroup00/LogVol00
        module /initrd-2.6.18-8.10AXxen.img