{"id":1413,"date":"2014-02-19T07:14:05","date_gmt":"2014-02-19T06:14:05","guid":{"rendered":"http:\/\/soci.hu\/blog\/?p=1413"},"modified":"2014-02-19T07:14:05","modified_gmt":"2014-02-19T06:14:05","slug":"nagy-sql-objektumok-listazasa","status":"publish","type":"post","link":"https:\/\/soci.hu\/blog\/index.php\/2014\/02\/19\/nagy-sql-objektumok-listazasa\/","title":{"rendered":"Nagy SQL objektumok list\u00e1z\u00e1sa"},"content":{"rendered":"<p>Ha v\u00e9letlen\u00fcl nem menne az SSMS riportja:<\/p>\n<p>SELECT<br \/>\n    t.NAME AS TableName,<br \/>\n    i.name as indexName,<br \/>\n    sum(p.rows) as RowCounts,<br \/>\n    sum(a.total_pages) as TotalPages,<br \/>\n    sum(a.used_pages) as UsedPages,<br \/>\n    sum(a.data_pages) as DataPages,<br \/>\n    (sum(a.total_pages) * 8) \/ 1024 as TotalSpaceMB,<br \/>\n    (sum(a.used_pages) * 8) \/ 1024 as UsedSpaceMB,<br \/>\n    (sum(a.data_pages) * 8) \/ 1024 as DataSpaceMB<br \/>\nFROM<br \/>\n    sys.tables t<br \/>\nINNER JOIN<br \/>\n    sys.indexes i ON t.OBJECT_ID = i.object_id<br \/>\nINNER JOIN<br \/>\n    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id<br \/>\nINNER JOIN<br \/>\n    sys.allocation_units a ON p.partition_id = a.container_id<br \/>\nWHERE<br \/>\n    t.NAME NOT LIKE &#8216;dt%&#8217; AND<br \/>\n    i.OBJECT_ID > 255 AND<br \/>\n    i.index_id <= 1\nGROUP BY \n    t.NAME, i.object_id, i.index_id, i.name \nORDER BY \n    TotalSpaceMB\n\n\tSELECT\n    OBJECT_NAME(i.OBJECT_ID) AS TableName,\n    i.name AS IndexName,\n    i.index_id AS IndexID,\n    8 * SUM(a.used_pages) AS 'Indexsize(KB)'\nFROM\n    sys.indexes AS i JOIN \n    sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id JOIN \n    sys.allocation_units AS a ON a.container_id = p.partition_id\nGROUP BY\n    i.OBJECT_ID,\n    i.index_id,\n    i.name\nORDER BY\n    'Indexsize(KB)' desc\n[\/source]\n<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Ha v\u00e9letlen\u00fcl nem menne az SSMS riportja: SELECT t.NAME AS TableName, i.name as indexName, sum(p.rows) as RowCounts, sum(a.total_pages) as TotalPages, sum(a.used_pages) as&#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6,4,30],"tags":[],"class_list":["post-1413","post","type-post","status-publish","format-standard","hentry","category-adatbazisok","category-szakmai-elet","category-sql-server"],"_links":{"self":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/1413","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/comments?post=1413"}],"version-history":[{"count":1,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/1413\/revisions"}],"predecessor-version":[{"id":1414,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/1413\/revisions\/1414"}],"wp:attachment":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/media?parent=1413"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/categories?post=1413"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/tags?post=1413"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}