Arquivo do Autor: admin

Selecionar banco de dados e tamanhos

SELECT 
      database_name = DB_NAME(database_id)
    , log_size_mb = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
    , row_size_mb = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
    , total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))
FROM sys.master_files WITH(NOWAIT)
WHERE database_id = DB_ID() -- for current db 
GROUP BY database_id

Trabalhando com Migrations

  1. Instalar através do Nuget Package o pacote Microsoft.EntityFrameworkCore.Tools
  2. Instalar o provedor de acesso
    1. Microsoft.EntityFrameworkCore.SqlServer
    2. Add-Migration Inicial
      1. Inicial é o nome dado ao Migration, sendo esse o inicial, os Migrations devem ser nomeados.
    3. Update-Database

Executar bloco de comando no PostgreSQL

Bloco nomeado

DO $$ 
	<<first_block>>
DECLARE
  film_count integer := 0;
BEGIN
   -- get the number of films
   SELECT COUNT(*) 
   INTO film_count
   FROM "Lancamento";
   -- display a message
   RAISE NOTICE 'The number of films is %', film_count;
END first_block $$;

Bloco anonimo

DO $$ 
DECLARE
  film_count INTEGER := 0;
BEGIN
   -- get the number of films
   SELECT COUNT(*) INTO film_count FROM "Lancamento";
   -- display a message
   RAISE NOTICE 'The number of films is %', film_count;
END
$$;

Códigos para conversão de datas no SQL Server

Código Formato
0 Oct 9 2020 2:20PM
1 10/09/20
2 20.10.09
3 09/10/20
4 09.10.20
5 09-10-20
6 09 Oct 20
7 Oct 09, 20
8 14:20:02
9 Oct 9 2020 2:20:02:400PM
10 10-09-20
11 20/10/09
12 201009
13 09 Oct 2020 14:20:02:400
14 14:20:02:400
20 2020-10-09 14:20:02
21 2020-10-09 14:20:02.400
22 10/09/20 2:20:02 PM
23 2020-10-09
24 14:20:02
25 2020-10-09 14:20:02.400
100 Oct 9 2020 2:20PM
101 10/09/2020
102 2020.10.09
103 09/10/2020
104 09.10.2020
105 09-10-2020
106 09 Oct 2020
107 Oct 09, 2020
108 14:20:02
109 Oct 9 2020 2:20:02:407PM
110 10-09-2020
111 2020/10/09
112 20201009
113 09 Oct 2020 14:20:02:410
114 14:20:02:410
120 2020-10-09 14:20:02
121 2020-10-09 14:20:02.410
126 2020-10-09T14:20:02.410
127 2020-10-09T14:20:02.410
130 22 ??? 1442 2:20:02:410PM
131 22/02/1442 2:20:02:410PM

Armazenar retorno do sp_who2 em tabela temporária

CREATE TABLE #sp_who2
(
	SPID INT,Status VARCHAR(255),
	Login  VARCHAR(255),HostName  VARCHAR(255),
	BlkBy  VARCHAR(255),DBName  VARCHAR(255),
	Command VARCHAR(255),CPUTime INT,
	DiskIO INT,LastBatch VARCHAR(255),
	ProgramName VARCHAR(255),SPID2 INT,
	REQUESTID INT
)

INSERT INTO #sp_who2 EXEC sp_who2

SELECT
		*
FROM
		#sp_who2
WHERE
		DBName <> 'master'
		AND DBName = 'BANCO_DE_DADOS'
		AND ProgramName <> 'Microsoft SQL Server Management Studio - Query'
ORDER BY
		DBName ASC

DROP TABLE #sp_who2