{"id":1981,"date":"2015-10-07T13:20:05","date_gmt":"2015-10-07T13:20:05","guid":{"rendered":"http:\/\/galhano.com\/blog\/?p=1981"},"modified":"2015-10-07T13:20:05","modified_gmt":"2015-10-07T13:20:05","slug":"query-to-list-number-of-records-in-each-table-in-a-database-2","status":"publish","type":"post","link":"http:\/\/galhano.com\/blog\/?p=1981","title":{"rendered":"Query to list number of records in each table in a database"},"content":{"rendered":"<p>[sql]<br \/>\nSELECT<br \/>\n    t.NAME AS TableName,<br \/>\n    i.name as indexName,<br \/>\n    p.[Rows],<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 &gt; 255 AND<br \/>\n    i.index_id &lt;= 1<br \/>\nGROUP BY<br \/>\n    t.NAME, i.object_id, i.index_id, i.name, p.[Rows]<br \/>\nORDER BY<br \/>\n    object_name(i.object_id)<br \/>\n[\/sql]<\/p>\n","protected":false},"excerpt":{"rendered":"<p>[sql] SELECT t.NAME AS TableName, i.name as indexName, p.[Rows], sum(a.total_pages) as TotalPages, sum(a.used_pages) as UsedPages, sum(a.data_pages) as DataPages, (sum(a.total_pages) * 8) \/ 1024 as TotalSpaceMB, (sum(a.used_pages) * 8) \/ 1024 as UsedSpaceMB, (sum(a.data_pages) * 8) \/ 1024 as DataSpaceMB FROM sys.tables t INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN sys.partitions p ON [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"close","ping_status":"close","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[],"class_list":["post-1981","post","type-post","status-publish","format-standard","hentry","category-sql-server","author-admin"],"_links":{"self":[{"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1981","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1981"}],"version-history":[{"count":1,"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1981\/revisions"}],"predecessor-version":[{"id":1982,"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1981\/revisions\/1982"}],"wp:attachment":[{"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1981"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1981"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1981"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}