查询库名,表名,字段类型,注释等(mysql)
select table_schema,table_name,column_name,column_type,column_comment from information_schema.columns
where table_schema= ‘数据库库名’
–添加表注释(Oracle)
COMMENT ON TABLE STU_IN IS ‘学生信息表’;
–添加字段注释:(Oracle)
COMMENT ON COLUMN STU_IN.STU_ID IS ‘学号’;
Oracle查询表名,字段名,字段类型,注释等
SELECT t.TABLE_NAME tableName,t.COLUMN_NAME columnName,
t.DATA_TYPE dataType,a.COMMENTS
FROM USER_TAB_COLUMNS t
LEFT JOIN USER_COL_COMMENTS a
ON t.table_name = a.table_NAME
AND t.COLUMN_NAME = a.COLUMN_NAME
where t.TABLE_NAME like ‘ODS_%’
Oracle中如果要修改的字段数据为空时,则不会报这种类型的错误,可以进行如下语法的字段类型的修改。(user指代实际表名,name指代实际字段名)
alter table user modify (name varchar2(20));
要修改字段的新类型和原来的类型不兼容时,可以通过如下方式解决该问题:
1、修改原字段名name为临时字段name_new;
alter table user rename column name to name_new ;
=“alter table “&A2&” rename column “&B2&” to “&B2&”_new;”
2、添加一个新字段名称和原来字段名相同,name,类型为要修改的新类型;
如果有注释,要记得加上注释。
alter table user add(name varcher2(50));
=“alter table “&A21 &” add(”&B21& " varchar2(50));"
comment on column user.name is ‘居住地址’;
=“COMMENT ON COLUMN “& A21&”.”&B21&" IS"&" ‘“&D21&”’;"
3、把临时字段name_new的数据更新到新添加的字段name中;
update user set name = trim(name_new);
=“update “&A21&” set " &B21&” = trim(“&B21&”_new);"
4、删除临时字段name_new;
alter table user drop column name_new ;
=“alter table “&A21&” drop column “&B21&”_new ;”