Move Database locations with detach/attach

USE MASTER;

GO

-- Take database in single user mode -- if you are facing errors

-- This may terminate your active transactions for database

ALTER DATABASE TestDB

SET SINGLE_USER

WITH ROLLBACK IMMEDIATE;

GO

-- Detach DB

EXEC MASTER.dbo.sp_detach_db @dbname = N'TestDB'

GO

/*

--

-- Move MDF File from Loc1 to Loc2

--

*/

-- Re-Attached DB

CREATE DATABASE [TestDB] ON

( FILENAME = N'F:\loc2\TestDB.mdf' ),

( FILENAME = N'G:\loc2\TestDB_log.ldf' )

FOR ATTACH

GO

/* -------------------------- */

Move the TEMP Db's from one place to another

USE master

GO

ALTER DATABASE tempdb

        MODIFY FILE (NAME = tempdev, FILENAME = 'C:\tempdb2005.mdf')

GO

ALTER DATABASE tempdb

        MODIFY FILE (NAME = templog, FILENAME = 'C:\tempdb2005.ldf')

GO

/* -------------------------- */

Move your databases without a Detach, but a backup/restore methodology

/* 

--

--Quote: Backups are very easy, and protect you from all of the above [in link below].

*/

/*

BACKUP DATABASE [TestDB] TO DISK = 'x:\backups\TestDB.bak'

  WITH COPY_ONLY, INIT, COMPRESSION;

*/

/*

--

--Restoring is similarly easy, though if the file structures aren't the same,

--you may need to use the WITH MOVE arguments. This effort is worth it.

--

*/

/*

RESTORE TestDB dbname FROM DISK = 'x:\backups\TestDB.bak'

  WITH MOVE 'TestDB_Data' TO 'D:\MSSQL\Data\TestDB.mdf',

   MOVE 'TestDB_Log'  TO 'E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\TestDB.ldf';

--

*/

--https://dba.stackexchange.com/questions/61810/what-are-the-risks-of-copying-mdf-ldf-files-without-detaching-the-db-first

Create Delilmiated Strings with COALESCE

DECLARE @EmployeeList varchar(100)


SELECT @EmployeeList = COALESCE(@EmployeeList + ', ', '') + 

   CAST(Emp_UniqueID AS varchar(5))

FROM SalesCallsEmployees

WHERE SalCal_UniqueID = 1


SELECT @EmployeeList


https://www.sqlteam.com/articles/using-coalesce-to-build-comma-delimited-string

Cycle through a Formatted List (ie ",")

declare @S varchar(MAX)

set @S = '1,2,3,4,5'

while len(@S) > 0

begin
  --print left(@S, charindex(',', @S+',')-1)
  exec YourSP left(@S, charindex(',', @S+',')-1)
  set @S = stuff(@S, 1, charindex(',', @S+','), '')

end

https://stackoverflow.com/questions/10031658/comma-separated-list-in-sql