Check and Create a Folder Using T-SQL Code

> Check if Folder Exist or Not in our Desired Location Using System Stored Procedure

"master.dbo.xp_fileexist" And if not exists create Directoty Using Below T-SQL Code:

declare @chkdirectory as nvarchar(4000)

    declare @folder_exists as int     

    set @chkdirectory = 'C:\SQLDBPool\SQL\Articles' 

    declare @file_results table

    (file_exists int,

    file_is_a_directory int,

    parent_directory_exists int

    ) 

    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     

    --script to create directory       

    if @folder_exists = 0

     begin

        print 'Directory is not exists, creating new one'

        EXECUTE master.dbo.xp_create_subdir @chkdirectory

        print @chkdirectory +  'created on' + @@servername

     end       

    else

    print 'Directory already exists'

GO

Dynamically Create a Folder for Each Database In Server

I Need to Create Folder For Each Database in a particular server.I like to have a specific folder structure to hold the backup files for each database.  See below:

                       D:\Backup\Server\Instance\BabjiDB
                     D:\Backup\Server\Instance\NaveenDB
                     D:\Backup\Server\Instance\JaipalDB

So we need to maintain each DB backup in separate folder,Check Below T-SQL code 

DECLARE

       @BaseFolder nvarchar(128)

      ,@DatabaseFolder nvarchar(256)

      ,@DbName sysname

     

-- Only one input parameter needed from the user.

SET @BaseFolder = 'D:\Backup';

DECLARE Db CURSOR FOR

SELECT name FROM sys.databases WHERE name <> 'tempdb';

OPEN Db;

FETCH NEXT FROM Db INTO @DbName;

WHILE @@FETCH_STATUS = 0

BEGIN

      -- Dynamically create a folder for each database.

      SET @DatabaseFolder = @BaseFolder + '\'

      + CONVERT(nvarchar,SERVERPROPERTY('MachineName')) + '\'

      + ISNULL(CONVERT(nvarchar,SERVERPROPERTY('InstanceName')),'DEFAULT')

      + '\' + @DbName;

      EXEC master.sys.xp_create_subdir @DatabaseFolder;

      FETCH NEXT FROM Db INTO @DbName;

END

-- Clean up

CLOSE Db;

DEALLOCATE Db;

GO

HOW TO CREATE WINDOWS USING T-SQL Script

1. By using system stored procedure "master.sys.xp_create_subdir" to create  windows directories(Folders).see below tsql code to create folder 


  DECLARE @foldername VARCHAR(100)
  SET @foldername='c:\createfromsql'
  EXEC master.sys.xp_create_subdir @foldername

Purge sp to print deleted count

/****************************************************************************************************************
-- SQL Script    :  ES_Purge_ContactBase.sql
--****************************************************************************************************************
-- Description    :
--------Delete all contact records in the ContactBase table that are not associated with an active policy or quote 
--------and no associated with any customer validation and interaction note data already stored in CRM

--------Inactive contacts are defined as contact records satisfying all of the following conditions:
--------1.    Contacts not associated with any active policy (eas_policy)  
--------2.    Contacts not associated with any active quote (eas_quote)  
--------3.    Contact records that are not associated with any records in the following tables:
--------a.    eas_customervalidationBase
--------b.    eas_interactionBase

--------The physical delete needs to be cascaded to the following entities based on the foreign key constraints.
--------1.    ContactBase
--------2.    eas_policybase
--------3.    eas_quotebase
--------4.    eas_policycontactbase
--------5.    eas_contact_policy_relationship
--------6.    eas_quotecontactbase
--------7.    eas_contact_quote_relationship
--------8.    CustomerAddressBase

---PID          : 15-CLM-174
-- Developed    : Babji Reddy
--created_date  : 03/14/2016
-- Reviewed by  :
-- Modified By    :
-- Release        : 16-REL-03
--********************************************************************************************************************/

--USE ENV_CRM
--GO

IF EXISTS(SELECT * FROM sys.objects WHERE object_id=object_id(N'ES_Purge_ContactBase') AND TYPE IN(N'P',N'PC'))
BEGIN
    DROP PROC dbo.ES_Purge_ContactBase
END
GO
   
CREATE PROCEDURE dbo.ES_Purge_ContactBase
(
  @number_of_purgerecords INT=NULL
)
AS
BEGIN
    BEGIN TRY;
    SET NOCOUNT ON;
    DECLARE @message VARCHAR(MAX)=''
    DECLARE @CustomerAddressBase INT=0
    DECLARE @eas_contact_quote_relationshipBase INT=0
    DECLARE @eas_quotecontactBase INT=0
    DECLARE @eas_quoteBase INT=0
    DECLARE @eas_policycontactBase INT=0
    DECLARE @eas_contact_policy_relationshipBase INT=0
    DECLARE @eas_policyBase INT=0 
    DECLARE @contactbase INT=0

    IF EXISTS(SELECT * FROM sysobjects WHERE id=OBJECT_ID(N'Temp_ContactBase_main') AND OBJECTPROPERTY(id,N'IsUserTable')=1)
    BEGIN
        DROP TABLE dbo.Temp_ContactBase_main
    END
           
    IF EXISTS(SELECT * FROM sysobjects WHERE id=OBJECT_ID(N'Temp_ContactBase_sub') AND OBJECTPROPERTY(id,N'ISUserTable')=1)
    BEGIN
        DROP TABLE dbo.Temp_ContactBase_sub
    END

    CREATE TABLE dbo.Temp_ContactBase_main
        (
            ContactId uniqueidentifier NOT NULL,
            CONSTRAINT PK_Temp_ContactBase_main PRIMARY KEY CLUSTERED (ContactId)               
        )

           
    CREATE TABLE Temp_ContactBase_sub
    (
        ContactId uniqueidentifier NOT NULL, 
        CONSTRAINT PK_Temp_ContactBase_sub PRIMARY KEY CLUSTERED (ContactId)
    )
           
IF @number_of_purgerecords IS  NULL
BEGIN 
;WITH Un_wanted_contacts AS(
SELECT eas_Contact AS ContactId from eas_quotecontactbase  (NOLOCK)  where statecode=0 
UNION 
 SELECT eas_contactid AS ContactId FROM eas_policycontactbase (NOLOCk) WHERE StateCode=0
UNION 
 SELECT eas_contactid  AS ContactId FROM  eas_interactionBase (NOLOCk) 
UNION 
 SELECt eas_contactid  AS ContactId FROM  eas_customervalidationBase (NOLOCk)
UNION 
 SELECt eas_PolicyHolderNameId  AS ContactId FROM  eas_customervalidationBase (NOLOCk)
 UNION
SELECT eas_primaryinsured  FROM eas_quotebase (NOLOCK) WHERE statecode = 0
 UNION
 SELECT eas_policyholderid FROM dbo.eas_policybase (NOLOCK) WHERE statecode = 0
)
INSERT INTO Temp_ContactBase_main(ContactId)
SELECT ContactId FROM contactbase  (NOLOCK) 
 EXCEPT 
 SELECT ContactId FROM Un_wanted_contacts

 END 
    ELSE 
    BEGIN

        ;WITH Un_wanted_contacts AS(
         SELECT eas_Contact AS ContactId from eas_quotecontactbase  (NOLOCK)  where statecode=0 
         UNION 
         SELECT eas_contactid AS ContactId FROM eas_policycontactbase (NOLOCk) WHERE StateCode=0
         UNION 
         SELECT eas_contactid  AS ContactId FROM  eas_interactionBase (NOLOCk) 
         UNION 
         SELECt eas_contactid  AS ContactId FROM  eas_customervalidationBase (NOLOCk)
         UNION 
         SELECt eas_PolicyHolderNameId  AS ContactId FROM  eas_customervalidationBase (NOLOCk)
         UNION
         SELECT eas_primaryinsured  FROM eas_quotebase (NOLOCK) WHERE statecode = 0
         UNION
         SELECT eas_policyholderid FROM dbo.eas_policybase (NOLOCK) WHERE statecode = 0
        )
        INSERT INTO Temp_ContactBase_main(ContactId)
        SELECT top (@number_of_purgerecords) ContactId FROM (
        SELECT ContactId FROM contactbase  (NOLOCK) 
         EXCEPT 
         SELECT ContactId FROM Un_wanted_contacts) a
 
   END    
                              
INSERT INTO dbo.Temp_ContactBase_sub(ContactId)
SELECT TOP 10000 ContactId FROM Temp_ContactBase_main WITH(NOLOCK)


WHILE EXISTS( SELECT 1 FROM Temp_ContactBase_main)
BEGIN   
         
        --CustomerAddressBase 
        --eas_contact_quote_relationship 
        --eas_quotecontactbase 
        --eas_quotebase 
        --eas_policybase 
        --eas_policycontactbase 
        --eas_contact_policy_relationship 
        --contactBase

        SET @message=''

        ---CustomerAddressBase

        DELETE cab
        FROM dbo.CustomerAddressBase cab
        INNER JOIN dbo.Temp_ContactBase_sub cb WITH(NOLOCk)
        ON cab.ParentId=cb.ContactId
       
        SET @CustomerAddressBase=@CustomerAddressBase+@@ROWCOUNT
        SET @message=@message+'No of purge records in CustomerAddressBase:'+CAST(@CustomerAddressBase AS VARCHAR(10))
       
        -- eas_contact_quote_relationshipBase
        DELETE ecqr
        FROM  dbo.eas_contact_quote_relationshipBase ecqr
        INNER  JOIN Temp_ContactBase_sub re_sub WITH(NOLOCK)
        ON ecqr.ContactId=re_sub.ContactId

        SET @eas_contact_quote_relationshipBase=@eas_contact_quote_relationshipBase+@@ROWCOUNT   
        SET @message=@message+SPACE(2)+'Number of purge records in eas_contact_quote_relationshipBase:'+CAST(@eas_contact_quote_relationshipBase AS VARCHAR(10)) 

        --eas_quotecontactBase
        DELETE ecqr
        FROM  dbo.eas_quotecontactBase ecqr
        INNER  JOIN Temp_ContactBase_sub re_sub WITH(NOLOCK)
        ON ecqr.eas_Contact=re_sub.ContactId

        SET @eas_quotecontactBase=@eas_quotecontactBase+@@ROWCOUNT
        SET @message=@message+SPACE(2)+'Number of purge records in eas_quotecontactBase:'+CAST(@eas_quotecontactBase AS VARCHAR(10)) 

        --eas_quoteBase
        DELETE ecqr
        FROM  dbo.eas_quoteBase ecqr
        INNER  JOIN Temp_ContactBase_sub re_sub WITH(NOLOCK)
        ON ecqr.eas_primaryinsured=re_sub.ContactId

        SET @eas_quoteBase=@eas_quoteBase+@@ROWCOUNT
        SET @message=@message+SPACE(2)+'Number of purge records in eas_quoteBase:'+CAST(@eas_quoteBase AS VARCHAR(10)) 
       
       --eas_policycontactBase
            DELETE ecv
         FROm dbo.eas_policycontactBase ecv       
         INNER  JOIN Temp_ContactBase_sub re_sub WITH(NOLOCK)
         ON ecv.eas_contactid=re_sub.ContactId

        SET @eas_policycontactBase=@eas_policycontactBase+@@ROWCOUNT
        SET @message=@message+SPACE(2)+'Number of purge records in eas_policycontactBase:'+CAST(@eas_policycontactBase AS VARCHAR(10)) 


      ---eas_contact_policy_relationship
        DELETE ecpr
        FROM  dbo.eas_contact_policy_relationshipBase ecpr       
        INNER  JOIN Temp_ContactBase_sub re_sub WITH(NOLOCK)
        ON ecpr.contactid=re_sub.ContactId         

         SET @eas_contact_policy_relationshipBase=@eas_contact_policy_relationshipBase+@@ROWCOUNT
         SET @message=@message+SPACE(2)+'Number of purge records in eas_contact_policy_relationshipBase:'+CAST(@eas_contact_policy_relationshipBase AS VARCHAR(10)) 
       ---eas_policyBase
        DELETE ecqr
        FROM  dbo.eas_policyBase ecqr
        INNER  JOIN Temp_ContactBase_sub re_sub WITH(NOLOCK)
        ON ecqr.eas_PolicyHolderId=re_sub.ContactId
        
        SET @eas_policyBase=@eas_policyBase+@@ROWCOUNT
        SET @message=@message+SPACE(2)+'Number of purge records in eas_contact_policy_relationshipBase:'+CAST(@eas_policyBase AS VARCHAR(10)) 
        --- Contactbase
        DELETE ab
        FROM  dbo.ContactBase ab 
        INNER  JOIN Temp_ContactBase_sub re_sub WITH(NOLOCK)
        ON ab.ContactId=re_sub.ContactId
       
        SET @ContactBase=@ContactBase+@@ROWCOUNT
        SET @message=@message+SPACE(2)+'Number Of Purge Records in ContactBase:'+CAST(@ContactBase AS VARCHAR(10))

        DELETE ab
        FROM  Temp_ContactBase_main ab
        INNER JOIN  Temp_ContactBase_sub y WITH(NOLOCK)
        On ab.ContactId=y.ContactId

        TRUNCATE TABLE Temp_ContactBase_sub
       
        INSERT INTO  Temp_ContactBase_sub(ContactId)
        SELECT TOP 10000 ContactId FROM Temp_ContactBase_main WITH(NOLOCK)
           
END

INSERT INTO CRM_Staging_Data.dbo.CRM_Purge_Log(Script_Name,Purge_Date,Purge_user,[Message])
SELECT 'SP: ES_Purge_ContactBase',GETDATE(),USER_NAME(),@message

    IF EXISTS(SELECT * FROM sysobjects WHERE id=OBJECT_ID(N'Temp_ContactBase_main') AND OBJECTPROPERTY(id,N'IsUserTable')=1)
    BEGIN
        DROP TABLE Temp_ContactBase_main
    END
   
    IF EXISTS(SELECT * FROM sysobjects WHERE id=OBJECT_ID(N'Temp_ContactBase_sub') AND OBJECTPROPERTY(id,N'IsUserTable')=1)
    BEGIN
        DROP TABLE Temp_ContactBase_sub   
    END
       
END TRY  
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;
   
    SELECT  @ErrorMessage = N'Error in SP: dbo.ES_Purge_ContactBase: ' + ERROR_MESSAGE(),
            @ErrorSeverity = ERROR_SEVERITY(),
            @ErrorState = ERROR_STATE();
    INSERT INTO CRM_Staging_Data.dbo.CRM_Purge_Log(Script_Name,Purge_Date,Purge_user,[Message])
    SELECT 'SP: ES_Purge_ContactBase',GETDATE(),USER_NAME(),@message+@ErrorMessage
    RAISERROR( @ErrorMessage,@ErrorSeverity,@ErrorState );
END CATCH
END


 
 

Remove Leading and Trailing Zeros

DECLARE @BankAccount TABLE (AccNo VARCHAR(15))

INSERT @BankAccount SELECT '01010'

INSERT @BankAccount SELECT '0010200'

INSERT @BankAccount SELECT '000103000'

  

SELECT * FROM @BankAccount

  

--Methods to remove leading zeros

  

-- 1.)  converting to integer data type

SELECT CONVERT(INT,AccNo) AccNo FROM @BankAccount

-- NN - note, this method will only work if the data are clean

  

-- 2.)  using SUBSTRING 

  

SELECT SUBSTRING(AccNo,PATINDEX('%[^0]%',AccNo),LEN(AccNo)) AccNo FROM @BankAccount

  

-- 3.)  using REPLACE,LTRIM & RTRIM

  

SELECT REPLACE(LTRIM(REPLACE(AccNo,'0',' ')),' ','0') AccNo FROM @BankAccount

--To remove both leading & trailing zeros

  

SELECT REPLACE(RTRIM(LTRIM(REPLACE(AccNo,'0',' '))),' ','0') AccNo FROM @BankAccount