Composable SQL Prompt snippets for datetime filters in SSMS. Type t_ and Tab to see the full menu.
These are small building blocks that snap together: declare with t_vars, fill @EndTime with a now snippet, fill @StartTime with t_last or a boundary snippet. Chain t_tz for timezone conversion.
.json files
| ; -- Previous statement must be properly terminated | |
| WITH detailPerPlan AS | |
| ( | |
| SELECT | |
| defs.database_id, | |
| defs.object_id, | |
| defs.total_worker_time, | |
| defs.execution_count, | |
| defs.total_elapsed_time, | |
| defs.total_elapsed_time / defs.execution_count AS avg_elapsed_time, |
| ; -- Previous statement must be properly terminated | |
| WITH tableRowCount | |
| AS | |
| ( | |
| SELECT | |
| ddps.object_id | |
| , SUM(ddps.row_count) AS rowCnt | |
| , SUM(ddps.used_page_count) * 8. / 1024. / 1024. AS usedSize | |
| FROM sys.dm_db_partition_stats ddps | |
| WHERE ddps.index_id < 2 |
| CREATE TABLE #xe2022 ( [name] nvarchar(256), [description] nvarchar(3072) ) | |
| INSERT INTO #xe2022 ([name], [description]) | |
| VALUES | |
| ( N'certificate_report', N'Certificate info.' ), | |
| ( N'azure_active_directory_service_failure', N'Occurs when we encounter a failure in AzureActiveDirectoryService layer, when performing MSODS lookup during Login and Create Login/User workflow.' ), | |
| ( N'aad_build_federated_context', N'Occurs when we attempt to build federated context.' ), | |
| ( N'aad_signing_key_refresh', N'Occurs when we attempt to refresh signing keys from Azure Active Directory, to update the in-memory cache.' ), | |
| ( N'auth_fw_cache_lookup_failure', N'This event is generated when the xodbc cache firewall lookup fails.' ), | |
| ( N'auth_fw_cache_lookup_success', N'This event is generated when the xodbc cache firewall lookup succeeds.' ), | |
| ( N'connection_attempt_failure_system_error', N'Connection attempt metrics' ), |
| ; WITH allReferences | |
| AS | |
| ( | |
| SELECT | |
| dsre.referenced_id AS Id | |
| , 0 AS lvl | |
| , CAST('.' + CAST(dsre.referenced_id AS VARCHAR(MAX)) + '.' AS VARCHAR(max)) AS path | |
| , CAST(CAST(1 AS BINARY(2)) AS VARBINARY(MAX)) AS sorthPath | |
| , 0 AS cycle | |
| FROM sys.dm_sql_referenced_entities('SEARCH.OBJECT', 'Object') dsre /* <--- insert the object in a 'schema.object' format */ |
| DECLARE @stackOrFrame xml = 'Paste the <frames></frames> here' | |
| ;WITH | |
| xmlShred AS | |
| ( | |
| SELECT | |
| COALESCE | |
| ( | |
| CONVERT(varbinary(64), f.n.value('.[1]/@handle', 'varchar(max)'), 1), | |
| CONVERT(varbinary(64), f.n.value('.[1]/@sqlhandle', 'varchar(max)'), 1) |
| /* | |
| SELECT * FROM sys.time_zone_info WHERE [name] LIKE '%Europe%' /* Find your time zone */ | |
| */ | |
| SELECT DATETIMEFROMPARTS(2021, 7, 28, 12, 45, 0, 0) /* Pick your date */ | |
| AT TIME ZONE 'UTC' /* Change to source time zone */ | |
| AT TIME ZONE 'Central European Standard Time' /* Change to target time zone */ |
| /* xml variable, no TYPE directive */ | |
| DECLARE @xmlNoType xml | |
| SET @xmlNoType = | |
| ( | |
| SELECT * | |
| FROM sys.all_objects | |
| FOR XML PATH(''), ROOT ('Document') | |
| ) | |
| SELECT (DATALENGTH(@xmlNoType) / 1024.0) / 1024. AS SizeMB | |
| GO |