--查看约束类型、名称select a.table_name,
a.constraint_name,
b.column_name,
a.constraint_type
from information_schema.table_constraints a,
information_schema.key_column_usage b where
a.table_name = b.table_name
and a.table_schema = b.table_schema
and a.constraint_name = b.constraint_name
3.添加索引/删除索引/查看索引
//TABLE_NAME指的是填入表名,index_name指的是填入索引名....// 创建唯一索引/聚集索引/非聚集索引CREATE[unique][clustered][nonclustered]index index_name
on {tabel/view} (column[dese/asc][....n])// 例如:创建唯一索引CREATEUNIQUEINDEX index_name
ON table_name (column_name)//例如:创建组合索引CREATEINDEX PersonIndex
ON table_name(column_name1, column_name2)//删除索引dropindex table_name.index_name
//查看索引exec sp_helpindex 'table_name'showindexfrom table_name --mysql的写法explainselect......--mysql查看执行计划
select name from syscolumns where id = object_id('表名');
8.查询某个字段在哪个表存在
select a.name 表名,b.name 列名
from sys.objects a,sys.columns b
where object_name(b.object_id)=a.name
and b.name='is_scan’
and type='u'
9.查询用到某个表的存储过程
selectdistinct object_name(id)from syscomments
where id in(select object_id from sys.objects wheretype='P')andtextlike'%TableName%'
10.查询对某个表做了更新操作的存储过程
selectdistinct object_name(id)from syscomments
where id in(select object_id from sys.objects wheretype='P')andtextlike'%update tablename%'
11.查询表字段的数据类型
--第一种写法select b.name tablename,a.name as columnname, typename=(casewhen c.name in('char','nvarhcar','varchar')then c.name+'('+rtrim(convert(char,a.max_length))+')'else c.name end)from sys.columns a innerjoin sys.tables b on b.object_id=a.object_id
innerjoin sys.types c on c.system_type_id=a.system_type_id
where b.name=tablename --表名--第二种写法select column_name,data_type from information_schema.columnswhere table_name = tablename --表名