Find Most executed queries in SQL Server

SELECT TOP 100 QueryState.execution_count, OBJECT_NAME(objectid), query_text = SUBSTRING( qt.text, QueryState.statement_start_offset / 2, ( CASE WHEN QueryState.statement_end_offset = -1 THEN len(convert(nvarchar(max), qt.text)) * 2 ELSE QueryState.statement_end_offset END – QueryState.statement_start_offset ) / 2 ), qt.dbid, dbname =…

List all triggers in SQL Server database

SELECT [so].[name] AS [Name], User_name([so].[uid]) AS [Owner], User_name([so2].[uid]) AS [Schema], Object_name([so].[parent_obj]) AS [TableName], Objectproperty([so].[id], ‘execisupdatetrigger’) AS [IsUpdate], Objectproperty([so].[id], ‘execisdeletetrigger’) AS [IsDelete], Objectproperty([so].[id], ‘execisinserttrigger’) AS [IsInsert], Objectproperty([so].[id], ‘execisaftertrigger’) AS [IsAfter], Objectproperty([so].[id], ‘execisinsteadoftrigger’) AS [IsInsteadOf], Objectproperty([so].[id], ‘execistriggerdisabled’)…

T-SQL – Find running queries

Here’s a little script in T-SQL to find queries running in our SQL SERVER database: SELECT sqltext.TEXT, res.session_id, res.status, res.command, res.cpu_time, res.total_elapsed_time FROM sys.dm_exec_requests res CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext This script is also useful…

SQL Server – How create a function

CREATE FUNCTION dbo.MyFunction (@input VARCHAR(200)) RETURNS VARCHAR(200) AS BEGIN DECLARE @WebSite VARCHAR(250) SET @WebSite = @Input SET @WebSite = REPLACE(@WebSite , ‘https://’, ”) SET @WebSite = REPLACE(@WebSite , ‘http://’, ”) SET @WebSite = REPLACE(@WebSite ,…