Sometimes you need to get the size of a particular table located in a database within the Microsoft Server and the following query can provide you that.\u00a0 You can run this manually or place it in a stored procedure for numerous tables.<\/p>\n
The SQL code
SELECT\r\n t.NAME AS TableName,
\r\n s.Name AS SchemaName,
\r\n p.rows AS RowCounts,
\r\n SUM(a.total_pages) * 8 AS TotalSpaceKB,
\r\n CAST(ROUND(((SUM(a.total_pages) * 8) \/ 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
\r\n SUM(a.used_pages) * 8 AS UsedSpaceKB,
\r\n CAST(ROUND(((SUM(a.used_pages) * 8) \/ 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
\r\n (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
\r\n CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) \/ 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB FROM\r\n sys.tables t
\r\nINNER JOIN
\r\n sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN
\r\n sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id INNER
JOIN\r\n sys.allocation_units a ON p.partition_id = a.container_id LEFT OUTER JOIN
\r\n sys.schemas s ON t.schema_id = s.schema_id WHERE
\r\n t.NAME NOT LIKE 'dt%'
\r\n AND t.is_ms_shipped = 0
\r\n AND i.OBJECT_ID > 255
\r\nGROUP BY
\r\n t.Name, s.Name, p.Rows ORDER BY t.Name<\/p><\/p>\n
Looking at the actual code it displays the name of the table, schema name, record count (rows), size and additional information pertaining to the space used or not used.<\/p>\n
This is a nifty way to quickly view which tables are large and growing.\u00a0 This provides analysis on the database giving a SQL admin a method to adjust what could happen in the future.<\/p>\n
You can also place this in your source code to call the stored procedure returning the list of tables and their information.\u00a0 A simple task for everyone to grasp where their database is heading.<\/p>\n
<\/p>\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
Microsoft SQL Server how to get a table size Sometimes you need to get the size of a particular table located in a database within the Microsoft Server and the following query can provide you that.\u00a0 You can run this manually or place it in a stored procedure for numerous tables. The SQL code SELECT […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"rop_custom_images_group":[],"rop_custom_messages_group":[],"rop_publish_now":"initial","rop_publish_now_accounts":[],"rop_publish_now_history":[],"rop_publish_now_status":"pending","_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[1],"tags":[20,263,262],"class_list":["post-928","post","type-post","status-publish","format-standard","hentry","category-sql-tips","tag-delphi","tag-microsoft-sql-server","tag-sql"],"yoast_head":"\n