/****** Object: StoredProcedure [dbo].[GET_TableScript_ORACLE] Script Date: 06/15/2012 13:07:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*==============================================================
名称: GET_TableScript_ORACLE
功能: 生成单个表的ORACLE脚本
创建：2010年10月22日
参数：@DBNAME --数据库名称
@TBNAME --表名
@SQL --输出脚本
==============================================================*/
ALTER PROCEDURE [dbo].[GET_TableScript_ORACLE] (@DBNAME varchar(40),@TBNAME VARCHAR(100),@SQL VARCHAR(max) OUTPUT) AS
BEGIN
declare @table_script nvarchar(max) --建表的脚本
declare @index_script nvarchar(max) --索引的脚本
declare @default_script nvarchar(max) --默认值的脚本
declare @check_script nvarchar(max) --check约束的脚本
declare @sql_cmd nvarchar(max) --动态SQL命令
declare @err_info varchar(200)
set @tbname = UPPER(@tbname);
if OBJECT_ID(@DBNAME+\'.dbo.\'+@TBNAME) is null
BEGIN
set @err_info=\'对象:\'+@DBNAME+\'.dbo.\'+@TBNAME+\'不存在!\'
raiserror(@err_info,16,1)
return
END
----------------------生成创建表脚本----------------------------
--1.添加算定义字段
set @table_script = \'CREATE TABLE \'+@TBNAME+\'
(\'+char(13)+char(10);
--添加表中的其它字段
set @sql_cmd=N\'
use \'+@DBNAME+\'
set @table_script=\'\'\'\'
select @table_script=@table_script+
+t.NAME+\'\' \'\'
+(case when t.name=\'\'GENIUS_UID\'\' then \'\'number(20,0)\'\'
when t.xusertype in (175,62,239,59,122,165,173) then c.oracle+\'\' (\'\'+convert(varchar(30),isnull(t.prec,\'\'\'\'))+\'\')\'\'
when t.xusertype in (231) and t.length=-1 then \'\'BLOB\'\'
when t.xusertype in (231) and t.length<>-1 then c.oracle+\'\' (\'\'+convert(varchar(30),isnull(t.prec,\'\'\'\'))+\'\')\'\'
when t.xusertype in (167) and t.length=-1 then \'\'BLOB\'\'
when t.xusertype in (167) and t.length<>-1 then c.oracle+\'\' (\'\'+convert(varchar(30),isnull(t.prec,\'\'\'\'))+\'\')\'\'
when t.xusertype in (106,108) then c.oracle+\'\' (\'\'+convert(varchar(30),isnull(t.prec,\'\'\'\'))+\'\',\'\'+convert(varchar(30),isnull(t.scale,\'\'\'\'))+\'\')\'\'
when t.xusertype in (48,52,56,104,127,189) then \'\'number(\'\'+c.ora_prec+\'\',0)\'\'
else c.oracle
END)
+(case when t.isnullable=1 then \'\' null\'\' else \'\' not null \'\'end)
+(case when COLUMNPROPERTY(t.ID, t.NAME, \'\'ISIDENTITY\'\')=1 then \'\' identity\'\' else \'\'\'\' end)
+\'\',\'\'+char(13)+char(10)
from syscolumns t join systypes p on t.xusertype = p.xusertype
inner join pubdb..DB_TYPES c on p.xtype=c.user_type_id
where t.\'\'+@TBNAME+\'\'\')
ORDER BY t.COLID;
\'
EXEc sp_executesql @sql_cmd,N\'@table_script varchar(max) output\',@sql_cmd output
set @table_script=@table_script+@sql_cmd
IF len(@table_script)>0
set @table_script=substring(@table_script,1,len(@table_script)-3)+char(13)+char(10)
+\');\'+char(13)+char(10)
+char(13)+char(10)+char(13)+char(10)
--------------------生成索引脚本---------------------------------------
set @index_script=\'\'
set @sql_cmd=N\'
use \'+@DBNAME+\'
declare @ct int
declare @indid int --当前索引ID
declare @p_indid int --前一个索引ID
select @indid=-1, @p_indid=0,@ct=0 --初始化，以后用@indid和@p_indid判断是否索引ID发生变化
set @index_script=\'\'\'\'
select @indid=INDID
,@index_script=@index_script
+(case when @indid<>@p_indid and @ct>0 then \'\');\'\'+char(13)+char(10)+char(13)+char(10) else \'\'\'\' end)
+(case when @indid<>@p_indid and UNIQ=\'\'PRIMARY KEY\'\'
then \'\'ALTER TABLE \'\'+TABNAME+\'\' ADD CONSTRAINT \'\'+name+\'\' PRIMARY KEY \'\'+char(13)+char(10)
+\'\'(\'\'+char(13)+char(10)
+\'\' \'\'+COLNAME+char(13)+char(10)
when @indid<>@p_indid and UNIQ=\'\'UNIQUE\'\'
then \'\'ALTER TABLE \'\'+TABNAME+\'\' ADD CONSTRAINT \'\'+name+\'\' UNIQUE \'\'+char(13)+char(10)
+\'\'(\'\'+char(13)+char(10)
+\'\' \'\'+COLNAME+char(13)+char(10)
when @indid<>@p_indid and UNIQ=\'\'INDEX\'\'
then \'\'CREATE INDEX \'\'+name+\'\' ON \'\'+TABNAME+char(13)+char(10)
+\'\'(\'\'+char(13)+char(10)
+\'\' \'\'+COLNAME+char(13)+char(10)
when @indid=@p_indid
then \'\' ,\'\'+COLNAME+char(13)+char(10)
END)
,@ct=@ct+1
,@p_indid=@indid
from
(
SELECT A.INDID,B.KEYNO
,NAME,(SELECT NAME FROM SYSOBJECTS WHERE ID=A.ID) AS TABNAME,
(SELECT NAME FROM SYSCOLUMNS WHERE ID=B.ID AND COLID=B.COLID) AS COLNAME,
(CASE WHEN EXISTS(SELECT 1 FROM SYSOBJECTS WHERE NAME=A.NAME AND XTYPE=\'\'UQ\'\') THEN \'\'UNIQUE\'\'
WHEN EXISTS(SELECT 1 FROM SYSOBJECTS WHERE NAME=A.NAME AND XTYPE=\'\'PK\'\') THEN \'\'PRIMARY KEY\'\'
ELSE \'\'INDEX\'\' END) AS UNIQ,
(CASE WHEN A.IND\'CLUSTERED\'\' WHEN A.INDID>1 THEN \'\'NONCLUSTERED\'\' END) AS CLUSTER
FROM SYSINDEXES A INNER JOIN SYSINDEXKEYS B ON A.INDID=B.INDID AND A.ID=B.ID
WHERE A.\'\'+@TBNAME+\'\'\') and a.indid<>0
) t
ORDER BY INDID,KEYNO\'
EXEc sp_executesql @sql_cmd,N\'@index_script varchar(max) output\',@sql_cmd output
set @index_script=@sql_cmd
IF len(@index_script)>0
set @index_script=@index_script+\');\'+char(13)+char(10)+char(13)+char(10)
--生成默认值约束
set @sql_cmd=\'
use \'+@DBNAME+\'
set @default_script=\'\'\'\'
SELECT @default_script=@default_script
+\'\'ALTER TABLE \'\'+OBJECT_NAME(O.PARENT_OBJ)
+\'\' MODIFY \'\'+C.NAME+\'\' default \'\'+replace(t.text,\'\'(getdate())\'\',\'\'(sysdate)\'\')+\'\';\'\'+char(13)+char(10)
+char(13)+char(10)
FROM SYSOBJECTS O INNER JOIN SYSCOMMENTS T ON O.ID=T.ID
INNER JOIN SYSCOLUMNS C ON O.PARENT_OBJ=C.ID AND C.CDEFAULT=T.ID
WHERE O.XTYPE=\'\'D\'\' AND O.PARENT_OBJ=OBJECT_ID(\'\'\'+@TBNAME+\'\'\')\'
EXEc sp_executesql @sql_cmd,N\'@default_script varchar(max) output\',@sql_cmd output
set @default_script=@sql_cmd+char(13)+char(10)
set @SQL=@table_script+@index_script+@default_script
declare @len int,@n int
set @len=LEN(@SQL)
set @n=0
while(@len>0)
BEGIN
PRINT(substring(@SQL,@n*4000+1,4000));
set @n=@n+1
set @len=@len-4000;
END
END;