Sqlserver2005迁移至Oracle系列之一:生成表(上)
2009-03-03 13:11:25 来源:WEB开发网 闂傚倸鍊搁崐鎼佸磹閹间礁纾归柟闂寸绾惧綊鏌熼梻瀵割槮缁炬儳缍婇弻鐔兼⒒鐎靛壊妲紒鎯у⒔閹虫捇鈥旈崘顏佸亾閿濆簼绨绘い鎺嬪灪閵囧嫰骞囬姣挎捇鏌熸笟鍨妞ゎ偅绮撳畷鍗炍旈埀顒勭嵁婵犲嫮纾介柛灞捐壘閳ь剛鎳撻~婵嬪Ω閳轰胶鐤呯紓浣割儐椤戞瑩宕ョ€n喗鐓曟い鎰靛亝缁舵氨绱撻崘鈺傜婵﹤顭峰畷鎺戔枎閹搭厽袦婵犵數濮崑鎾绘⒑椤掆偓缁夌敻骞嗛悙鍝勭婵烇綆鍓欐俊鑲╃磼閹邦収娈滈柡灞糕偓鎰佸悑閹肩补鈧尙鏁栧┑鐐村灦閹稿摜绮旈悽绋课﹂柛鏇ㄥ灠閸愨偓濡炪倖鍔﹀鈧繛宀婁邯濮婅櫣绱掑Ο璇茶敿闂佺ǹ娴烽弫璇差嚕婵犳碍鏅插璺猴工瀹撳棝姊虹紒妯哄缂佷焦鎸冲畷鎴﹀箻鐠囧弶宓嶅銈嗘尰缁嬫垶绂嶉悙顒佸弿婵☆垳鍘ф禍楣冩倵濮樼偓瀚�

--生成表的基本信息,包含(列名称、类型、长度、精度;是否为null;字段的默认值(约束);是否是自增列、基数、增量)
select a.name as tabname,b.name as colname,(select name from sys.types where user_type_id = c.system_type_id) as typename,b.max_length as length,b.precision,b.scale,b.is_nullable as isnull
,d.name as defname,d.definition as 'default'
,b.is_identity as isidentity,f.seed_value as seed,f.increment_value as incre
,e.value as describe
into #table_define
from sys.objects a
inner join sys.columns b on a.object_id = b.object_id
inner join sys.types c on b.user_type_id = c.user_type_id
left join sys.default_constraints d on a.object_id = d.parent_object_id and b.column_id = d.parent_column_id
left join sys.extended_properties e on e.class = 1 and a.object_id = e.major_id and b.column_id = e.minor_id
left join sys.identity_columns f on a.object_id = f.object_id and b.column_id = f.column_id
where (a.is_ms_shipped = 0) and (a.type = 'U') and (a.name <> 'sysdiagrams') and (a.name like @tabname) and (a.schema_id = schema_id(@schema))
order by a.name,b.column_id
- ››oracle 中 UPDATE nowait 的使用方法
- ››Oracle ORA-12560解决方法
- ››Oracle 10g RAC 常用维护命令
- ››Oracle如何在ASM中定位文件的分布
- ››sqlserver安装和简单的使用
- ››Oracle的DBMS_RANDOM.STRING 的用法
- ››oracle 外部表导入时间日期类型数据,多字段导入
- ››Oracle中查找重复记录
- ››oracle修改用户登录密码
- ››Oracle创建删除用户、角色、表空间、导入导出等命...
- ››Oracle中登陆时报ORA-28000: the account is lock...
- ››Oracle数据库在配置文件中更改最大连接数
更多精彩
赞助商链接