Sql query to get RowCount and Size of tables in the database

Sometimes we might need to keep track of space utilized by each table in our database. Specially in case if application database grew unexpectedly over course of development and testing. 

We want to know the space used of each table without using Management Studio's table properties. 

Here is your query. Just replace msdb with your DatabaseName

SELECT  sc.name + '.' + t.NAME AS TableName,          p.[Rows],          ( SUM(a.total_pages) * 8 ) / 1024 AS TotalReservedSpaceMB, -- Number of total pages * 8KB size of each page in SQL Server          ( SUM(a.used_pages) * 8 ) / 1024 AS UsedDataSpaceMB,          ( SUM(a.data_pages) * 8 ) / 1024 AS FreeUnusedSpaceMB  FROM    msdb.sys.tables t          INNER JOIN msdb.sys.schemas sc ON sc.schema_id = t.schema_id          INNER JOIN msdb.sys.indexes i ON t.OBJECT_ID = i.object_id          INNER JOIN msdb.sys.partitions p ON i.object_id = p.OBJECT_ID                                              AND i.index_id = p.index_id          INNER JOIN msdb.sys.allocation_units a ON p.partition_id = a.container_id  WHERE   t.type_desc = 'USER_TABLE'          AND i.index_id <= 1  --- Heap\ CLUSTERED         --AND t.NAME='MYTableName' -- Replace with valid table name GROUP BY sc.name + '.' + t.NAME,          i.[object_id],i.index_id, i.name, p.[Rows]  ORDER BY ( SUM(a.total_pages) * 8 ) / 1024 DESC

Sample output as shown below,

-Thanks,
Kiran

Sql Job : Traffic Light Status - depending on last 3 execution results

Recently came across interesting requirement from business.
Sql jobs keeps loading/refreshing data for projects. If you are not showing last data refresh time in report then end users will not be having idea about data updates. They will start panicking saying reports not showing correct data and you cannot send update to all 1000 users that last refresh is successful.

So we wanted to show traffic light status (Green/Yellow/Red) for job executions on SharePoint site.

In case of any doubt End user can directly visit page and see the respective jobs Status.
Logic was

The logic is as per Business expectations.
Last 1 job failure/Successful àGreen.
Last 2 job failures à Yellow
Last 3 job failures à Red.

Below is the query for getting jobs status

WITH RunTotalTestData AS (       SELECT         CASE             WHEN JobExecutionNo = 1 THEN Run_Status * 100             WHEN JobExecutionNo = 2 THEN Run_Status * 10             WHEN JobExecutionNo = 3 THEN Run_Status * 1         END         RunStat,         k.JobName,         k.StartTime,         k.run_status,         k.ExecutionStatus       FROM (                   SELECT                     name AS JobName,                     CONVERT(datetime, CONVERT(char(8), run_date) + ' ' + STUFF(STUFF((LEFT('000000', 6 - LEN(run_time)) + CONVERT(varchar(6), run_time)), 3, 0, ':'), 6, 0, ':')) AS StartTime,                     DATEADD(MINUTE, DATEDIFF(MINUTE, '0:00:00', CONVERT(time, STUFF(STUFF((LEFT('000000', 6 - LEN(run_duration)) + CONVERT(varchar(6), run_duration)), 3, 0, ':'), 6, 0, ':'))), CONVERT(datetime, CONVERT(char(8), run_date) + ' ' + STUFF(STUFF((LEFT('000000', 6 - LEN(run_time)) + CONVERT(varchar(6), run_time)), 3, 0, ':'), 6, 0, ':'))) AS EndTime,                     CASE run_status                         WHEN 1 THEN 1                         ELSE 0                     END AS run_status,                     CASE run_status                         WHEN 0 THEN 'failed'                         WHEN 1 THEN 'Succeded'                         WHEN 2 THEN 'Retry'                         WHEN 3 THEN 'Cancelled'                         WHEN 4 THEN 'In Progress'                     END AS ExecutionStatus,                     instance_id,                     jh.job_id,                     ROW_NUMBER() OVER (PARTITION BY name ORDER BY run_date DESC, run_time DESC) AS JobExecutionNo                   FROM msdb.dbo.sysjobhistory jh                   INNER JOIN msdb.dbo.sysjobs j ON jh.job_id = j.job_id                   WHERE step_id = 0 AND j.name LIKE ('%Daily%')               )k       WHERE JobExecutionNo < 4 ) SELECT JobName,   CASE     WHEN Last3Exec = 0 THEN 'Red'     WHEN Last3Exec < 10 THEN 'Yellow'     WHEN Last3Exec >= 10 THEN 'Green'   END Flag,   CASE     WHEN Last3Exec = 0 THEN 2     WHEN Last3Exec < 10 THEN 1     WHEN Last3Exec >= 10 THEN 0   END FlagVal,   CASE     WHEN Last3Exec >= 10 THEN 'Job Running all Good'     ELSE 'Job is running very slow due to Performance Issue.'   END AS DisplayMessage,   Last3Exec FROM (SELECT a.JobName,SUM(a.RunStat) AS Last3Exec FROM RunTotalTestData a GROUP BY a.JobName) K GO

It will display Result-set as below,

-Thanks,

Kiran Jagtap.