常用SQL

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包含所有空格，返回字节数