sqlserver 批量操作表索引等

1.批量操作表语句

select ‘alter table ‘+name+’ alter column id varchar(50) not null’ from sys.tables where name like ‘模糊匹配%’

2.数据库索引操作

SELECT 索引名称=a.name ,表名=c.name ,索引字段名=d.name ,索引字段位置=d.colid,c.status
FROM sysindexes a
JOIN sysindexkeys b ON a.id=b.id AND a.indid=b.indid
JOIN sysobjects c ON b.id=c.id
JOIN syscolumns d ON b.id=d.id AND b.colid=d.colid
WHERE a.indid NOT IN(0,255)
and c.xtype=‘U’ --and c.status>0 --查所有用户表
AND c.name like ‘模糊匹配%’ --查指定表
and d.name =‘id’–查指定字段
ORDER BY c.name,a.name,d.name;