Important SQL Server Management Studio Shortcuts (SSMS)

Sql Server Management Studio Shortcuts

Execute Query : F5
Object Explorer : F8
New File : CTRL + N
Open File : CTRL + O
Output in Grid : CTRL + D  
Output in Text : CTRL + T 
Search : CTRL + F
Replace : CTRL + H
Estimated Execution Plan : Ctrl + L
Actual Execution Plan : Ctrl + M
Cancel Executing Query : ALT + BREAK
GoToLine : Ctrl +G
Undo : Ctrl +Z
Redo : Ctrl +Shift + z
SaveAll : Ctrl +Shift + s
Query Designer : CTRL +SHIFT+Q
Solution Explorer : Ctrl + Alt + L
Registered Server : Ctrl + Alt + G
Important SQL Server Management Studio Shortcuts (SSMS) was last modified: October 1st, 2017 by Justin Antony

Find SSRS Subscription Error

Find SSRS Subscription Error

SSRS subscription error details can be fetched from the system table using the following code.

SELECT 
*
FROM 
DBO.SUBSCRIPTIONS AS S
INNER JOIN 
DBO.REPORTSCHEDULE RS ON S.SUBSCRIPTIONID = RS.SUBSCRIPTIONID
INNER JOIN 
DBO.[CATALOG] AS C ON C.ITEMID = S.REPORT_OID
INNER JOIN 
DBO.[USERS] U ON C.CREATEDBYID = U.USERID
INNER JOIN 
DBO.SCHEDULE SC  ON RS.SCHEDULEID = SC.SCHEDULEID
INNER JOIN 
MSDB.DBO.SYSJOBS SJ  ON CAST(SC.SCHEDULEID AS SYSNAME) = SJ.NAME
WHERE
S.LASTSTATUS LIKE '%FAIL%' 
OR S.LASTSTATUS LIKE '%PENDING%'
Find SSRS Subscription Error was last modified: October 1st, 2017 by Justin Antony

Find all Table record count based on its schema name

Table record count based on schema name

The following query will list all the tables belongs to schema1 and schema2 along with their record count information. Create a table RECCNT before running the following query.

select  ' INSERT INTO RECCNT select '''+ s.name+ '.' + o.name  + ''', count(*) from ' + s.name+ '.' + o.name from sys.objects O INNER JOIN sys.schemas S
ON O.schema_id = S.schema_id
where type = 'u' and s.name in ('SCHEMA1','SCHEMA2' )
Order by s.name
GO
select * from RECCNT
Find all Table record count based on its schema name was last modified: October 1st, 2017 by Justin Antony

Get Job Category level information from SQL Server system table

Get the sql server job category level information using the following query.

select * from msdb.dbo.sysjobs jb
join msdb.dbo.sysjobsteps js 
on jb.job_id = js.job_id
join msdb.dbo.syscategories jc 
on jb.category_id = jc.category_id
Get Job Category level information from SQL Server system table was last modified: October 1st, 2017 by Justin Antony