SQL SERVER DBA, Linux and Azure

Delete Bakups older than 1 day in SQL Server

--Delete Bakups older than 1 day in SQL Server

DECLARE @DeleteDate datetime

 SET @DeleteDate = DateAdd(day, -1, GetDate()) 

  EXECUTE master.sys.xp_delete_file 0, -- FileTypeSelected (0 = FileBackup, 1 = FileReport)

   N'C:\Backup\msdb', -- folder path (trailing slash)

    N'bak', -- file extension which needs to be deleted (no dot)

     @DeleteDate, -- date prior which to delete 

     1 -- subfolder flag (1 = include files in first subfolder level, 0 = not) 

Generate and change all SQL databases to a different compatibility level - Alter SQL Compatibility Level


SELECT name, compatibility_level  

FROM sys.databases where compatibility_level not in(120) -- SQL Server 2012 compatibility_level is 120

---------------------------------

SELECT name, compatibility_level  

FROM sys.databases 

---------------------------

/* Start Of Code */


DECLARE @SQL VARCHAR(max)  = ''

,             @CompLevel int = 120 -- Need to change the compatibility_level as per SQL Server version


SELECT @SQL += 'ALTER DATABASE ' + quotename(NAME) + ' SET COMPATIBILITY_LEVEL = ' + cast(@CompLevel as char (3)) + ';' + CHAR(10) + CHAR(13)

FROM sys.databases

WHERE 

--COMPATIBILITY_LEVEL not in(120) 

--and 

 compatibility_level <> @CompLevel


PRINT @SQL

--EXEC (@SQL)



/* End Of Code */

Finding an object in server in SQL Server using SP_MSFOREACHDB

 Today one of my friend asked me that she has created a stored procedure in a database, suddenly she has recognized she has forgotten the database in which the procedure was created. We suggested him to use the below query which searches each and every database in the server and returns the database name and the object name that is specified in where clause.

SP_MSFOREACHDB searches the objects in all databases in the server.

Example:

SP_MSFOREACHDB
'
USE ?
select
    ''?'' Database_Name
   , Name Object_name
from sys.procedures
WHERE name LIKE ''USP_Test''