SQL SERVER DBA, Linux and Azure

T-SQL query to get tables size in GB with no of rows in SQL server

Hi All,

Please find the below T-SQL script to get the tables size in GB with number of rows in SQL Server Database.


Use DB_Name;  --Change your database here

go

SELECT 

 s.name + '.' + t.Name AS [Table Name],

 part.rows AS [Total Rows In Table - Modified],

 CAST((SUM( DISTINCT au.Total_pages) * 8 ) / 1024.000 / 1024.000 AS NUMERIC(18, 3)) 

 AS [Table's Total Space In GB]

FROM 

 SYS.Tables t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id

 INNER JOIN SYS.Indexes idx ON t.Object_id = idx.Object_id

 INNER JOIN SYS.Partitions part ON idx.Object_id = part.Object_id 

                    AND idx.Index_id = part.Index_id

 INNER JOIN SYS.Allocation_units au ON part.Partition_id = au.Container_id

 INNER JOIN SYS.Filegroups fGrp ON idx.Data_space_id = fGrp.Data_space_id

 INNER JOIN SYS.Database_files Df ON Df.Data_space_id = fGrp.Data_space_id

WHERE t.Is_ms_shipped = 0 AND idx.Object_id > 255 

GROUP BY t.Name, s.name, part.rows

ORDER BY [Table's Total Space In GB] DESC


Retrieve SSRS report schedule info through SQL

 --Retrieve SSRS report schedule info through SQL

Grabs information about what reports in SSRS are scheduled, when are they due to be sent out, 

report parameters and any errors in the last run etc.

Please note that the XPath on the [Parameters] column varies based on what parameters are setup in your own report. 

Please adjust them for your own purpose. Also the 'Prompt' value for a parameter is not stored, so you might have to 

join back to the relevant table for a friendly name.

Also there is additional info in the [ExtensionSetting].

=============================================================

  SELECT

[Locale],

[InactiveFlags],

'Next Run Date' = CASE next_run_date

WHEN 0 THEN null

ELSE

substring(convert(varchar(15),next_run_date),1,4) + '/' +

substring(convert(varchar(15),next_run_date),5,2) + '/' +

substring(convert(varchar(15),next_run_date),7,2)

END,

'Next Run Time' = isnull(CASE len(next_run_time)

WHEN 3 THEN cast('00:0'

+ Left(right(next_run_time,3),1)

+':' + right(next_run_time,2) as char (8))

WHEN 4 THEN cast('00:'

+ Left(right(next_run_time,4),2)

+':' + right(next_run_time,2) as char (8))

WHEN 5 THEN cast('0' + Left(right(next_run_time,5),1)

+':' + Left(right(next_run_time,4),2)

+':' + right(next_run_time,2) as char (8))

WHEN 6 THEN cast(Left(right(next_run_time,6),2)

+':' + Left(right(next_run_time,4),2)

+':' + right(next_run_time,2) as char (8))

END,'NA'),

Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="TO"])[1]','nvarchar(50)') as [To]

,Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="RenderFormat"])[1]','nvarchar(50)') as [Render Format]

,Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="Subject"])[1]','nvarchar(50)') as [Subject]

        ---Example report parameters: StartDateMacro, EndDateMacro & Currency.

,Convert(XML,[Parameters]).value('(//ParameterValue/Value[../Name="StartDateMacro"])[1]','nvarchar(50)') as [Start Date]

,Convert(XML,[Parameters]).value('(//ParameterValue/Value[../Name="EndDateMacro"])[1]','nvarchar(50)') as [End Date]

,Convert(XML,[Parameters]).value('(//ParameterValue/Value[../Name="Currency"])[1]','nvarchar(50)') as [Currency]

,[ModifiedDate]

,S.[Description]

,[LastStatus]

,[EventType]

,[LastRunTime]

,[DeliveryExtension]

,[Version]

FROM [ReportServer$LIVE].[dbo].[Subscriptions] S

INNER JOIN ReportServer$LIVE.dbo.ReportSchedule R ON S.SubscriptionID = R.SubscriptionID

INNER JOIN msdb.dbo.sysjobs J ON Convert(nvarchar(128),R.ScheduleID) = J.name

INNER JOIN msdb.dbo.sysjobschedules JS ON J.job_id = JS.job_id


TempDB-Move TempDB Script in SQL Server

Hi All,

Please find the below mentioned script to generate the Move tempdb script in SQL Server 

 --Move_tempdb.sql

/*

Author: Brent Ozar

Original link: https://www.brentozar.com/archive/2017/11/move-tempdb-another-drive-folder/

*/

USE MASTER

GO

SELECT 'ALTER DATABASE tempdb MODIFY FILE (NAME = [' + f.name + '],'

    + ' FILENAME = ''Z:\MSSQL\DATA\' + f.name

    + CASE WHEN f.type = 1 THEN '.ldf' ELSE '.mdf' END

    + ''');'

FROM sys.master_files f

WHERE f.database_id = DB_ID(N'tempdb');