About Me

Colorado
Paul has 18 years experience with Microsoft SQL Server. He has worked in the roles of production DBA, database developer, database architect, applications developer, business intelligence and data warehouse developer, and instructor for students aspiring for MCDBA certification. He has performed numerous data migrations and supported large databases (3 Terabyte, 1+ billion rows) with high transactions. He is a member of PASS, blogs about lessons learned from a developer’s approach to SQL Server administration, and has been the president of the Boulder SQL Server Users’ Group for 11 years, from January 2009 to 2020.

Wednesday, September 11, 2013

This is my favorite view on the blog.  It contains is an instance-wide list of tables, complete with table size in number of rows and number of bytes.

It does have many dependencies, all of which can be found on this blog.
                vwPartitionRaw
                vwDictCol
                SpaceUsed
                vwObjectFileGroup
         udfPadLeft()
                udfFormatInteger()

This object is referenced by various other objects in this blog.

USE Admin
IF Object_ID('dbo.vwTable') Is Not Null
      DROP VIEW dbo.vwTable
go

CREATE VIEW [dbo].[vwTable]
AS
/*    DATE        AUTHOR            REMARKS
      9/11/13           PPaiva            Initial creation.


      SELECT *
      FROM Admin.dbo.vwTable
      ORDER BY Rows desc

      SELECT TOP 100 *
      FROM Admin.dbo.vwTable
      WHERE DB = 'MyDB'
      ORDER BY 1, 2, 3, 4

*/

-- Get rows/table
WITH cteTP
AS (  SELECT Server, DB, SchemaName, Tablename, object_id, Sum(Rows) Rows, Count(*) NumPartitions
            FROM dbo.vwPartitionRaw
            GROUP BY  Server, DB, Tablename, SchemaName, object_id
      ),

-- Tables with number of columns
cteDC
AS (  SELECT  Server,
                        DB,
                        SchemaName,
                        ObjName TableName,
                        IsSystemDB,
                        ObjCreateDate,
                        ObjModifyDate,
                        object_id,
                        Count(*) NumCols,
                        Min(ViewCreateDate) ViewCreateDate
            FROM dbo.vwDictCol
            WHERE ObjType = 'Table'
            GROUP BY Server,
                        DB,
                        SchemaName,
                        ObjName,
                        IsSystemDB,
                        ObjCreateDate,
                        ObjModifyDate,
                        object_id
      ),
-- Calc BytesPerRow          
cteRowSize
AS (  SELECT Server, DB, object_id, Sum(Length) as BytesPerRow
            FROM dbo.vwDictCol
            WHERE ObjType = 'table'
            GROUP BY Server, DB, object_id
      ),

-- Contains
cteSpaceUsed
AS (  SELECT Server, DB, object_id, DataMB, IndexMB, TotalMB, ReservedMB, PopDate SpaceUsedPopDate
                  --SizeMB, Reserved, data, index_size
            FROM dbo.SpaceUsed
      ),
     
cteFileGroup
AS    (     SELECT Server, DB, ObjectID, FileGroup
            FROM dbo.vwObjectFileGroup
      )
-- Final query         
SELECT  tp.Server,
            tp.DB,
            fg.FileGroup,
            tp.SchemaName,
            tp.TableName,          
            Admin.dbo.udfPadLeft(
                                    Admin.dbo.udfFormatInteger(tp.Rows),
                                    15,
                                    ' '
                                    ) RowsFmt,
            Admin.dbo.udfFormatInteger(su.TotalMB)TotalMBFmt,
            tp.NumPartitions,
            dc.NumCols,
            dc.ObjCreateDate,
            dc.ObjModifyDate,
            su.DataMB,
            su.IndexMB,
            su.ReservedMB,
            tp.Rows,
            su.TotalMB,
            rs.BytesPerRow,
            Convert(decimal(10, 1), (rs.BytesPerRow * tp.Rows / 1000000000.0)) DataGBProjectedMax,
            dc.object_id,
            IsSystemDB,
            'Use [' + tp.db + '];  Exec sp_help ''[' + tp.SchemaName + '].[' + tp.TableName + ']''' spHelp,
            'Use [' + tp.db + '];  Exec sp_helpindex ''[' + tp.SchemaName + '].[' + tp.TableName + ']''' spHelpIndex,
            'TRUNCATE TABLE [' + tp.DB + '].[' + tp.SchemaName + '].[' + tp.TableName + ']' TruncateScript,
            'DROP TABLE [' + tp.DB + '].[' + tp.SchemaName + '].[' + tp.TableName + ']' DropScript,
            su.SpaceUsedPopDate,
            dc.ViewCreateDate vwDictColCreateDate
FROM cteTP tp
LEFT JOIN cteDC dc
      ON  tp.Server = dc.Server
      AND tp.DB = dc.DB
      AND tp.object_id = dc.object_id           -- object_id is unique only within a database
LEFT JOIN cteRowSize rs
      ON  rs.Server = tp.Server
      AND rs.DB = tp.DB
      AND rs.object_id = tp.object_id
LEFT JOIN cteSpaceUsed su
      ON  su.Server = tp.Server
      AND su.DB = tp.DB
      AND su.object_id = tp.object_id
LEFT JOIN cteFileGroup fg
      ON  fg.ObjectID = dc.Object_ID
      AND tp.Server = fg.Server
      AND tp.DB = fg.DB





No comments:

Post a Comment