TABLEのINDEX付与状態確認 †
select OBJECT_NAME(A.object_id) As [オブジェクト名]
,B.name
,C.name
,A.index_id
,B.type_desc
from sys.index_columns A
inner join sys.indexes B on A.index_id=B.index_id
inner join sys.columns C on A.column_id=C.column_id
where A.object_id=B.object_id
and A.object_id=C.object_id
-- and A.object_id=OBJECT_ID('TABLE名')
order by OBJECT_NAME(A.object_id),B.index_id,A.index_column_id
ファイルグループとデータファイルの紐付け †
select D.name
,F.name
,F.physical_name
,F.size
from sys.database_files F
inner join sys.data_spaces D on F.data_space_id=D.data_space_id
統計取得情報 †
SELECT OBJECT_NAME(OBJECT_ID) AS オブジェクト
,name AS 統計名
,STATS_DATE(object_id, stats_id) AS 更新日
FROM sys.stats
order by OBJECT_ID,name
パーティション関数とファイルグループの紐付け †
SELECT
ps.name As [パーティション構成名]
,ds.name As [ファイルグループ名]
,dds.destination_id As [パーティション番号]
FROM sys.destination_data_spaces dds
INNER JOIN sys.partition_schemes ps
ON dds.partition_scheme_id = ps.data_space_id
INNER JOIN sys.data_spaces ds
ON dds.data_space_id = ds.data_space_id
ORDER BY partition_scheme_id
パーティション関数と境界値の紐付け †
SELECT f.name, r.value
FROM sys.partition_range_values r
INNER JOIN sys.partition_functions f
ON r.function_id = f.function_id
バックアップ情報 †
select
S.name
,S.backup_set_id
,S.media_set_id
,S.database_name
,S.type
,S.position
,M.logical_device_name
,M.physical_device_name
,S.compressed_backup_size/1024/1024 as [容量(MB)]
,S.backup_start_date
,S.backup_finish_date
from msdb.dbo.backupset S inner
join msdb.dbo.backupmediafamily M
on S.media_set_id=M.media_set_id
order by S.backup_set_id
LOCK状態確認 †
SELECT CONVERT (SMALLINT, req_spid) AS spid,
db_name(rsc_dbid) AS dbid,
object_name(rsc_objid) AS ObjId,
rsc_indid AS IndId,
SUBSTRING (v.name, 1, 4) AS Type,
SUBSTRING (rsc_text, 1, 16) AS Resource,
SUBSTRING (u.name, 1, 8) AS Mode,
SUBSTRING (x.name, 1, 5) AS Status
FROM master.dbo.syslockinfo,
master.dbo.spt_values v,
master.dbo.spt_values x,
master.dbo.spt_values u
WHERE master.dbo.syslockinfo.rsc_type = v.number
and v.type = 'LR'
and master.dbo.syslockinfo.req_status = x.number
and x.type = 'LS'
and master.dbo.syslockinfo.req_mode + 1 = u.number
and u.type = 'L'
