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
