http://engineermemo.wordpress.com/2011/04/30/sql-azure-%E3%81%A7%E3%83%86%E3%83%BC%E3%83%96%E3%83%AB%E3%81%AE%E3%82%B5%E3%82%A4%E3%82%BA%E3%82%92%E5%8F%96%E5%BE%97/ にあるものをベースに、DataSync 関連のテーブルを除去したかったので以下のようなので調査。
1: SELECT
2: OBJECT_NAME(sys.indexes.object_id) AS object_name,
3: sys.indexes.name,
4: -- sys.indexes.type_desc,
5: sys.dm_db_partition_stats.row_count,
6: SUM(sys.dm_db_partition_stats.reserved_page_count) * 8.0 / 1024 AS [Size (MB)]
7: FROM
8: sys.dm_db_partition_stats
9: LEFT JOIN
10: sys.indexes
11: ON
12: sys.dm_db_partition_stats.object_id = sys.indexes.object_id
13: AND
14: sys.dm_db_partition_stats.index_id = sys.indexes.index_id
15: WHERE OBJECT_NAME(sys.indexes.object_id) NOT LIKE '%_dss_tracking'
16: AND sys.indexes.name NOT LIKE 'PK_DataSync%'
17: AND sys.indexes.type_desc = 'CLUSTERED'
18: GROUP BY
19: sys.indexes.object_id,
20: sys.indexes.index_id,
21: sys.indexes.name,
22: -- sys.indexes.type_desc,
23: sys.dm_db_partition_stats.row_count
24: ORDER BY
25: OBJECT_NAME(sys.indexes.object_id),
26: sys.indexes.object_id,
27: sys.indexes.index_id
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
当初は http://blog.clicdata.com/2012/07/31/table-row-count-and-size-in-sql-azure/ にあるSQL文を試していたのだが、2倍の数値が出て少し悩んでしまった。どういう理由で row_count の値をSUM で足したりしてるの…