Home > Link, Microsoft SQL Server, MSBI, Optimization, Query, Script, SQL Mentalist, SQL PraRup, SQL Query, SQL Server, Technology,, TSQL, Vishal Pawar > BI SQL # 199 : SQL Server DBA Scripts : Temporary Tables and Their Size

BI SQL # 199 : SQL Server DBA Scripts : Temporary Tables and Their Size

November 14, 2013 Leave a comment Go to comments

Hi Folks,

In this article we are going to cover Temporary Tables and Their Size.

In this post we are going to discuss following points:

  • Problem Statement of SQL Script:
  • Description of SQL Script:
  • SQL Script Output Column
  • SQL Script Code
  • SQL Script Output Screenshot
  • User Level to execute

    Problem Statement of SQL Script:

    Get Temporary tables and their size.

    Description of SQL Script:

    This script will help in maintain the temporary database in terms of size.

    SQL Script Output Column

    image

    SQL Script Code

      SELECT TBL.NAME AS ObjName
    ,STAT.row_count AS StatRowCount
    ,STAT.used_page_count * 8 AS UsedSizeKB
    ,STAT.reserved_page_count * 8 AS RevervedSizeKB
FROM tempdb.sys.partitions AS PART
INNER JOIN tempdb.sys.dm_db_partition_stats AS STAT ON PART.
    partition_id = STAT.partition_id
    AND PART.partition_number = STAT.partition_number
INNER JOIN tempdb.sys.tables AS TBL ON STAT.object_id = TBL.
    object_id
ORDER BY TBL.NAME;
Advertisement
Categories: Link, Microsoft SQL Server, MSBI, Optimization, Query, Script, SQL Mentalist, SQL PraRup, SQL Query, SQL Server, Technology,, TSQL, Vishal Pawar Tags: , , , , , , , , , , , ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out /  Change )

Cancel

Connecting to %s

%d bloggers like this: