1. 获取字段默认值
MSS:
SELECT SO.NAME AS "Table Name", SC.NAME AS "Column Name", SM.TEXT AS "Default Value"
FROM dbo.sysobjects SO INNER JOIN dbo.syscolumns SC ON SO.id = SC.id
LEFT JOIN dbo.syscomments SM ON SC.cdefault = SM.id
WHERE SO.xtype = 'U' and SO.NAME = 'ROBXFK'
ORDER BY SO.[name], SC.colid
2. 查看表的索引
ORA:
select t.*,i.* from user_ind_columns t,user_indexes i where t.index_name = i.index_name and t.table_name = i.table_name and t.table_name = 'ROBXDJ'
3. 查看实表的建表SQL
MSS:
表-》编写表脚本为-》Create到-》新查询编辑器窗口
ORA:
表-》Edit-》ViewSQL
4. 查看主键
MSS:
EXEC sp_pkeys @table_name='ROBXDJ'
ORA:
select a.constraint_name, a.column_name
from user_cons_columns a, user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'P'
and a.table_name = 'PROJECTINFO'
5. 查看 修改 删除约束
ORA:
select * from user_constraints where table_name = 'ROBXDJ'
alter table ROBXDJ add constraint PK_ROBXDJ_BHX UNIQUE(ROBXDJ_BH) enable novalidate
alter table ROBXDJ add constraint PK_ROBXDJ_BHX unique(ROBXDJ_BH) novalidate
alter table ROBXDJ disable constraint PK_ROBXDJ_BHX
alter table ROBXDJ enable constraint PK_ROBXDJ_BHX
alter table ROBXDJ drop constraint PK_ROBXDJ_BHX
MSS:
查看:
select c.name from sysconstraints a
inner join syscolumns b on a.colid=b.colid
inner join sysobjects c on a.constid=c.id
where a.id=object_id('ROJKHX')
and b.name='ROJKHX_XJHKJE'
修改:
Alter
table
表名
drop
constraint
约束名
ALTER TABLE ROJKHX add DEFAULT ('0') for ROJKHX_XJHKJE WITH VALUES
----------------------------------------------------------------------------------------------------------
declare @constraint_name varchar(100)
select @constraint_name = c.name from sysconstraints a
inner join syscolumns b on a.colid=b.colid
inner join sysobjects c on a.constid=c.id
where a.id=object_id('ROJKHX')
and b.name='ROJKHX_XJHKJE'
--select @constraint_name
if @constraint_name is null
begin
--exec('alter table ROJKHX drop constraint '+@constraint_name) ;
ALTER TABLE ROJKHX add DEFAULT ('0') for ROJKHX_XJHKJE WITH VALUES
end
----------------------------------------------------------------------------------------------------------
删除约束
declare @constaintname nvarchar(60)
declare @sql nvarchar(500)
set @constaintname = (select distinct c.name from sysconstraints a
inner join syscolumns b on a.colid = b.colid
inner join sysobjects c on a.constid = c.id
where a.id = object_id('ROYWSQ')
and b.name = 'ROYWSQ_PJZT');
set @sql = N'alter table ROYWSQ drop constraint '+@constaintname
exec sp_executesql @sql
6.找到重复数据
select ROBXDJ_BH from ROBXDJ group by ROBXDJ_BH having count(1)>1;
7.查看表结构
MSS: sp_help ROBXDJ
ORA: SQL> desc ROBXDJ
8.查看字段长度
MSS: select len(a) from A --len返回字符数,不包括尾随空格 ,
select datalength(a) from A --datalength包含所有空格,返回字节数