一、系统表、字段操作
select * from usermenu
得到所有的数据表(用户数据表):
select * from sysobjects where xtype = 'u'
得到当前数据表的所有字段:
select * from syscolumns where id = object_id('tb_clientdata')
取字段、字段类型、字段长度
select a.name,b.name,a.length from syscolumns a left join systypes b on a.xtype = b.xtype where id = object_id('tb_clientdata') and b.name <> 'sysname'
取表、字段、字段类型、字段长度
select c.name as tbbane,a.name as clname,b.name as type,a.length from syscolumns a left join systypes b on a.xtype = b.xtype left join sysobjects c on c.id = a.id where b.name <> 'sysname' and c.xtype = 'u'
SQL: select * into b from a where 1<>1
EXEC sp_rename 'customers.[contact title]', 'title', 'COLUMN'
二、索引和约束
·约束
exec sp_helpconstraint index_tb '查看约束
alter table info_state_jb add constraint ck_index CHECK (state_jb like '[0-9][0-9][0-9][0-9][0-9]') '创建约束 ck_index表示约束名
alter table info_state_jb add constraint ck2_index check(state_jb in ('MM','GG')) '创建约束
alter table index_tb drop constraint ck2_index '删除约束
·索引
exec sp_helpindex info_sysimage '查看索引
create index id_index on info_state_jb(id) '创建索引
create clustered index id_index on info_state_jb(id) '创建聚焦索引(主键)
create unique index uq1_index on info_MistakeThing(MistakeThing) ’创建唯一索引
drop index info_state_jb.id_index '删除索引
·外键
exec sp_fkeys '查看外键
三、命令
SP_WHO 查看进程
sp_help 存储过程名/视图名
sp_kill 71 查杀进程
sp_helptext ph_vw_dalei 查视图名称
四、获取记录号
select ROW_NUMBER() OVER(ORDER BY id DESC) as id,* from UserTable
五、条件判断
SELECT CASE LEFT(TB116_SHOP_CODE,2) WHEN '18' THEN 'AA' ELSE 'k' END FROM UserTable