表的操作

–清理字段里面的空格 
replace (TypeCode ,’ ’ ,”)

–查询表的注解 
exec UP_DataDictionary ‘表明’

–修改表中字段类型或长度 
alter table <表名> alter column <字段名> 新类型名(长度) 
–给表新增字段 
alter table <表名> add <字段名> 字段类型(长度)

–查询库中所有表结构 
select 
case when a.colorder=1 then d.name else ” end as ‘表名’, 
case when a.colorder=1 then isnull(f.value,”) else ” end as ‘表说明’, 
a.colorder as ‘字段序号’, 
a.name as ‘字段名’, 
case when COLUMNPROPERTY(a.id,a.name,’IsIdentity’)=1 then ‘√’ else ” end as ‘标识’, 
case when exists( 
select 1 from sysobjects where xtype=’PK’ and name in( 
select name from sysindexes where indid in( 
select indid from sysindexkeys where id=a.id AND colid=a.colid))) then ‘√’ else ” end as ‘主键’, 
b.name as’类型’, 
a.length as ‘占用字节数’, 
COLUMNPROPERTY(a.id,a.name,’PRECISION’) as’长度’, 
isnull(COLUMNPROPERTY(a.id,a.name,’Scale’),0) as ‘小数位数’, 
case when a.isnullable=1 then ‘√’ else ” end as ‘允许空’, 
isnull(e.text,”) as ‘默认值’, 
isnull(g.[value],”) as ‘字段说明’ 
FROM syscolumns a 
left join systypes b on a.xtype=b.xusertype 
inner join sysobjects d on a.id=d.id and d.xtype=’U’ and d.name<>’dtproperties’ 
left join syscomments e on a.cdefault=e.id 
left join sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id 
left join sys.extended_properties f on d.id=f.major_id and f.minor_id=0 
–where d.name=’要查询的表’ –如果只查询指定表,加上此条件 
order by a.id,a.colorder

select name from sysobjects where xtype=’TR’ –所有触发器名称 
select name from sysobjects where xtype=’P’ –所有存储过程 
select name from sysobjects where xtype=’V’ –所有视图 
select name from sysobjects where xtype=’U’ –所有表

–全部禁用： 
Alter table t1 disable trigger all; 
–全部生效： 
Alter table t1 enable trigger all; 
–单个禁用： 
Alter table t1 disable trigger 触发器名;

–查出触发器、存储过程的名称与内容： 
select b.name as ‘名称’,a.text as ‘内容’,case xtype when ‘p’ then ‘存储过程’ else ‘触发器 ’ end as ‘类型’ 
from syscomments a,sysobjects b where object_id(b.name)=a.id and b.xtype in( ‘P’,’TR’) and b.status> =0 order by ‘类型’

–查出某表中所有字段名与字段类型： 
select a.name as [column],b.name as type 
from syscolumns a,systypes b 
where a.id=object_id(‘表名’) and a.xtype=b.xtype