Skip to main content

Posts

Showing posts from December, 2021

How to TRUNCATE all tables in Microsoft SQL SERVER instance

When we are developing some software and we want to clean the SQL database to simulate a new installation, it would be interesting to have a routine that would erase the contents of all tables. This script performs this task, it deletes the contents of all the tables of the instance that is open in SQL Server Management Studio.  ATTENTION: The TRUNCATE command erases all the contents of the tables and there is no way to restore, only with backup.  Use this script very consciously and in test environments where you really want to do a general clean up in your database.  I am not responsible for the use of this in your bank.  Created by my friend  Airton Junior  , it follows the killer script...  USE MASTER; --TABELA TEMPORÁRIA PARA ARMAZENAR OS BANCOS SEPARADOS PARA LIMPEZA CREATE TABLE #TEMPORARIA (NOMEBANCO VARCHAR(255)); GO --SEPARATION OF DATABASES INSERT INTO #TEMPORARIA (NOMEBANCO) SELECT name FROM master.sys.databases WHERE name LIKE '% BANKNAME %' AND nam

How to search for commands that have already been executed on the Microsoft SQL Server instance.

Hi, With the query below it is possible to view commands that have already been executed in the SQL SERVER instance. SELECT DMExQryStats.last_execution_time AS [Executed At], DMExSQLTxt.text AS [Query] FROM sys.dm_exec_query_stats AS DMExQryStats CROSS APPLY sys.dm_exec_sql_text(DMExQryStats.sql_handle) AS DMExSQLTxt ORDER BY DMExQryStats.last_execution_time DESC If you want to check for especificios commands, add the WHERE clause to search the command content. Example: If you want to search all UPDATES performed in your database, add this clause WHERE WHERE lower(substring(DMExSQLTxt.text,1,6))='update'

How to create a query that returns the first day of the month and the last day of the month using GETDATE() Microsoft SQL SERVER.

 QUERY:  SELECT DATEADD(month, DATEDIFF(month, -1, getdate()) - 2, 0) as Primeirodiadomes,     DATEADD(ss, -1, DATEADD(month, DATEDIFF(month, 0, getdate()), 0)) as Ultimodiadomes RESULT :