Sql获取表所有列名字段——select * 替换写法,Sqlserver、Oracle、PostgreSQL、Mysql
实际开发中经常用到select * from table,往往需要知道具体的字段,这个时候再去数据库中翻或者查看数据字典比较麻烦。为了方便,自己特意写了一个小函数f_selectall,针对SqlServer、Oracle和PostgreSQL数据库分别写了。
核心思想:先查出每张表的列名字段,然后合并列。
一、SqlServer版本:
create function f_selectall ( @ptablename VARCHAR(50),----表名 如SA_WL_LYTZ @split VARCHAR(50) -----别名,如a ) RETURNS VARCHAR(3000) as BEGIN declare @split1 VARCHAR(50); declare @split2 VARCHAR(50); declare @sreturn VARCHAR(3000); if(@split is null) set @split1= '' ELSE set @split1= @split --select ltrim(RTRIM(@split)) if( @split1 <> '') set @split2 = @split1+'.'; ELSE set @split2 = @split1 ; set @sreturn = (select 'select '+cols+' from '+TABLE_NAME+' '+@split1 from (SELECT DISTINCT TABLE_NAME,STUFF((SELECT ','+@split2+ COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = A.TABLE_NAME FOR XML PATH('')),1,1,'') AS cols FROM INFORMATION_SCHEMA.COLUMNS AS A where table_name = UPPER(@ptablename)) t); RETURN @sreturn END
使用的时候只需:select dbo.f_selectall('TABLE','a') ,即可。
输出结果为:select a.col1,a.col2,a.col3 from TABLE a ;
完美代替 select a.* from table a ;
如果不想用别名a,直接空着即可,即select dbo.f_selectall('TABLE',''),
输出结果为:select col1,col2,col3 from TABLE ;
Sqlserver获取列名语句:
SELECT * FROM INFORMATION_SCHEMA.COLUMNS where table_name = '表名'
二、Oracle版本:
CREATE OR REPLACE FUNCTION F_SELECTALL (v_table IN VARCHAR2, v_prefix IN VARCHAR2) RETURN VARCHAR2 IS Rtsql VARCHAR2 (1000); prefix VARCHAR2 (20); temp VARCHAR2 (20); BEGIN temp:=trim(v_prefix); IF temp is null or temp='' THEN prefix:=''; ELSE prefix:=v_prefix||'.'; END IF; select 'select '||t.col||' from '||UPPER(v_table)||' '||v_prefix into Rtsql from (select wm_concat(t1.col) as col from (select prefix||column_name as col from user_tab_columns where Table_Name=UPPER(v_table)) t1 ) t; Return Rtsql; -----使用: select F_SELECTALL('x_code','a') from dual; END;
使用: select F_SELECTALL('x_code','a') from dual
或:select F_SELECTALL('x_code','') from dual
Oracle获取列名语句:
select * from user_tab_columns where Table_Name= '表名'
三、PostgreSQL版本:
CREATE OR REPLACE FUNCTION "tzwork"."f_selectall"("@v_table" varchar, "@v_prefix" varchar) RETURNS "pg_catalog"."varchar" AS $BODY$ declare "@result" varchar; declare "@prefix" varchar; BEGIN if(length(trim("@v_prefix"))>0) then "@prefix"="@v_prefix"||'.'; else "@prefix"=''; end if; "@result" = ( select 'select '||string_agg("@prefix"||attname ,',')||' from '||relname||' '||"@v_prefix" from (select t.* from ( select c.relname,col_description(a.attrelid,a.attnum)as comment,a.attname ,format_type(a.atttypid,a.atttypmod) as type,attnotnull as notnull from pg_class c,pg_attribute a where c.relname="@v_table" and c.oid=a.attrelid and a.attnum>0 ) t where length(type) > 1)test group by relname ); RETURN "@result"; END$BODY$ LANGUAGE plpgsql VOLATILE COST 100
使用: select F_SELECTALL('x_code','a')
或:select F_SELECTALL('x_code','')
PostgreSQL获取列名语句
select c.relname,col_description(a.attrelid,a.attnum)as comment,a.attname ,format_type(a.atttypid,a.atttypmod) as type,attnotnull as notnull from pg_class c,pg_attribute a where c.relname='表名' and c.oid=a.attrelid and a.attnum>0
四、Mysql版本
DROP FUNCTION IF EXISTS f_selectall; DELIMITER $ CREATE FUNCTION f_selectall(ptablename VARCHAR(50),split VARCHAR(50)) RETURNS VARCHAR(300) BEGIN declare split1 VARCHAR(50); declare split2 VARCHAR(50); declare sreturn VARCHAR(3000); select if(ISNULL(split),'',LTRIM(RTRIM(split))) into split1; select if(split1='',split1,CONCAT(split1,'.')) into split2; select CONCAT('select ',t.col,CONCAT(' from ',ptablename,' ',split1) ) into sreturn from (select GROUP_CONCAT(CONCAT('a.',COLUMN_NAME)) col from information_schema.COLUMNS where table_name = ptablename ) t ; RETURN sreturn; END $ DELIMITER ;
使用: select F_SELECTALL('x_code','a')
或:select F_SELECTALL('x_code','')
Mysql获取列名语句:
select * from information_schema.COLUMNS where table_name = '表名'
自用小工具,工作中可以提高效率。