DB/SQLServer/調査用SQL の変更点

追加された行はこの色です。
削除された行はこの色です。
DB/SQLServer/調査用SQL へ行く。
DB/SQLServer/調査用SQL の差分を削除
*[[DB]]/[[SQL Server/DB/SQLServer]]/調査用SQL [#jfa92fa2]
*[[DB]]/[[SQL Server>DB/SQLServer]]/調査用SQL [#jfa92fa2]
#contents


**TABLEのINDEX付与状態確認 [#k1f955df]
 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

**ファイルグループとデータファイルの紐付け [#vb223302]
 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

**統計取得情報 [#va3a59dd]
 SELECT OBJECT_NAME(OBJECT_ID) AS オブジェクト
     ,name AS 統計名
     ,STATS_DATE(object_id, stats_id) AS 更新日
 FROM sys.stats
 order by OBJECT_ID,name

**パーティション関数とファイルグループの紐付け [#l82cd439]
 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

**パーティション関数と境界値の紐付け [#mf5bf26a]
 SELECT f.name, r.value
 FROM sys.partition_range_values r
 INNER JOIN sys.partition_functions f
     ON r.function_id = f.function_id

**バックアップ情報 [#c46b7ce2]
 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状態確認 [#uc514764]
 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'
-[[ロックモード詳細>http://msdn.microsoft.com/ja-jp/library/ms175519.aspx]]