Snippet Name: SQL_File_Duplicate_Detection

Tags:

Created Date: 2025-06-16 13:12:44
Last Modified Date: 2025-05-31 10:32:03

Dependencies:

DBA Notes:

Sample Output:

Title: SQL File Duplicate Detection
Author: user
i would like to create a table in SQL server with information about all the files under the folder "G:\My Drive\_Data" and all sub folders. with that in a table, i could later find duplicate names, but that are different files, or true duplicates.

Author: ChatGPT
To create a table in SQL Server and populate it with information about all the files in the folder `G:\My Drive\_Data` (including subfolders), follow these steps:

### 1. **Define the SQL Server Table**
The table will store file details such as name, path, size, and creation date.

```sql
CREATE TABLE FileDetails (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    FileName NVARCHAR(255),
    FilePath NVARCHAR(MAX),
    FileSize BIGINT,
    CreatedDate DATETIME,
    ModifiedDate DATETIME,
    FileHash NVARCHAR(64) NULL -- Optional: For true duplicate detection
);
```

### 2. **PowerShell Script to Populate the Table**
Use PowerShell to scan the folder structure and insert file details into the SQL Server table.

```powershell
# Define folder path and SQL Server details
$RootFolder = "G:\My Drive\_Data"
$SqlServer = "YourSqlServer"
$Database = "YourDatabase"
$Table = "FileDetails"

# Load the SQL Server module
Import-Module SqlServer

# Get all files recursively
$Files = Get-ChildItem -Path $RootFolder -Recurse -File

# Insert file details into the SQL Server table
foreach ($File in $Files) {
    $FileName = $File.Name
    $FilePath = $File.FullName
    $FileSize = $File.Length
    $CreatedDate = $File.CreationTime
    $ModifiedDate = $File.LastWriteTime

    # Generate an MD5 hash of the file contents (optional for duplicate detection)
    $FileHash = ""
    if (Test-Path $FilePath) {
        $FileHash = (Get-FileHash $FilePath -Algorithm MD5).Hash
    }

    # Insert into SQL Server
    Invoke-Sqlcmd -ServerInstance $SqlServer -Database $Database -Query "
        INSERT INTO $Table (FileName, FilePath, FileSize, CreatedDate, ModifiedDate, FileHash)
        VALUES ('$FileName', '$FilePath', $FileSize, '$CreatedDate', '$ModifiedDate', '$FileHash')"
}
```

### 3. **Detect Duplicates**
Once the table is populated, you can query for duplicates:

#### Files with the Same Name but Different Paths
```sql
SELECT FileName, COUNT(*)
FROM FileDetails
GROUP BY FileName
HAVING COUNT(*) > 1;
```

#### True Duplicates (Same Name, Size, and Hash)
```sql
SELECT FileName, FileSize, FileHash, COUNT(*)
FROM FileDetails
GROUP BY FileName, FileSize, FileHash
HAVING COUNT(*) > 1;
```

### 4. **Automate the Script**
You can schedule this PowerShell script to run periodically using Task Scheduler to keep the table updated.

Let me know if you need help modifying the script or setting up automation!

Author: user
can we also extract the file extension as a seperate column and property

Author: ChatGPT
Yes, we can extract the file extension as a separate column in the SQL Server table and as a property in the PowerShell script. Here's how:

### 1. **Modify the SQL Server Table**
Add a column for the file extension:

```sql
ALTER TABLE FileDetails
ADD FileExtension NVARCHAR(10);
```

### 2. **Update the PowerShell Script**
Extract the file extension in PowerShell and include it in the SQL Server insert statement.

Updated script:

```powershell
# Define folder path and SQL Server details
$RootFolder = "G:\My Drive\_Data"
$SqlServer = "YourSqlServer"
$Database = "YourDatabase"
$Table = "FileDetails"

# Load the SQL Server module
Import-Module SqlServer

# Get all files recursively
$Files = Get-ChildItem -Path $RootFolder -Recurse -File

# Insert file details into the SQL Server table
foreach ($File in $Files) {
    $FileName = $File.Name
    $FilePath = $File.FullName
    $FileSize = $File.Length
    $CreatedDate = $File.CreationTime
    $ModifiedDate = $File.LastWriteTime
    $FileExtension = $File.Extension

    # Generate an MD5 hash of the file contents (optional for duplicate detection)
    $FileHash = ""
    if (Test-Path $FilePath) {
        $FileHash = (Get-FileHash $FilePath -Algorithm MD5).Hash
    }

    # Insert into SQL Server
    Invoke-Sqlcmd -ServerInstance $SqlServer -Database $Database -Query "
        INSERT INTO $Table (FileName, FilePath, FileSize, CreatedDate, ModifiedDate, FileExtension, FileHash)
        VALUES ('$FileName', '$FilePath', $FileSize, '$CreatedDate', '$ModifiedDate', '$FileExtension', '$FileHash')"
}
```

### 3. **Updated Query for Duplicates**
With the file extension stored, you can also query duplicates with or without considering extensions.

#### Duplicates Considering File Extension
```sql
SELECT FileName, FileExtension, COUNT(*)
FROM FileDetails
GROUP BY FileName, FileExtension
HAVING COUNT(*) > 1;
```

#### True Duplicates Considering File Extension
```sql
SELECT FileName, FileExtension, FileSize, FileHash, COUNT(*)
FROM FileDetails
GROUP BY FileName, FileExtension, FileSize, FileHash
HAVING COUNT(*) > 1;
```

This approach ensures file extension is captured, enabling more granular analysis. Let me know if you need help refining this further!

Author: user
i appear to have some file names in windows, whcih contain single quotes. how can we fix that

Author: ChatGPT
Single quotes in file names can cause issues in SQL queries because they are used to delimit string literals. To handle this, you need to escape single quotes in the file names by doubling them (`'` becomes `''`). Here???s how you can update the PowerShell script to handle this:

### Updated PowerShell Script
Escape single quotes in the file name and file path by replacing each single quote (`'`) with two single quotes (`''`):

```powershell
# Define folder path and SQL Server details
$RootFolder = "G:\My Drive\_Data"
$SqlServer = "YourSqlServer"
$Database = "YourDatabase"
$Table = "FileDetails"

# Load the SQL Server module
Import-Module SqlServer

# Get all files recursively
$Files = Get-ChildItem -Path $RootFolder -Recurse -File

# Insert file details into the SQL Server table
foreach ($File in $Files) {
    $FileName = $File.Name.Replace("'", "''")
    $FilePath = $File.FullName.Replace("'", "''")
    $FileSize = $File.Length
    $CreatedDate = $File.CreationTime
    $ModifiedDate = $File.LastWriteTime
    $FileExtension = $File.Extension.Replace("'", "''")

    # Generate an MD5 hash of the file contents (optional for duplicate detection)
    $FileHash = ""
    if (Test-Path $FilePath) {
        $FileHash = (Get-FileHash $FilePath -Algorithm MD5).Hash
    }

    # Insert into SQL Server
    Invoke-Sqlcmd -ServerInstance $SqlServer -Database $Database -Query "
        INSERT INTO $Table (FileName, FilePath, FileSize, CreatedDate, ModifiedDate, FileExtension, FileHash)
        VALUES ('$FileName', '$FilePath', $FileSize, '$CreatedDate', '$ModifiedDate', '$FileExtension', '$FileHash')"
}
```

### Key Adjustments
1. **Replace Single Quotes**:
   - `Replace("'", "''")` escapes single quotes in `FileName`, `FilePath`, and `FileExtension`.

2. **Test Hash Handling**:
   Ensure the hash field is robustly calculated without affecting other columns.

### Why This Works
SQL Server uses single quotes for string literals, so doubling them within the string ensures the SQL parser interprets the single quote correctly as part of the string, not as a delimiter.

### Testing the Script
- Run the script on a test directory with known problematic file names (e.g., containing `'`).
- Confirm the data is correctly inserted into SQL Server by querying the table.

This approach should resolve the issue with single quotes seamlessly. Let me know if you face any issues!