1、重装系统后,把原来sql server2000/2005的mdf和ldf文件考到重装的系统,通过附加功能恢复数据库,那么原来数据库的索引是否会还在?需不需要重建索引呢?2、如何查看某库的所有索引?并怎么样单独把这些索引导出来重建立?3、最准确查看当前SQL SERVER用了多少内存的方法是什么,好象打了sp4后,进程中的sqlservr已经报不准了.
解决方案 »
- 如何判断表中那个字段建立了聚集索引?
- 数据导入问题
- 安装sql server 2008的时候出现的问题,困扰我好久了
- 这样子数据库可以恢复吗 ?(有mdf,ldf文件)
- 不装SQL客户端如何访问SQL服务器
- 怎样让SQL SERVER 2005支持汉字数字?
- 好奇怪,请教各位大侠:我的机子现在上网没多久,进程中就有一个sqlserver的文件cpu的占用率将近95%左右,让机子慢的要命,请问是怎么回
- sql2000常见问题 棘手
- 帮忙解决一下sql中如何使 CREATE TABLE接受的tablename是变量的情况,具体问题内详
- 牛人请进:这个表结构该如何构造?
- 为什么每次开机,看日志发现sql都要对某几个数据库进行"检查点操作"
- ODBC中连接远程dsn的写法?
SqlServer数据库字典--索引.sqlSELECT TOP 100 PERCENT --a.id, CASE WHEN b.keyno = 1 THEN c.name ELSE '' END AS 表名, CASE WHEN b.keyno = 1 THEN a.name ELSE '' END AS 索引名称, d.name AS 列名, b.keyno AS 索引顺序, CASE indexkey_property(c.id, b.indid, b.keyno, 'isdescending') WHEN 1 THEN '降序' WHEN 0 THEN '升序' END AS 排序, CASE WHEN p.id IS NULL THEN '' ELSE '√' END AS 主键, CASE INDEXPROPERTY(c.id, a.name, 'IsClustered') WHEN 1 THEN '√' WHEN 0 THEN '' END AS 聚集, CASE INDEXPROPERTY(c.id, a.name, 'IsUnique') WHEN 1 THEN '√' WHEN 0 THEN '' END AS 唯一, CASE WHEN e.id IS NULL THEN '' ELSE '√' END AS 唯一约束, a.OrigFillFactor AS 填充因子, c.crdate AS 创建时间, c.refdate AS 更改时间 FROM dbo.sysindexes a INNER JOIN dbo.sysindexkeys b ON a.id = b.id AND a.indid = b.indid INNER JOIN dbo.syscolumns d ON b.id = d.id AND b.colid = d.colid INNER JOIN dbo.sysobjects c ON a.id = c.id AND c.xtype = 'U' LEFT OUTER JOIN dbo.sysobjects e ON e.name = a.name AND e.xtype = 'UQ' LEFT OUTER JOIN dbo.sysobjects p ON p.name = a.name AND p.xtype = 'PK' WHERE (OBJECTPROPERTY(a.id, N'IsUserTable') = 1) AND (OBJECTPROPERTY(a.id, N'IsMSShipped') = 0) AND (INDEXPROPERTY(a.id, a.name, 'IsAutoStatistics') = 0) ORDER BY c.name, a.name, b.keyno
3、最准确查看当前SQL SERVER用了多少内存的方法是什么,好象打了sp4后,进程中的sqlservr已经报不准了.
crtl + alt + del
as
(
select
top 100 percent row_number()over(partition by a.Name order by b.index_id) as ID,object_Name(a.object_id) as TableName,a.Name as IndexName,c.Name as ColName,
description=a.type_desc,a.is_unique,a.is_primary_key,a.is_unique_constraintfrom
sys.indexes a
join
sys.index_columns b on a.Object_id=b.Object_id and a.index_id=b.index_id
join
sys.columns c on c.object_id=a.object_id and c.column_id=b.column_id
where
objectproperty(a.object_id,'IsUserTable')=1 and a.Object_id<>object_id('dtproperties')
order by TableName,a.Name
)
,index2
as
(
select
TableName,IndexName,ColName,is_unique,is_primary_key,is_unique_constraint,description
from
(select distinct TableName,IndexName,is_unique,is_primary_key,is_unique_constraint,description from Index1)a
outer apply
(select ColName=stuff(replace(replace((select ColName from Index1 where TableName=a.TableName and IndexName=a.IndexName order by ID for xml auto),'<Index1 ColName="',','),'"/>',''),1,1,''))b
)
select
*
from
index2
order by TableName,IndexName在05查看索引信息
右键库/所有任务/生成SQL语句/选择索引.
库文件未丢失,附加后数据库与原一样,索引不会丢失.
2
declare @tbname varchar(100),@sqlstr varchar(200)
declare t_cur cursor for select distinct object_name(id) as tbname from sysindexes where id in (select id from sysobjects where type='U')
open t_cur
fetch next from t_cur into @tbname
while @@fetch_status=0
begin
select @tbname
set @sqlstr='exec sp_helpindex '''+@tbname+''''
exec (@sqlstr)
fetch next from t_cur into @tbname
end
close t_cur
deallocate t_cur