Author: Vigneshwaran A

You can learn whatever you believe that you can

How to identify the list of folders and files which has been modified on given date using SQL Server

In some situations you may face scenarios like “you need to know all the files and folders modified recently or modified after the specific date range to check and remove unnecessary files\folders from the Drive”, for this scenario you can make use of this script.

Pre-requisites:-

  1. PowerShell
  2. Access to “xp_cmdshell”

The below is the SQL stored procedure which uses 2 parameters (“Drive\Folder path” and “Date”), Date filter is an optional parameter, if you don’t supply any values, by default it will consider the files\folders which are modified from Yesterday (please note that you can change the default date filter value based on your requirement)

--EXEC [SP_Get_Directory_FilesInfo]  'D:\temp\scripts\','2020-04-01'
--EXEC [SP_Get_Directory_FilesInfo]  'D:\temp\scripts\'
Create or Alter PROCEDURE [dbo].[SP_Get_Directory_FilesInfo] 
(@path nvarchar(4000),@yyyymmdd date=NULL) AS 
BEGIN

declare @pscript nvarchar(4000)

IF @yyyymmdd IS Null
	SET @yyyymmdd= cast(DATEADD(DD,-1,getdate()) as date)

if right(@path,1)='\'
	set @path = substring(@path,1,len(@path)-1)

--To avoid using of system drive--
IF (Charindex('C:\',@path)>0  or charindex('c$',@path)>0) BEGIN
	Select 'Alert Message!! C-Drive is not supported' Return;
END

--PS Script--
set @pscript ='powershell.exe -ExecutionPolicy Unrestricted -command "Get-ChildItem -Recurse -Path "' + @path +
'" | Where-Object LastWriteTime -ge "' + CAST(@yyyymmdd  AS VARCHAR) +
'" | select-Object FullName, @{Name="''LastWriteTime''"; Expression={$_.LastWriteTime.ToString("''yyyy-MM-dd HH:mm:ss''")}}, Mode | Export-CSV -NoTypeInformation -path "' + @path + '\Outputfile.csv"" '

Exec xp_cmdshell @pscript, NO_OUTPUT;

drop table if exists tblcsv_data
create table tblcsv_data (aa nvarchar(max),bb nvarchar(max), mode nvarchar(100))

declare @c nvarchar(4000), @d nvarchar(4000)
set @c = @path + '\Outputfile.csv'

EXEC('
BULK INSERT tblcsv_data 
FROM ''' + @c +'''
WITH
(
    FIRSTROW = 2,
    FIELDTERMINATOR = ''","'',  --CSV field delimiter
    ROWTERMINATOR = ''\n'',   --Use to shift the control to next row
    TABLOCK
);
')

set @d = 'del '+ @c
Exec xp_cmdshell @d, NO_OUTPUT;

delete from tblcsv_data where aa like '%Outputfile.csv%'

drop table if exists tbl_FilesInfo
create table tbl_FilesInfo (id bigint identity(1,1), 
FileNames nvarchar(max), 
LastModified Datetime,
isDirectory bit null
)

insert into tbl_FilesInfo (FileNames,LastModified,isDirectory)
select replace(aa,'"',''),
replace(bb,'"',''),
case when mode='d-----"' then 1 else 0 end isDirectory
from tblcsv_data

if not exists (select 1 from tbl_FilesInfo) Begin
	Select 'Please Check!! There are no files having Date Range greater than [' + CAST(@yyyymmdd  AS VARCHAR) + ']' as Status
	return;
	END

select  FileNames,
LastModified,
isDirectory
from tbl_FilesInfo
order by isDirectory desc,
LastModified desc

END

Sample Input Folder:-

Execution of SP with 2 Parameters:-

Execution of SP without Date Parameter:-

In addition to the above, here is the some example of PS scripts which you can directly use it in PowerShell and get the same desired results.

#PS Script 1 output will be displayed in powershell window
Get-ChildItem -Recurse -Path "C:\temp\script" | Where-Object LastWriteTime -ge "2021-04-02" | select-Object FullName, @{Name="''LastWriteTime''"; Expression={$_.LastWriteTime.ToString("''yyyy-MM-dd HH:mm:ss''")}}, @{Name = "Isdirectory"; Expression = {$_.Mode -replace "d-----","1" -replace "-a----","0"}}

#PS Script 2 output will be saved in .csv file
#If you want to save results to text file, please change export file name as Outputfile.txt
Get-ChildItem -Recurse -Path "C:\temp\script" | Where-Object LastWriteTime -ge "2021-04-02" | select-Object FullName, @{Name="''LastWriteTime''"; Expression={$_.LastWriteTime.ToString("''yyyy-MM-dd HH:mm:ss''")}}, @{Name = "Isdirectory"; Expression = {$_.Mode -replace "d-----","1" -replace "-a----","0"}} | Export-CSV "C:\temp\script\Outputfile.csv"

If you enjoyed this blog post, please feel free to share it with your friends!

Converting Row values into String with delimiter in SQL Server

In this post we are going to see how we can convert the Row values into comma separated String. Different SQL versions to achieve this (mentioned below), lets discuss one by one.

  1. Using Coalesce and Substring Function
  2. Using Replace Function
  3. Using Stuff Function
Using Coalesce and Substring Function

In this example, we have converted the column values (RowNO, RowValues) from RowsIntoString table into String value with comma delimiter using coalesce and substring SQL functions.

drop table if exists RowsIntoString

create table RowsIntoString (RowNo int, RowValues nvarchar(100))
insert into RowsIntoString (RowNo, RowValues) 
values (1,'first'),(2,'second'),(4,'third'),(4,'fourth'),(5,'fifth'),(7,'sixth'),(7,'seventh')
,(7,'eighth'),(9,'nineth'),(10,'tenth'),(11,'eleventh')

select * from RowsIntoString

---using Coalesce---
DECLARE @CSkeycol VARCHAR(MAX), @CSkeycol2 VARCHAR(MAX)
select @CSkeycol=COALESCE(@CSkeycol + ', ', '')+cast(RowNo as nvarchar) from RowsIntoString
select @CSkeycol as RowsIntoStringValue

select @CSkeycol2=COALESCE(@CSkeycol2 + ''', ''', '')+RowValues from RowsIntoString
select ''''+@CSkeycol2+'''' as RowsIntoStringValue

---using Substring---
declare @tmp varchar(MAX)=''
declare @tmp2 varchar(MAX)=''
select @tmp = @tmp + cast(RowNo as nvarchar) + ', ' from RowsIntoString
select SUBSTRING(@tmp, 0, LEN(@tmp)) as RowsIntoStringValue

select @tmp2 = @tmp2 + RowValues + ''', ''' from RowsIntoString
select ''''+SUBSTRING(@tmp2, 0, LEN(@tmp2)-2) as RowsIntoStringValue
Using Replace Function

In this example, we have converted the multi row string values into string value with comma delimiter using Replace SQL function.

declare @stg varchar(MAX)
set @stg='second
third
fourth
fifth
sixth
seventh'
select '''' + replace(@stg, char(10),''', ''') + '''' as RowsIntoStringValue
set @stg='1
2
3
4
5
6
7'
select replace(@stg, char(10),', ') as RowsIntoStringValue
Using Stuff Function

In this example, we have Concatenated the Rowvalues with comma which is having same RowNo (in other words, concatenating the RowValues column if RowNo column has more than one values) using Stuff SQL functions.

select * from RowsIntoString where RowNo in (4,7)

select distinct t1.RowNo,
  STUFF(
         (SELECT ', ' + RowValues
          FROM RowsIntoString t2
          where t1.RowNo = t2.RowNo
          FOR XML PATH (''))
          , 1, 1, '')  AS RowValues
from RowsIntoString t1
where RowNo in (4,7)
There is another post written by Latheesh which details another way with string_AGG.

Thanks for reading this blog post, If you enjoyed this, please share it with your friends!

Update() Trigger Function in Table

Update() function returns “True” in any case the Insert/update attempt is successful on the supplied column, the significance of this function is to trigger the action/code only if the supplied column is get updated or inserted successfully.

In the below example, I’ve used the Update() function for identifying any changes in “CodeValue” column of table “Configtbl” and ignored the column changes if its value is same.

“Configtbl_Audit” table will get loaded based on the changes in “CodeValue” column in “Configtbl”

drop table if exists Configtbl
drop table if exists Configtbl_Audit

create table Configtbl(
	 Code nvarchar(10) not null,
	 CodeValue decimal(10,2) not null
)
insert into Configtbl(Code,CodeValue) values ('VA1111',25.99),('VA1118',19.99)

create table Configtbl_Audit(
	 Code nvarchar(10) not null,
	 Old_Value decimal(10,2) not null,
	 New_Value decimal(10,2) not null,
	 DateModified datetime not null
)
 
IF EXISTS (SELECT name FROM sys.objects WHERE name = 'TRG_UPD_NEW_OLD_VALUE' AND type = 'TR')  
   DROP TRIGGER TRG_UPD_NEW_OLD_VALUE 
GO 

CREATE TRIGGER TRG_UPD_NEW_OLD_VALUE  
ON Configtbl
FOR UPDATE   
AS   

declare @oldvalue decimal(10,2),@newvalue decimal(10,2), @Code nvarchar(10)
IF (UPDATE (CodeValue))  
BEGIN
	select @oldvalue = CodeValue , @Code = Code from deleted

	insert into Configtbl_Audit(Code, Old_Value, New_Value, DateModified)
	select @Code, @oldvalue, CodeValue, getdate()
	from inserted 
	where code=@Code
	and @oldvalue <> CodeValue

END;  
GO 

Output:-

View of Data in Configtbl and Configtbl_Audit tables before doing any updates:

After updating “Code” column in Configtbl table:

After updating “CodeValue” column in Configtbl table:

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

Windows Power Shell Script to run SQL Files in Folder against SQL Server Database

Problem Statement:-

At times, we may need to run SQL files present in a folder against SQL Server Database. Since there are no built in feature available as of now, here is our try with a powershell script.

Pre-requisites:-

  1. Save the (.bat) Batch script & (.ps1) PS Script in same folder in which all the SQL Files are stored.
  2. The Machine should have the SQL Server instance and Power shell tool.
  3. Ensure the parameters values are verified in (.bat) Batch file

Scenarios Covered:-

i) All key values are handled with parameters
ii) If database not exist it will throw error and comes out of PS
iii) If any script has issues/failed, it will throw error and comes out of PS
iv) If we want to run the sql scripts in subfolder as well, then “Includesubfolders” parameter set to 1
v) Batch file automatically retrieve the root path

PS Script:-

$Scriptpath  = $args[0]
$Server =  $args[1]
$database = $args[2]
$user= $args[3]
$pwd= $args[4]
$Includesubfolders=$args[5]

Function IsDBInstalled([string]$Server, [string]$database)
{

 $t=Invoke-Sqlcmd -ServerInstance $Server -Username  $user -Password  $pwd -Database "master" -Query "select 1 from sys.databases where name='$database'" -OutputSqlErrors $true 
  if (!$t) {
            Write-Host "Failed to connect to [$database] database on [$Server]" -BackgroundColor darkred 
            Write-Error "Failed to connect to [$database] database on [$Server]" -ErrorAction Stop
  } else {
              
            write-host "[$database] Database exists in SQL Server [$Server]" -BackgroundColor blue -ForegroundColor black
  }
}

IsDBInstalled $Server $database

if($Includesubfolders -eq 1) {
$subscripts = Get-ChildItem $Scriptpath -recurse | Where-Object {$_.Extension -eq ".sql"}
foreach ($s in $subscripts)
    {   Write-Host "Running Script : " $s.Name -BackgroundColor green -ForegroundColor darkRed
        $tables=Invoke-Sqlcmd -ServerInstance $Server -Username  $user -Password  $pwd -Database  $database -InputFile $s.FullName -ErrorAction 'Stop' -querytimeout ([int]::MaxValue)
        write-host ($tables | Format-List | Out-String) 
        }
} else {
$scripts = Get-ChildItem $Scriptpath | Where-Object {$_.Extension -eq ".sql"}
foreach ($s in $scripts)
    {   Write-Host "Running Script : " $s.Name -BackgroundColor green -ForegroundColor darkRed
        $tables=Invoke-Sqlcmd -ServerInstance $Server -Username  $user -Password  $pwd -Database  $database -InputFile $s.FullName -ErrorAction 'Stop' -querytimeout ([int]::MaxValue)
        write-host ($tables | Format-List | Out-String) 
        }
}

Batch Script:-

@ECHO ON
SET root=%cd%
SET PSScript=%root%\RunSQLFiles.ps1
SET PowerShellDir=C:\Windows\System32\WindowsPowerShell\v1.0
CD /D "%PowerShellDir%"

SET path=%root%
SET "machine=sqlserversample45"
SET "db=sample"
SET "user=username"
SET "pwd=password"
SET "Includesubfolders=0"

Powershell -ExecutionPolicy Bypass -Command "& '%PSScript%' '%path%' '%machine%' '%db%' '%user%' '%pwd%' '%Includesubfolders%'"

Pause
EXIT /B

Output:-

If Database Not Exists:

If Database Exists:

If Any Script has Error:

If “Includesubfolders” parameters set to “1”

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

Windows Power Shell Script to Find Full File Path Length for all files in Directory

In some cases, we may need to identify the maximum length of full file path in a directory such that we can reduce the file name to avoid file length/security policy issues.

PS Script:-

$pathToScan = "C:\temp\File_Length"  
$outputFilePath = "C:\temp\File_Length\output.txt" 
$writeOnConsole = $true   

$outputDir = Split-Path $outputFilePath -Parent
if (!(Test-Path $outputDir)) { New-Item $outputDir -ItemType Directory }

if ($writeOnConsole) {Write-Host "*************************************"}
if ($writeOnConsole) {Write-Host "  List of files with file Length :-  "}
if ($writeOnConsole) {Write-Host "*************************************"}
$stream = New-Object System.IO.StreamWriter($outputFilePath, $false)
Get-ChildItem -Path $pathToScan -Recurse -Force | Sort-Object {($_.FullName.Length)} -Descending | ForEach-Object {
    $Path = $_.FullName
    $len = $_.FullName.Length
    $strg = "$len : $Path"
    
    if ($writeOnConsole) { Write-Host $strg }

    $stream.WriteLine($strg)
}
$stream.Close()

Output:-

Hope this would be helpful, thanks for reading !!

For more Powershell related blogs: refer here.