SQL Server: alter physical file names

select 'ALTER DATABASE ' + d.name +' MODIFY FILE ( NAME = ' + f.name + ', FILENAME = ''' + f.physical_name +''' );'

from sys.master_files f, sys.databases d

where f.database_id > 4

and f.database_id = d.database_id

and f.physical_name like 'C:\ClusterStorage\Test UDB\Data\%'

order by d.name

SQL Server set databases offline

select 'ALTER DATABASE [' + A.Name + '] SET OFFLINE WITH ROLLBACK IMMEDIATE;'

from sys.databases a

where a.database_id > 4

and state_desc ='ONLINE'

and a.name not in ('SSISDB')

order by a.name

SQL Server set databases online

select 'ALTER DATABASE [' + A.Name + '] SET ONLINE;'

from sys.databases a

where a.database_id > 4

and state_desc ='OFFLINE'

and a.name not in ('SSISDB')

order by a.name

SQL Server ETA of running SQL e.g. dbcc

 select  T.text, R.Status, R.Command, DatabaseName = db_name(R.database_id)
        , R.cpu_time, R.total_elapsed_time, R.percent_complete
from    sys.dm_exec_requests R
        cross apply sys.dm_exec_sql_text(R.sql_handle) T

SQL Server Agent Job Startup to remove a tempdb file

 USE [tempdb]
GO
DBCC SHRINKFILE (N'temp5', EMPTYFILE)
GO
ALTER DATABASE [tempdb] REMOVE FILE [temp5]
GO

SQL Server Detach / Attach all User Databases Script

 USE [master];
GO
DECLARE @database NVARCHAR(200) ,
    @cmd NVARCHAR(1000) ,
    @detach_cmd NVARCHAR(4000) ,
    @attach_cmd NVARCHAR(4000) ,
    @file NVARCHAR(1000) ,
    @i INT ,
    @DetachOrAttach BIT;
SET @DetachOrAttach = 1;
-- 1 Detach 0 - Attach
-- 1 Generates Detach Script
-- 0 Generates Attach Script
DECLARE dbname_cur CURSOR STATIC LOCAL FORWARD_ONLY
FOR
    SELECT  RTRIM(LTRIM([name]))
    FROM    sys.databases
    WHERE   database_id > 4;
 -- No system databases
OPEN dbname_cur
FETCH NEXT FROM dbname_cur INTO @database
WHILE @@FETCH_STATUS = 0 
    BEGIN
        SELECT  @i = 1;
        SET @attach_cmd = '-- ' + QUOTENAME(@database) + CHAR(10)
            + 'EXEC sp_attach_db @dbname = ''' + @database + '''' + CHAR(10);
      -- Change skip checks to false if you want to update statistics before you detach.
        SET @detach_cmd = '-- ' + QUOTENAME(@database) + CHAR(10)
            + 'EXEC sp_detach_db @dbname = ''' + @database
            + ''' , @skipchecks = ''true'';' + CHAR(10);
      -- Get a list of files for the database
        DECLARE dbfiles_cur CURSOR STATIC LOCAL FORWARD_ONLY
        FOR
            SELECT  physical_name
            FROM    sys.master_files
            WHERE   database_id = DB_ID(@database)
            ORDER BY [file_id];
        OPEN dbfiles_cur
        FETCH NEXT FROM dbfiles_cur INTO @file
        WHILE @@FETCH_STATUS = 0 
            BEGIN
                SET @attach_cmd = @attach_cmd + '    ,@filename'
                    + CAST(@i AS NVARCHAR(10)) + ' = ''' + @file + ''''
                    + CHAR(10);
                SET @i = @i + 1;
                FETCH NEXT FROM dbfiles_cur INTO @file
            END
        CLOSE dbfiles_cur;
        DEALLOCATE dbfiles_cur;
        IF ( @DetachOrAttach = 0 ) 
            BEGIN
            -- Output attach script
                PRINT @attach_cmd;
            END
        ELSE -- Output detach script
            PRINT @detach_cmd;
        FETCH NEXT FROM dbname_cur INTO @database
    END
CLOSE dbname_cur;
DEALLOCATE dbname_cur;