ignas-sakalauskas

-- Database size in MB
SELECT
  SUM(reserved_page_count) * 8.0 / 1024 AS 'MB'
FROM sys.dm_db_partition_stats

-- https://ignas.me/tech/sql-database-tables-sizes/

ignas-sakalauskas

using System.Net;
using Microsoft.AspNetCore.Mvc;

namespace WebApplication4.Controllers
{
    [Route("api/[controller]")]
    public class ValuesController : Controller
    {
        [HttpGet]
        public IActionResult Get()

ignas-sakalauskas

USE [YourDBName]
 
SELECT * FROM sys.Tables
SELECT * FROM sysobjects WHERE xtype='u'

-- https://ignas.me/tech/select-all-tables-database/

ignas-sakalauskas

USE master

ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE YourDatabaseName 

-- https://ignas.me/tech/force-drop-database-when-in-use/

ignas-sakalauskas

-- CPU intensive queries

SELECT TOP 50
qs.total_worker_time/qs.execution_count as [Avg CPU Time],
SUBSTRING(qt.text,qs.statement_start_offset/2,
(case when qs.statement_end_offset = -1
then len(convert(nvarchar(max), qt.text)) * 2
else qs.statement_end_offset end -qs.statement_start_offset)/2)
as query_text,
qt.dbid, dbname=db_name(qt.dbid),

ignas-sakalauskas

USE YourDatabaseName
GO
 
PRINT 'Checking for the existence of this procedure'
IF (SELECT OBJECT_ID('sp_generate_inserts','P')) IS NOT NULL
 BEGIN
 PRINT 'Procedure already exists. So, dropping it'
 DROP PROC sp_generate_inserts
 END
GO

ignas-sakalauskas

BEGIN
    BEGIN TRY
        SET NOCOUNT ON
        SET XACT_ABORT ON
          
        -- Code Which Doesn't Require Transaction
        -- e.g. initialize variables
          
        BEGIN TRANSACTION
 

ignas-sakalauskas

SET NOCOUNT ON;
 
DECLARE @rows int = 1;
DECLARE @now datetime = GETDATE();
 
WHILE @rows > 0
BEGIN
  BEGIN TRANSACTION;
 
    DELETE TOP (500) [ASPState].dbo.ASPStateTempSessions

ignas-sakalauskas

CREATE PROCEDURE [dbo].[Maintenance_RefreshViews]
AS

--Refresh the underlying metadata of all views
DECLARE @viewName AS VARCHAR(255)

DECLARE listOfViews CURSOR FOR
SELECT [name] FROM sysobjects WHERE xtype = 'V' ORDER BY [name]

OPEN listOfViews

ignas-sakalauskas

SELECT so.name as TableName, ddps.row_count as [RowCount]
FROM sys.objects so
JOIN sys.indexes si ON si.OBJECT_ID = so.OBJECT_ID
JOIN sys.dm_db_partition_stats AS ddps ON si.OBJECT_ID = ddps.OBJECT_ID AND si.index_id = ddps.index_id
WHERE si.index_id < 2 AND so.is_ms_shipped = 0
ORDER BY ddps.row_count DESC

-- https://ignas.me/tech/sql-quick-row-count/