-> Below script can be used to get the size of all tables in a SQL Server database.

SELECT

a3.name +'.'+a2.name AS [TableName],
a1.rows as [# Records],
(a1.reserved + ISNULL(a4.reserved,0))* 8 AS [Reserved KB],
a1.data * 8 AS [Data KB],
(CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS [Indexes KB],
(CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS [Unused KB],
convert(decimal(18,2),(((a1.reserved + ISNULL(a4.reserved,0))* 8) - ((CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8))/1024.0/1024.0) as [Table_used_Space GB]
FROM
(SELECT
ps.object_id,
SUM (
CASE
WHEN (ps.index_id < 2) THEN row_count
ELSE 0
END
) AS [rows],
SUM (ps.reserved_page_count) AS reserved,
SUM (
CASE
WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
END
) AS data,
SUM (ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
GROUP BY ps.object_id) AS a1
LEFT OUTER JOIN
(SELECT
it.parent_id,
SUM(ps.reserved_page_count) AS reserved,
SUM(ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
WHERE it.internal_type IN (202,204)
GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)
INNER JOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id )
INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
WHERE a2.type != N'S' and a2.type != N'IT'
--ORDER BY a3.name, a2.name
--order by a1.rows desc
order by [Table_used_Space GB] desc

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

-> Below TSQL can be used to query commandlog table created by Ola Hallengren’s Index Maintenance script to check Alter Index duration.

select
[DatabaseName]
,[SchemaName]
,[ObjectName]
,[ObjectType]
,[IndexName]
,[IndexType]
,[CommandType]
,ExtendedInfo.value('(/ExtendedInfo/PageCount)[1]', 'int') as Page_Count,convert(decimal(5,2),ExtendedInfo.value('(/ExtendedInfo/Fragmentation)[1]', 'Float')) as Fragmentation
, CAST(DATEDIFF(second, StartTime, EndTime) / 60 / 60 / 24 / 7 AS NVARCHAR(50)) + ':'
+ CAST(DATEDIFF(second, StartTime, EndTime) / 60 / 60 / 24 % 7 AS NVARCHAR(50)) + ':'
+ CAST(DATEDIFF(second, StartTime, EndTime) / 60 / 60 % 24 AS NVARCHAR(50)) + ':'
+ CAST(DATEDIFF(second, StartTime, EndTime) / 60 % 60 AS NVARCHAR(50)) + ':'
+ CAST(DATEDIFF(second, StartTime, EndTime) % 60 AS NVARCHAR(50)) + '' as "w:d:h:m:s"
, DATEDIFF(second, StartTime, EndTime) [Duration in Sec]
,[StartTime]
,[EndTime]
from [dbo].[Commandlog]
order by DATEDIFF(second, StartTime, EndTime) desc

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

-> Below query can be used to get the details of statistics on a table.

select distinct object_name(a.object_id) as Table_name,a.name StatsName,a.stats_id,a.auto_created,a.user_created,c.name as Stats_Column from sys.stats a
INNER JOIN sys.stats_columns b on a.object_id=b.object_id and a.stats_id=b.stats_id
INNER JOIN sys.columns c on b.object_id=c.object_id and b.column_id=c.column_id
where a.object_id=object_id('SomeTableName')

-> Removing the where clause will display all statistics from all tables on the database.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights