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

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;
Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: