«

»

Jun 21

Query to find Table Space Utilization in Database

This is the query to find the amount of space which each table occupy in there respective Database.


SELECT
DATABASENAME (TITLE ‘Database’)
,TABLENAME (TITLE ‘Table Name’)
,SUM(CURRENTPERM)/(1024*1024*1024)Ā  (DECIMAL(15,6))Ā  (TITLE ‘Current Perm Space(GB)’)
FROM DBC.TABLESIZE
WHERE DATABASENAME = ‘YOUR_DATABASE_NAME
GROUP BY 1,2
ORDER BY 3 DESC

Where YOUR_DATABASE_NAME is the name of the database for which u need to do the analysis.

This Query will return 3 columns Database, Table Name and Current Perm Space(GB).Based on the analysis of these data we can easily see that how much space each table is occupying in the database.


14 comments

Skip to comment form

  1. satish

    Hi Raj,

    Can you please give me a query for transpose columns to rows and rows to columns?

  2. Melony

    Thank you for the excellent query!!!!!!

    Melony
    Database Analyst

  3. Suraj

    I have eight tables in one schema say “S1″…in teradata DB…each eight table having a unique column say ‘UC’.

    Now i want to display all the available tablename whose column ‘UC’ value having character value ‘P’ and if not then do’t display the respective tablename??

  4. venkat

    Hi,

    This is site one of very good site which i had seen.
    thank you so much for providing such valuable information

  5. gaurav sharma

    really nice site.. very good contents. THANKS

    1. admin

      Thanks for appreciation

  6. Nitin

    Excellent Help.
    Thanks šŸ™‚

    1. admin

      Your Welcome šŸ™‚

  7. munna

    an excellent site to learn any thing about teradata

    1. admin

      Thanks Munna šŸ™‚

      i appreciate if you also give your feedback for the improvement of the site

      1. munna

        sure…..iam planning for certifications..iam reading the materials your are provided as PDF in this site..but i dont have any planned way to achieve those certifications….can you provide some informations on the posts…so that every one will get benefited…

        1. admin

          Go to this link –

          http://www.teradatatech.com/?p=62

          for complete knowledge about Teradata certifications.

          Initially you have to clear Basics and SQL certificate , to move to another level.

          I suggest to go through Teradata Users Guide by COFFINGS and also refer WBT material from Teradata site itself.

          For practice question on both these certifications you can refer –
          http://www.teradatatech.com/?p=325
          http://www.teradatatech.com/?cat=5

  8. Eulah

    Greetings I located your site by mistake when i searched Live search for this concern, I have to say your site is really useful I also love the layout, it is good!

    1. admin

      Thanks …. i must say some mistakes are good šŸ™‚

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>