mssql oracle 增删改（列，表，索引，主键，临时表）表信息

增加标识列
ALTER TABLE dbo.XXXX ADD ID int IDENTITY(1,1)
新增列
alter table "xxx".xxx add ID int
字段改名
exec sp_rename '表名.[字段原名]','字段新名','column' 
exec sp_rename '"xxx".xxx.ID','ID_','column'
修改字段名称：
alter table "9959".tbcardserviceitems rename column enddays to itemid;
增删改字段名：
alter table "000".TBS add (y number(2));
ALTER TABLE "000".TBS MODIFY(Y NUMBER(3));
ALTER TABLE "000".TBS DROP COLUMN y;
--删除列
alter table "xxx".xxx drop column ID
--======================================================
修改表名：
alter table "xxx".T01 rename to T01_
--===================================================
--删除主建约束
alter table "xxx".xxx drop PK_xxxx
--删除外键：
alter table "xxx".xxx drop constraint FK_CATTODEP_CATE
--删除主键：
ALTER TABLE "000".TBS  DROP PRIMARY KEY CASCADE;
删除索引：
DROP INDEX "000"."IDX_xxx";
删除约束：
alter table "000".xxx drop constraint PK_xxx;
ALTER TABLE "000".TBS ADD CONSTRAINT TBS_C01  CHECK (nvl(PURCHPRICE,-1)>=0);
alter table "000".TBS drop constraint TBS_C01

增加主键：
alter table "000".TBS add constraint PK_TBS primary key (CategoryItemCode, IsLease)
--===================================================== 
增加索引：
CREATE UNIQUE CLUSTERED INDEX [PK_TB200907_ABSUBBACK_d] ON #qcsj ( [DeptCode] , [CounterCode]);
CREATE INDEX Idx1 ON #qcsj ( [DeptCode] );
create unique index "000".PK_TBS on "000".TBS( QUERYCODE,DATASETCODE,PRECONDITIONID)
alter table "000".TBS add ( constraint PK_TBS primary key ( QUERYCODE,DATASETCODE,PRECONDITIONID) using index "000".PK_TBS);
--======================================================== 
 --sqlserver
select --o.name,c.name,t.name,
 N'alter table '+quotename(o.name)
 +N' alter column '+quotename(c.name)
 +N' '+quotename(t.name)
 +CASE 
  WHEN t.name IN (N'decimal',N'numeric')
  THEN N'('+CAST(c.prec as varchar)+N','+CAST(c.scale as varchar)+N')'
  WHEN t.name IN (N'float')
   OR t.name like N'%char'
   OR t.name like N'%binary'
  THEN N'('+CAST(c.prec as varchar)+N')'
  ELSE N'' END
 +N' NULL'
from sysobjects o,syscolumns c,systypes t
where o.xtype='U' and o.status>=0
 and o.id=c.id
 and c.xusertype=t.xusertype
 and t.name not in(N'text',N'image',N'ntext',N'timestamp')
 and columnproperty(c.id,c.name,N'AllowsNull')=0
 and columnproperty(c.id,c.name,N'IsComputed')=0
 and columnproperty(c.id,c.name,N'IsIdentity')=0
 and columnproperty(c.id,c.name,N'IsRowGuidCol')=0
 and not exists(
  select * from sysobjects 
  where parent_obj=o.id 
   and xtype='pk' 
   and name in (
    select name from sysindexes idx,sysindexkeys idxk
    where idx.indid not in(0,255)
     and idx.id=idxk.id and idx.indid=idxk.indid
     and idxk.id=c.id and idxk.colid=c.colid))
 and o.name='tbgoods'
 and o.uid=(select schema_id from sys.schemas where name='000') 


oracle 临时表创建
   create global temporary table system.a1 ON COMMIT PRESERVE ROWS
                                   as
                                SELECT goodscode, suppcode
                                  FROM (SELECT ROW_NUMBER ()
                                               OVER (PARTITION BY goodscode
                                                     ORDER BY suppcode)
                                                  rn,
                                               goodscode,
                                               suppcode
                                          FROM "000".tbgoodssupp) a
                                 WHERE a.rn = 1 and 1=2;


 oracle查询表的相关信息：
select cols.column_id, cols.column_name as name, nullable,
 data_type as type,
 decode(data_type, 'CHAR', char_length,
                   'VARCHAR', char_length,
                   'VARCHAR2', char_length,
                   'NCHAR', char_length,
                   'NVARCHAR', char_length,
                   'NVARCHAR2', char_length,
                   null) nchar_length,
  decode( data_type, 'NUMBER', data_precision + data_scale, data_length ) length,
  data_precision precision, data_scale scale, data_length dlength, data_default  
  , comments 
  ,data_type_mod 
  ,cols.char_used
  ,initcap(histogram) histogram
  ,num_distinct
from
  sys.dba_col_comments coms, 
  sys.dba_tab_columns cols
where coms.table_name=cols.table_name
and coms.column_name=cols.column_name
and coms.owner=cols.owner
and cols.table_name = 'TBS'
and cols.owner = '000'
order by column_id