SET NOCOUNT ON;
CREATE TABLE #fixdrv ( Name sysname NOT NULL, Size int NOT NULL, iType int NULL, Type sysname NULL );
INSERT #fixdrv (Name, Size) EXECUTE master.dbo.xp_fixeddrives;
UPDATE #fixdrv set Type = 'Fixed', iType = 1 WHERE Type IS NULL;

INSERT #fixdrv (Name, Size) EXECUTE master.dbo.xp_fixeddrives 2;
UPDATE #fixdrv set Type = 'Removable', iType = 4 WHERE Type IS NULL;
INSERT #fixdrv (Name, Size) EXECUTE master.dbo.xp_fixeddrives 3;
UPDATE #fixdrv set Type = 'CD-ROM', iType = 2 WHERE Type IS NULL;
UPDATE #fixdrv set Name = Name + ':\';
SELECT Name AS [Name], CONVERT(decimal(10,3), Size / 1000.0) AS [Size], iType AS [DiskType], Type AS [Type]
  FROM #fixdrv
  ORDER BY Name, iType;
DROP TABLE #fixdrv;

DECLARE @depth int = 1;    -- livello di scansione - 0= tutto il file system
DECLARE @showFiles int = 0; -- 0= non mostrare File - 1= mostra file
EXECUTE master.dbo.xp_dirtree 'C:\', @depth, @showFiles;
GO
--<------- secondo batch che esegue la specifica richiesta
PRINT 'con questi presupposti, possiamo passare come parametro il disco desiderato ed il disco di default;';
DECLARE @DiskRequested varchar(3);
DECLARE @DiskDefault varchar(3);

DECLARE @UseDisk varchar(3);
DECLARE @Folder varchar (50) = 'Backup';
DECLARE @chkdirectory varchar(500) = '';
DECLARE @folder_exists int = 0;
DECLARE @file_results TABLE (
  file_exists int,
    file_is_a_directory int,
    parent_directory_exists int
    );

SELECT @DiskDefault = 'C:\', @DiskRequested = 'D:\';

PRINT 'di nuovo, carichiamo l''albero del file system con i dischi disponibili';
CREATE TABLE #fixdrv ( Name sysname NOT NULL, Size int NOT NULL, iType int NULL, Type sysname NULL );
INSERT #fixdrv (Name, Size) EXECUTE master.dbo.xp_fixeddrives;
UPDATE #fixdrv set Type = 'Fixed', iType = 1 WHERE Type IS NULL;

PRINT 'escludiamo Removibili e CD-ROM';
-- escludi removibili
INSERT #fixdrv (Name, Size) EXECUTE master.dbo.xp_fixeddrives 2;
UPDATE #fixdrv set Type = 'Removable', iType = 4 WHERE Type IS NULL;
-- escludi CD-ROM
INSERT #fixdrv (Name, Size) EXECUTE master.dbo.xp_fixeddrives 3;
UPDATE #fixdrv set Type = 'CD-ROM', iType = 2 WHERE Type IS NULL;
UPDATE #fixdrv set Name = Name + ':\';

PRINT 'con una JOIN tra il set dei dischi disponibili ed il set di disco desiderato e predefinito,';
PRINT 'otteniamo il disco con precedenza maggiore';

WITH ctePreferences AS (
  SELECT @DiskRequested AS [Disk], 0 AS [Precedence]
  UNION
  SELECT @DiskDefault AS [Disk], 1 AS [Precedence]
  ),
cteResult AS (
  SELECT TOP(1) Name AS [Name], CONVERT(decimal(10,3), Size / 1000.0) AS [Size], iType AS [DiskType], Type AS [Type]
    FROM #fixdrv d
    JOIN ctePreferences c ON c.[Disk] = d.[Name]
    WHERE d.iType = 1 -- solo dishi fissi
    ORDER BY c.[Precedence]
  )
  SELECT @UseDisk = c.Name
    FROM cteResult c;

DROP TABLE #fixdrv;


SELECT @UseDisk AS [Disk 2 use];
IF (NOT @UseDisk IS NULL) BEGIN
  PRINT 'il disco esiste, ora verifichiamo che il folder richiesto NON esista';
  SELECT @chkdirectory = @UseDisk + @Folder;
  
  INSERT INTO @file_results 
      (file_exists, file_is_a_directory, parent_directory_exists)
    EXEC master.dbo.xp_fileexist @chkdirectory;
     
    SELECT @folder_exists = file_is_a_directory
    FROM @file_results;

  IF (@folder_exists = 0) BEGIN
    PRINT 'creazione directory';
    EXECUTE master.dbo.xp_create_subdir @chkdirectory;
    END;
  ELSE
    PRINT 'Directory Esistente - nessuna operazione effettuata';
  END;
ELSE
  PRINT 'nessun disco valido';

SET NOCOUNT ON;
USE [master];
GO
DECLARE @File_Count int;
DECLARE @logical_name varchar(256);
DECLARE @Physical_name varchar(256);
DECLARE @curPhysicalName varchar(1024);
DECLARE @curDirName varchar(1024);
DECLARE @Alter_command varchar(MAX);

SELECT @curPhysicalName = physical_name 
  FROM sys.master_files f
  WHERE f.name = 'tempdev';

SET @curDirName = REVERSE(RIGHT(REVERSE(@curPhysicalName),( LEN(@curPhysicalName) - CHARINDEX('\',REVERSE(@curPhysicalName),1)) + 1)); 
SELECT @curDirName;

SELECT @Alter_command = 'ALTER DATABASE [tempdb] MODIFY FILE (NAME = N''tempdev'', SIZE = 2048576KB , FILEGROWTH = 0);
ALTER DATABASE [tempdb] ADD FILE ( NAME = N''tempdev2'' , FILENAME = N''' + @curDirName + 'tempdb2.Ndf'', SIZE = 1048576KB , FILEGROWTH = 0);'
--EXEC @Alter_command;

SELECT @Alter_command;
--<----------------------


C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\


ALTER DATABASE [tempdb] MODIFY FILE (NAME = N'tempdev', SIZE = 2048576KB , FILEGROWTH = 0);
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev2' , FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\tempdb2.Ndf', SIZE = 10485