关闭

常用SQL

标签: sql
320人阅读 评论(0) 收藏 举报
分类:

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

----------------------------------------------------------------------------------------------------------


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


0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:23342次
    • 积分:662
    • 等级:
    • 排名:千里之外
    • 原创:44篇
    • 转载:7篇
    • 译文:0篇
    • 评论:0条
    文章分类