Solved

SQL database analysis

Posted on 2014-03-26
3
133 Views
Last Modified: 2014-04-01
How to write out a query to list out all the tables in a particular database, with number of rows, total size, index size, etc ?

Can I automate it to send it out daily through email ? Tks
0
Comment
Question by:AXISHK
  • 2
3 Comments
 
LVL 33

Accepted Solution

by:
ste5an earned 500 total points
ID: 39956424
E.g.

USE AdventureWorks2012;

SELECT  * ,
        Q.page_count * 8 / 1024 AS MB
FROM    ( SELECT    S2.[name] AS schema_name ,
                    T.[name] AS table_name ,
                    SUM(S.row_count) AS row_count ,
                    SUM(S.used_page_count) AS page_count ,
                    CASE SUM(S.used_page_count)
                      WHEN 0 THEN 0
                      ELSE SUM(S.row_count) / SUM(S.used_page_count)
                    END AS rows_per_page
          FROM      sys.dm_db_partition_stats S
                    INNER JOIN sys.tables T ON T.object_id = S.object_id
                    INNER JOIN sys.schemas S2 ON S2.schema_id = T.schema_id
          WHERE     ( S.index_id = 0
                      OR S.index_id = 1
                    )
          GROUP BY  S2.[name] ,
                    T.[name]
        ) Q
ORDER BY Q.page_count DESC;

Open in new window


Works on 2005+.
0
 

Author Comment

by:AXISHK
ID: 39957911
Tks. How to automote it to send out by email daily ?
0
 
LVL 33

Assisted Solution

by:ste5an
ste5an earned 500 total points
ID: 39958303
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question