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.

 

Curious case of varchar to uniqueidentifier in SQL Server

Converting a datatype to another datatype is a very common requirement in real world and we usually do it with CAST or CONVERT (eg. int to char or varchar etc).

There are two types of conversions – implicit and explicit.

Explicit conversions are the conversions done by developers using SQL server functions like cast/convert etc and Implicit conversions are mainly managed by SQL Server internally. If you want to see these types of conversions, you can check for IMPLICIT_CONVERT in the execution plan generated by SQL Server.

Today, we are going to see a curious case of an implicit conversion – varchar to uniquidentifier.

SQL Server does a implicit conversion from varchar to uniqueidentifier by default as below:

Declare @varchar nvarchar(MAX), @uniqueidentifier uniqueidentifier
Set @varchar ='f124656c-136b-4beb-ab3a-b348053f898a,7f0b0dd5-22bc-421b-9416-3a7c24146a98'

Set @uniqueidentifier = @varchar
Select @uniqueidentifier

Now, if you look at the results, you can see the first 36 character is being converted implicitly. Since its a trivial, the operator may not be able to see in your execution plan.

The word of caution

Sometimes, if we are assigning more values/characters in the varchar variable, it would ONLY pick the first 36 characters and ignoring the rest characters by default. This default character of the conversion may end up with a wrong results, however, there would not be any sign of error! So beware of this characteristic while you writing a code.

Windows Power Shell script to Purge/Cleanup Backup & Transaction files

At times, we need to purge backup files (.bak, *.trn) which are older than some x days from the server on the regular basis, such that Disk Space and SQL Data backups will be maintained consistently within the server.

Power Shell Script:-

The below power shell script will purge the backup files (which are older than 5 days) from server, this script identifies the older files based on the last modified date time.

Based on your requirement, you can change the date range and schedule this script for Server Maintenance.

Get-ChildItem -Path "C:\Backups" -Recurse -ErrorAction SilentlyContinue -include *.bak, *.trn | 
Where-Object {$_.LastWriteTime -lt (Get-Date).AddDays(-5) -and $_.PSIsContainer -eq $False} | 
Remove-Item

How to get list of filenames in a folder – Command Shell Script

There may be some situation where you want to find the list of all filenames whose patterns are like “*.txt”, “File*.csv”, “*.html”.., etc. In such scenario you can use the below command shell scripts to get the desired results which you are looking for.

1) To get the list of all filenames and folder names present in a folder “C:\testdata\”

cd "C:\testdata\"
dir /b /s

Output:-

2) To get all the filenames of pattern “*.txt” or “*.html” from folder “C:\testdata\”

dir /b /s "*.txt"
dir /b /s "*.html"

Output:-

3) To get all the filenames of pattern “*.txt” from folder “C:\testdata\” and store the corresponding results in the text document

dir /b /s "*.txt" > output.txt
dir /b /s "*.txt" > "c:\testdata\test\outputs.txt"

Output:-

Using Windows Power Shell to get the Server Disk Space Information

To find Drive Names, Total Space, Free Space and Free Space Percentage for the list of Servers specified in computers.txt file using Power Shell Script.

Prerequisites:-

  1. Save the computers.txt file and below PS script in the same folder
  2. User should have access to the servers mentioned in computers.txt file
  3. There should not be any empty space or new line without Servername in computers.txt file

PS Script:-

$path=Split-Path $MyInvocation.MyCommand.path 
$Computers = get-content "$path\computers.txt" 
foreach ($Computer in $Computers)  
{  
$Disks = Get-wmiobject  Win32_LogicalDisk -computername $Computer -ErrorAction SilentlyContinue -filter "DriveType= 3" 
$Servername = (Get-wmiobject  CIM_ComputerSystem -ComputerName $computer).Name
foreach ($objdisk in $Disks)  
{  
        
    $total=“{0:N3}” -f ($objDisk.Size/1GB)  
    $free= “{0:N3}” -f ($objDisk.FreeSpace/1GB)  
    $freePercent="{0:P2}" -f ([double]$objDisk.FreeSpace/[double]$objDisk.Size)  
       
       Write-Host "Servername      :" $Servername  
       Write-Host "Drive Folder    :" $objDisk.DeviceID "Drive" 
       Write-Host "Total size (GB) :" $total 
       Write-Host "Free Space (GB) :" $free 
       Write-Host “Free Space (%)  :” $freePercent 
} 
} 

Output:-

To save the Output to Diskspace_Report.csv file, Use below PS script:-

$path=Split-Path $MyInvocation.MyCommand.path 
$Computers = get-content "$path\computers.txt" 
foreach ($Computer in $Computers)  
{  
$Disks = Get-wmiobject  Win32_LogicalDisk -computername $Computer -ErrorAction SilentlyContinue -filter "DriveType= 3" 
$Servername = (Get-wmiobject  CIM_ComputerSystem -ComputerName $computer).Name
foreach ($objdisk in $Disks)  
{  
        
    $total=“{0:N3}” -f ($objDisk.Size/1GB)  
    $free= “{0:N3}” -f ($objDisk.FreeSpace/1GB)  
    $freePercent="{0:P2}" -f ([double]$objDisk.FreeSpace/[double]$objDisk.Size)  

        $out=New-Object PSObject 
        $out | Add-Member -MemberType NoteProperty -Name "Servername" -Value $Servername 
        $out | Add-Member -MemberType NoteProperty -Name "Drive" -Value $objDisk.DeviceID  
        $out | Add-Member -MemberType NoteProperty -Name "Total size (GB)" -Value $total 
        $out | Add-Member -MemberType NoteProperty -Name “Free Space (GB)” -Value $free 
        $out | Add-Member -MemberType NoteProperty -Name “Free Space (%)” -Value $freePercent 
        $out | Add-Member -MemberType NoteProperty -Name "Name " -Value $objdisk.volumename 
        $out | Add-Member -MemberType NoteProperty -Name "DriveType" -Value $objdisk.DriveType 
        $out | export-csv $path\Diskspace_Report.csv -NoTypeInformation -Append   
} 
}

Generate Random characters in SQL Server

Here is a script to generate randon characters in SQL Server

It also allows you to generate the characters in random as well as required format. There are four types of format the function supports:

Formats:
1. ‘Proper’ – proper name form (i.e. Xxxxx)
2. ‘Upper’ – all uppercase (i.e. XXXXX)
3. ‘Lower’ – all lowercase (i.e. xxxxx)
5. ‘Mixed’ – randomly mixed case (i.e. xXxxxXXxx)


Create View dbo.Q_Random as 
	Select Rand() as [RandomNumber]
GO
Create Function dbo.fn_GenerateText 
				(@Length integer, @Format varchar(6) = 'Mixed')
Returns varchar(256)
as 
Begin

--	Formats:	
--		'Proper' - proper name form (i.e. Xxxxx)
--		'Upper'  - all uppercase (i.e. XXXXX)
--		'Lower'  - all lowercase (i.e. xxxxx)
--		'Mixed'  - randomly mixed case (i.e. xXxxxXXxx)
--		 null    - randomly mixed case (i.e. xXxXxxxxxX)
--

Declare 
	@RandomValue             varchar(256), 
	@Count                   integer,
	@RandomNumber            float, 
	@RandomNumberInteger     integer, 
	@CurrentCharacter        char(1),
	@ValidCharactersLength   integer,
	@ValidCharacters         varchar(255) 

Set @RandomValue = '';

If (@Length = 0) 
	Goto ReturnData 


If (@Format = 'Mixed') 
	Set @ValidCharacters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789'; 
else
	Set @ValidCharacters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz';


Set @ValidCharactersLength = Len(@ValidCharacters); 
Set @CurrentCharacter      = ''; 
Set @RandomNumber          = 0; 
Set @RandomNumberInteger   = 0; 

Set @Count = 1; 


While @Count <= @Length 
Begin 
	Set @RandomNumber = (Select RandomNumber from Q_Random); 

	Set @RandomNumberInteger = Convert(integer, ((@ValidCharactersLength - 1) * @RandomNumber + 1)); 
 
	Set @CurrentCharacter = SubString(@ValidCharacters, @RandomNumberInteger, 1); 

	Set @RandomValue = @RandomValue + @CurrentCharacter; 

	Set @Count = @Count + 1; 
End 


If @Format = 'Lower' 
	Set @RandomValue = Lower(@RandomValue); 

If @Format = 'Upper' 
	Set @RandomValue = Upper(@RandomValue); 

If @Format = 'Proper' 
	Set @RandomValue = Upper(Left(@RandomValue, 1)) + Substring(Lower(@RandomValue), 2, (@Length - 1));  

--	... or the default  gives random `casing`, and 'Mixed' gives random alphanumeric `casing`


ReturnData:

Return @RandomValue 

End 

Sample Executions:

Select dbo.fn_GenerateText(10,’Lower’)
Select dbo.fn_GenerateText(10,’Upper’)
Select dbo.fn_GenerateText(10,’Mixed’)–default value
Select dbo.fn_GenerateText(10,’Proper’)

How to change mdf and ldf files path to instance default path in SQL Server

Here is a code snippet to change the mdf and ldf file path of an existing database to instance default path in SQL Server.
If you want to move to fixed path, then you can change the script as required.

Assumptions:
1. The script is mainly written for SQL Server 2016, for other version, please change as required.
2. The script will not execute or change the path automatically. It just prints the statements to execute. The user can validate the scripts and do the action as required. This is to prevent any unexpected events.


--Print 'Collect Default Data & Log information'
declare @DefLOG nvarchar(512)
declare @DefDATA nvarchar(512)

--Prepare the SQL statements for remapping
if (Cast(SERVERPROPERTY('Productmajorversion') as varchar(2))='13')
Begin

Set @DefLog = Cast( Serverproperty('InstanceDefaultLogPath') as varchar(512))
Set @DefDATA = Cast( Serverproperty('InstanceDefaultDataPath') as varchar(512))

--Print 'Move all datafiles'
declare cur_move cursor for
select --DB_NAME(dbid),name,filename, reverse(SUBSTRING(reverse(filename),1,CHARINDEX('\',reverse(filename))-1)),
'ALTER DATABASE ['+DB_NAME(dbid)+'] MODIFY FILE ( NAME = '''+name+''' , FILENAME = '''+@DefDATA+reverse(SUBSTRING(reverse(filename),1,CHARINDEX('\',reverse(filename))-1))+''' )'
from sys.sysaltfiles where dbid <32000
and groupid=1 and dbid>5 and charindex(@DefDATA,filename)=0
union all
select --DB_NAME(dbid),name,filename, reverse(SUBSTRING(reverse(filename),1,CHARINDEX('\',reverse(filename))-1)),
'ALTER DATABASE ['+DB_NAME(dbid)+'] MODIFY FILE ( NAME = '''+name+''' , FILENAME = '''+@DefLOG+reverse(SUBSTRING(reverse(filename),1,CHARINDEX('\',reverse(filename))-1))+''' )'
from sys.sysaltfiles where dbid <32000 and charindex(@DefDATA,filename)=0
and groupid=0 and dbid>5

declare @move varchar(max)
open cur_move
fetch next from cur_move into @move

while @@fetch_status=0
begin

print @move
--Exec (@move)

fetch next from cur_move into @move
end
close cur_move
deallocate cur_move

End
Else
Begin
Select 'This script supports SQL Server 2016 or later! For older version, you need to below to identify the default path, otherwise all are same.'
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @DefLOG OUTPUT
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @DefDATA OUTPUT
return;
End