How to give latest database name in Shrink database sql script?

Question

I have used below script to shrink database. I know even if not best practice but have requirement to do shrink. Is there any way to get latest database name every time into the script to shrink. at the moment I am doing manually but need to do automatically. Please can anyone suggest.

USE [Sales_backup_2016_08_10_203001_7966467]
ALTER DATABASE [Sales_backup_2016_08_10_203001_7966467]
SET RECOVERY SIMPLE
DBCC SHRINKFILE (Sales_Data)
GO
USE [Sales_backup_2016_08_10_203001_7966467]
DBCC SHRINKFILE (Sales_Log,0)
GO
ALTER DATABASE [Sales_backup_2016_08_10_203001_7966467]
SET RECOVERY SIMPLE
DBCC SHRINKFILE (Sales_Log,0)
GO

Answer 1

This Statement might help you

SELECT TOP 1 NAME FROM SYS.DATABASES WHERE name<>'tempdb' ORDER BY create_date DESC

Answer 2

Here are couple of methods you could use:

Method A: Dynamic SQL

This query, which is a little old and could be improved, writes the shrink statement to a variable. The variable is then executed.

For brevity, I've not included the full shrink statement. Edit the line that starts + 'USE ' + QUOTENAME(d.name)....

This query is best viewed in Results to Text mode (Ctrl + T), as it uses line breaks to make the content easier to read (for us humans).

    DECLARE @Qry NVARCHAR(MAX)  = ''        -- The dynamic SQL is written to this var.

    -- Populate @Qry.
    SELECT  
        @Qry = @Qry 
            + 'USE ' + QUOTENAME(d.name) + '; ALTER DATABASE ' + QUOTENAME(d.name) + ' ...;'
            + CHAR(10) 
    FROM    
        sys.Databases d
    WHERE   
        d.[State] =0 
        AND LOWER(d.Name) <> 'tempdb'
    ;

/* Enable the first line to run.
 * Disable the bottom line, when you are happy with the script.
 */
-- EXECUTE sp_ExecuteSQL @Qry
SELECT @Qry;

Method B: Maintencance Tasks

Integration Services includes a number of tasks for performing maintenance work. One of these is the Shrink Database Task. SQL Server includes a wizard that can help you write and schedule your job.

Both methods can be scheduled using the SQL Agent.

EDIT

Corrected query, which contained an undeclared variable.