Script SQL Server

Reading XML in SQL (Attributes)

Reads the XML attributes: =================================================================== DECLARE @idoc int, @doc varchar(1000);   SET @doc ='<ISKeyValueList version="1.00"><Item type="varchar(20)" key="VehicleSecurityPending">false</Item><Item type="Integer"...

Parse and Transform JSON Data with OPENJSON (SQL Server)

Option 1 - OPENJSON with the default output     --//T-SQL Code Example 01  --============================================= DECLARE @json NVARCHAR(MAX) SET @json='{"name":"John","surname":"Doe","age":45,"skills":["SQL","C#","MVC"]}'; SELECT * FROM OPENJSON(@json); Option 2 -...

How improve the performance in SQL? Identifying and fixing the missing indexes - Part 01

This is the first session to show up how you can identify the missing indexes on your project. First of all, I've developed a new temporary table for tests:  --// Create a temporary table --============================================================ Create table tbLabIndexMissing(id int,...

List the Missing Backups

USE [DBA] GO     SET ANSI_NULLS ON GO   SET QUOTED_IDENTIFIER ON GO     create   view [dbo].[vwServerBackup] as With backupBase  As (    Select  sd.name     From msdb..backupmediafamily...

Personal Logshipping - Database replication for Reports and Contingency - Loading balance Performance

I had decided to build a manual Logshipping instead of using the built-in MS LogShipping for my better control in terms of current connection controls.      --// Create tbLogShippingControl - I built a table to control the...

Migration - Backup database with Read-Only and OffLine

DECLARE @name VARCHAR(50) -- database name   DECLARE @path VARCHAR(256) -- path for backup files   DECLARE @fileName VARCHAR(256) -- filename for backup   DECLARE @fileDate VARCHAR(20) -- used for file name DECLARE @strSQL NVARCHAR(200) -- used for file...

Migration - Check the remaining time to complete the backup - Progress Status

SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete) AS [Percent Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time], CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed...

Migration - Simple Restore Sample

    --// Remove the Users Connections and detach the database Exec DBA..sp_DBA_KillDB DB     restore filelistonly from disk = '\\backupServer\DB_20181019.BAK'  go   restore database DB from disk = '\\backupServer\DB_20181019.BAK'  with move...

Migration - Makes unavailable All or Selected the databases (Read-Only / OffLine)

DECLARE @name VARCHAR(50) -- database name   DECLARE @strSQL NVARCHAR(200) -- used for file name     DECLARE db_cursor CURSOR READ_ONLY FOR   SELECT name  FROM master.dbo.sysdatabases  WHERE (name like 'Fin%'  or      ...

Kill all the connections from a specific database

USE [DBA] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER proc [dbo].[sp_DBA_KillDB] ( @DatabaseName varchar(60) )   As   DECLARE @dbid tinyint DECLARE @spid smallint DECLARE @exec_str varchar(10)              ...

1 | 2 | 3 | 4 | 5 >>

Crie um site com

  • Totalmente GRÁTIS
  • Centenas de templates
  • Todo em português

Este site foi criado com Webnode. Crie um grátis para você também!