TSQL: Generate Make Directory (MD) commands

Cheap and cherful way to create MD commands from an existing server. Handy for building a new server based upon an old.

/*
CREATE PROCEDURE #DBA_Create_Folder_Create_Batch_File
AS
BEGIN
*/

/****CommentStart

Author: Kevin Foreman
Date: 09-11-2006
Description:

Quick and dirty piece of code to generate make directory (MD) commands
based upon an existing folder structure. Save output to a .BAT/.CMD
file, eyaball and edit as required then call on destination server.

Input:
@drive_letter >> drive that you want folder structure from,
@include_folder1 to @include_folder4 >> 4 top level folders
Output:
MD commands for all subfolder/folders included
HEALTH WARNING:
No error checking

Maintained:


CommentEnd****/

SET NOCOUNT ON

--setup variables
DECLARE
@s varchar(2000) ,
@sql varchar(2000),
@i int ,
@drive_letter char(1) ,
@include_folder1 varchar(200) ,
@include_folder2 varchar(200) ,
@include_folder3 varchar(200) ,
@include_folder4 varchar(200)

--populate variables
SELECT
@drive_letter = 'i' ,
@include_folder1 = 'Build' ,
@include_folder2 = 'SQLBACK' ,
@include_folder3 = 'wibble' ,
@include_folder4 = 'wobble'
SELECT
@s = 'DIR ' + @drive_letter + ': /AD /S /B'

--create temp table to hold folder list
CREAT TABLE
#Folder_Name
(Folder_Name varchar(2000))

--get folder info
INSERT
#Folder_Name (Folder_Name)
EXEC
master.dbo.xp_cmdshell @s

--output make directory commands
SELECT DISTINCT
'MD "' + LTRIM(RTRIM(Folder_Name)) + '"' AS 'Create_Folder'
FROM
#Folder_Name
WHERE
(
UPPER (@drive_letter + ':\' + LTRIM(RTRIM(@include_folder1)) ) = UPPER ( LTRIM(RTRIM(Folder_Name)) )
OR
UPPER (@drive_letter + ':\' + LTRIM(RTRIM(@include_folder1)) + '\' ) = UPPER ( SUBSTRING(Folder_Name,1,LEN(LTRIM(RTRIM(@include_folder1))) + 4) )
)
OR
(
UPPER (@drive_letter + ':\' + LTRIM(RTRIM(@include_folder2)) ) = UPPER ( LTRIM(RTRIM(Folder_Name)) )
OR
UPPER (@drive_letter + ':\' + LTRIM(RTRIM(@include_folder2)) + '\' ) = UPPER ( SUBSTRING(Folder_Name,1,LEN(LTRIM(RTRIM(@include_folder2))) + 4) )
)
OR
(
UPPER (@drive_letter + ':\' + LTRIM(RTRIM(@include_folder3)) ) = UPPER ( LTRIM(RTRIM(Folder_Name)) )
OR
UPPER (@drive_letter + ':\' + LTRIM(RTRIM(@include_folder3)) + '\' ) = UPPER ( SUBSTRING(Folder_Name,1,LEN(LTRIM(RTRIM(@include_folder3))) + 4) )
)
OR
(
UPPER (@drive_letter + ':\' + LTRIM(RTRIM(@include_folder4)) ) = UPPER ( LTRIM(RTRIM(Folder_Name)) )
OR
UPPER (@drive_letter + ':\' + LTRIM(RTRIM(@include_folder4)) + '\' ) = UPPER ( SUBSTRING(Folder_Name,1,LEN(LTRIM(RTRIM(@include_folder4))) + 4) )
)
ORDER BY
'Create_Folder'

--cleanup
DROP TABLE #Folder_Name

/*
END
EXEC #DBA_Create_Folder_Create_Batch_File
DROP PROC #DBA_Create_Folder_Create_Batch_File
*/