{"id":1790,"date":"2014-04-11T10:56:49","date_gmt":"2014-04-11T10:56:49","guid":{"rendered":"http:\/\/galhano.com\/blog\/?p=1790"},"modified":"2014-06-13T10:42:01","modified_gmt":"2014-06-13T10:42:01","slug":"query-to-list-number-of-records-in-each-table-in-a-database","status":"publish","type":"post","link":"http:\/\/galhano.com\/blog\/?p=1790","title":{"rendered":"Query to list number of records in each table in a database"},"content":{"rendered":"<p>sql server 2005<\/p>\n<p>[sql]<\/p>\n<p>SELECT<br \/>\n\u00a0\u00a0\u00a0 t.NAME AS TableName,<br \/>\n\u00a0\u00a0\u00a0 s.Name AS SchemaName,<br \/>\n\u00a0\u00a0\u00a0 p.rows AS RowCounts,<br \/>\n\u00a0\u00a0\u00a0 SUM(a.total_pages) * 8 AS TotalSpaceKB,<br \/>\n\u00a0\u00a0\u00a0 SUM(a.used_pages) * 8 AS UsedSpaceKB,<br \/>\n\u00a0\u00a0\u00a0 (SUM(a.total_pages) &#8211; SUM(a.used_pages)) * 8 AS UnusedSpaceKB<br \/>\nFROM<br \/>\n\u00a0\u00a0\u00a0 sys.tables t<br \/>\nINNER JOIN\u00a0\u00a0\u00a0\u00a0 \u00a0<br \/>\n\u00a0\u00a0\u00a0 sys.indexes i ON t.OBJECT_ID = i.object_id<br \/>\nINNER JOIN<br \/>\n\u00a0\u00a0\u00a0 sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id<br \/>\nINNER JOIN<br \/>\n\u00a0\u00a0\u00a0 sys.allocation_units a ON p.partition_id = a.container_id<br \/>\nLEFT OUTER JOIN<br \/>\n\u00a0\u00a0\u00a0 sys.schemas s ON t.schema_id = s.schema_id<br \/>\nWHERE<br \/>\n\u00a0\u00a0\u00a0 t.NAME NOT LIKE &#8216;dt%&#8217;<br \/>\n\u00a0\u00a0\u00a0 AND t.is_ms_shipped = 0<br \/>\n\u00a0\u00a0\u00a0 AND i.OBJECT_ID &gt; 255<br \/>\nGROUP BY<br \/>\n\u00a0\u00a0\u00a0 t.Name, s.Name, p.Rows<br \/>\nORDER BY<br \/>\n\u00a0\u00a0\u00a0 t.Name<\/p>\n<p>[\/sql]<\/p>\n","protected":false},"excerpt":{"rendered":"<p>sql server 2005 [sql] SELECT \u00a0\u00a0\u00a0 t.NAME AS TableName, \u00a0\u00a0\u00a0 s.Name AS SchemaName, \u00a0\u00a0\u00a0 p.rows AS RowCounts, \u00a0\u00a0\u00a0 SUM(a.total_pages) * 8 AS TotalSpaceKB, \u00a0\u00a0\u00a0 SUM(a.used_pages) * 8 AS UsedSpaceKB, \u00a0\u00a0\u00a0 (SUM(a.total_pages) &#8211; SUM(a.used_pages)) * 8 AS UnusedSpaceKB FROM \u00a0\u00a0\u00a0 sys.tables t INNER JOIN\u00a0\u00a0\u00a0\u00a0 \u00a0 \u00a0\u00a0\u00a0 sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN \u00a0\u00a0\u00a0 sys.partitions [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"close","ping_status":"close","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[33,40],"tags":[],"class_list":["post-1790","post","type-post","status-publish","format-standard","hentry","category-code-snippets","category-sql","author-admin"],"_links":{"self":[{"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1790","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=1790"}],"version-history":[{"count":9,"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1790\/revisions"}],"predecessor-version":[{"id":1837,"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1790\/revisions\/1837"}],"wp:attachment":[{"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1790"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1790"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1790"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}