Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
The Trade-off Between SQL Server Security and Performance - Free Webinar
 

PowerShell Module to Read Directory Contents and Store in a SQL Server Table


By:   |   Last Updated: 2018-12-04   |   Comments (3)   |   Related Tips: More > PowerShell

Problem

In this tip we will walk through how to create a PowerShell module to get a list of files and subfolders of a Windows folder and store this information in a SQL Server table.

Solution

In this tip, I am going to explain the code of a PowerShell module which gets a list of files that reside in a folder and stores this information in a SQL Server table.

I am going to:

  1. Create a SQL table that has a column to store FQN (Fully qualified name) of any file.
  2. Show how to write the PowerShell code.
  3. Execute PowerShell function using T-SQL.

In this tip, I have used the following PowerShell cmdlets.

PowerShell cmdlet Description
Invoke-SQLCmd It is used to execute a SQL command. Reference: Invoke-SQLcmd
Get-ChildItem It gets a list of files in a specified location. Reference: Get-ChildItem.
Foreach-Object It iterates through the collection of items and performs operations against each item. Reference: Foreach-Object

Create the SQL Server Table

To store the directory contents in a table, first I will create a table named "tblFileSummary" in database "DemoDatabase". The following code creates the SQL table.

USE [DemoDatabase]
GO

CREATE TABLE [dbo].[tblFileSummary](
   [ID] [int] IDENTITY(1,1) NOT NULL,
   [File_name] [varchar](max) NULL,
   [Fully_Qualified_FileName] [varchar](max) NULL,
   [attributes] [varchar](250) NULL,
   [CreationTime] [datetime] NULL,
   [LastAccessTime] [datetime] NULL,
   [LastWriteTime] [datetime] NULL,
   [Length] [numeric](10, 2) NULL,
PRIMARY KEY CLUSTERED 
(
   [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Create a PowerShell Module

As I mentioned, I want to get the list of files within a specific directory. To do that, we will create a PowerShell cmdlet. It accepts the Drive Letter as a parameter, iterates through the entire drive and populates the following details:

  1. File Name
  2. Fully qualified file name
  3. File attribute
  4. Last access time
  5. Last modified time
  6. File Size

To execute this function using a SQL Stored procedure, we must register it as a PowerShell module. Once the PowerShell module is registered, we add this module in the PowerShell profile.

First, let’s understand the PowerShell script.

To understand the PowerShell code, I have split it into multiple sections:

Script Part-1

Create a PowerShell function. This function accepts the Drive Letter as a parameter. The following code block creates the function.

function global:getFileSummery
{
param(
    [Parameter(Position=0,mandatory=$true)]
    [string[]] $FileLocation
)

Script Part-2

Construct a string variable named "$sqlstatment" that has an "Insert" query statement. The following code creates a string variable.

[email protected]'
INSERT INTO tblFileSummary(
   Fully_Qualified_FileName, File_name, attributes, CreationTime, LastAccessTime, LastWriteTime,
    Length    
) 
VALUES (
   '{0}',
   '{1}',
   '{2}',
   '{3}',
   '{4}',
   '{5}',
    '{6}'
)
'@

Script Part-3

Populate list of files command and formats the output according to the insert query stored in the "$sqlstatement" variable. The following is the code block.

Invoke-Sqlcmd -Query "Truncate Table tblFileSummary" -ServerInstance TTI412-VM\SQL2017 -database DemoDatabase
Get-ChildItem -Recurse $FileLocation | 
select File_name,Fully_Qualified_FileName,attributes, CreationTime, LastAccessTime, LastWriteTime,@{Label="Length";Expression={$_.Length / 1MB -as [int] }}|
   ForEach-Object {
      $SQL = $sqlstatement -f $_.FullName, $_.name,$_.attributes, $_.CreationTime, $_.LastAccessTime, $_.LastWriteTime,$_.Length                            
        Invoke-sqlcmd -Query $SQL -ServerInstance TTI412-VM\SQL2017 -database DemoDatabase

   }

The above code block performs the following tasks:

  1. Truncates table "tblFileSummary" using "Invoke-SQLCmd" cmdlet.
  2. It passes "$FilePath" parameter to "Get-ChildItem -Recurse" cmdlet and populates the list of files. "$FilePath" parameter is drive letter/ directory location (C:\Temp\...).
  3. Format the output, generated by "Get-ChildItem -Recurse" in multiple columns. Column sequence of output and the insert statement which is stored in "$sqlstatement" parameter must be the same.
The output of "Get-ChildItem -Recurse" Column sequence in the insert query
FullName {0}
Name {1}
Attribute {2}
CreationTime {3}
LastAccesstime {4}
LastWriteTime {5}
FileSize {6}
  1. It creates a new string variable named "$SQLQuery" by concatenating "$sqlstatement" and the values generated by "Get-ChildItem -Recurse" command. The format of "$SQLQuery" parameter is like a T-SQL insert statement. Using "ForEach-Object" cmdlet, it generates multiple insert statements and stores it in a $SQLQuery variable. See the following image:
powershell output
  1. By using "Invoke-SQLCmd" command, it inserts the value of the $SQLQuery variable into the tblFileDetails table.

Complete PowerShell Script

function global:getFileSummery
{
param(
    [Parameter(Position=0,mandatory=$true)]
    [string[]] $FileLocation
)

Write-Output "Inserting files"
[email protected]'
INSERT INTO tblFileSummary(
   Fully_Qualified_FileName, File_name, attributes, CreationTime, LastAccessTime, LastWriteTime,
    Length    
) 
VALUES (
   '{0}',
   '{1}',
   '{2}',
   '{3}',
   '{4}',
   '{5}',
    '{6}'
)
'@
Invoke-Sqlcmd -Query "Truncate Table tblFileSummary" -ServerInstance TTI412-VM\SQL2017 -database DemoDatabase
Get-ChildItem -Recurse $FileLocation  | 
select File_name,Fully_Qualified_FileName,attributes, CreationTime, LastAccessTime, LastWriteTime,@{Label="Length";Expression={$_.Length / 1MB -as [int] }}|
   ForEach-Object {
      $SQL = $sqlstatement -f $_.FullName, $_.name,$_.attributes, $_.CreationTime, $_.LastAccessTime, $_.LastWriteTime,$_.Length                            
        Invoke-sqlcmd -Query $SQL -ServerInstance TTI412-VM\SQL2017 -database DemoDatabase

   }
Write-Output "File Inserted successfully... Below Is list of files"
}

Save Script as a PowerShell Module

To execute the PowerShell function using T-SQL, register this script as PowerShell module. To register any PowerShell script, first a directory must be created in "C:\Windows\System32\WindowsPowerShell\v1.0\Modules". The name of the PowerShell script and directory must be the same. To register the above script, perform the following tasks:

  1. Create a new directory named "getFileSummery" in "C:\Windows\System32\WindowsPowerShell\v1.0\Modules" directory.
  2. Save the PowerShell script as getFileSummey.psm1 in "getFileSummery" directory.

Execute the PowerShell Function in T-SQL Script

To execute a PowerShell function in a stored procedure, enable xp_cmdshell. To do that, execute the following command:

use master
go

exec sp_configure 'show advanced option',1
reconfigure with override

exec sp_configure 'xp_cmdshell',1
reconfigure with override

To execute the PowerShell script using a T-SQL query, use the "powershell.exe" command. As I mentioned, our script inserts the fully qualified path of files located in a specific drive or specific directory, hence we must create a parameterized T-SQL script. In the script, we can pass the drive letter OR directory path as a parameter.

For this demonstration, I created a folder named "Scripts"" in my E drive. I copied a few SQL scripts to the folder. I am going to insert the file details of all directories and files located in the E:\Script folder. To do that, execute the following code:

declare @PSScript varchar(2500)declare @FileLocation varchar(2500)
set @FileLocation='E:\Scripts'
set @PSScript= 'powershell.exe getFileSummery ''' + @FileLocation +'''' 
exexec xp_cmdshell @PSScript

Let’s verify that whether file details are stored in a table using the following code:

SELECT * FROM tblFileSummary

As you can see in the following output, the fully qualified names and details from the "E:\Scripts" directory is stored in the "Fully_Qualified_FileName" " column.

query results

Summary

In this article, I explained:

  1. How to create a PowerShell module and how the PowerShell script works.
  2. Execute the PowerShell function using T-SQL code.
Next Steps


Last Updated: 2018-12-04


next webcast button


next tip button



About the author
MSSQLTips author Nisarg Upadhyay Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional with more than 5 years of experience.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Sunday, December 09, 2018 - 2:11:40 PM - Ioannis M. Back To Top

Unfortunately I get the same error

getFileSummery : The term 'getFileSummery' is not recognized as the name of a 

cmdlet, function, script file, or operable program. Check the spelling of the 

name, or if a path was included, verify that the path is correct and try again.

At line:1 char:1

+ getFileSummery 'H:\30032011'

+ ~~~~~~~~~~~~~~

    + CategoryInfo          : ObjectNotFound: (getFileSummery:String) [], Comm 

   andNotFoundException

    + FullyQualifiedErrorId : CommandNotFoundException

NULL


Friday, December 07, 2018 - 2:56:59 PM - Robert C Back To Top

 FWIW, the path for modules is below

 PS C:\WINDOWS\system32> $env:psmodulePath

C:\Users\rob\Documents\WindowsPowerShell\Modules;C:\Program Files\WindowsPowerShell\Modules;C:\WINDOWS\system32\WindowsPowerShell\v1.0\Modules;C:\Program Files (x86)\Microsoft SQL Server\140\Tools\PowerShell\Modules\

PS C:\WINDOWS\system32>


Thursday, December 06, 2018 - 9:36:49 PM - Robert C Back To Top

 I have saved the script as a Powershell module in C:\Windows\System32\WindowsPowerShell\v1.0\Modules\getFileSummary\getFileSummary.psm1

I don't understand why getFileSummary is not recognized.  I'm a complete noob at MS SQL so hopefully it's something completely obvious.  Thanks in advance for any advice.

Here is the script:

function global:getFileSummary

{

param(

    [Parameter(Position=0,mandatory=$true)]

    [string[]] $FileLocation

)

Write-Output "Inserting files"

[email protected]'

INSERT INTO tblFileSummary(

   Fully_Qualified_FileName, File_name, attributes, CreationTime, LastAccessTime, LastWriteTime,

    Length    

VALUES (

   '{0}',

   '{1}',

   '{2}',

   '{3}',

   '{4}',

   '{5}',

    '{6}'

)

'@

Invoke-Sqlcmd -Query "Truncate Table tblFileSummary" -ServerInstance SURFSUP\MSSQLSERVER -database DemoDatabase

Get-ChildItem -Recurse $FileLocation  | 

select File_name,Fully_Qualified_FileName,attributes, CreationTime, LastAccessTime, LastWriteTime,@{Label="Length";Expression={$_.Length / 1MB -as [int] }}|

   ForEach-Object {

      $SQL = $sqlstatement -f $_.FullName, $_.name,$_.attributes, $_.CreationTime, $_.LastAccessTime, $_.LastWriteTime,$_.Length                            

        Invoke-sqlcmd -Query $SQL -ServerInstance SURFSUP\MSSQLSERVER -database DemoDatabase

 

   }

Write-Output "File Inserted successfully... Below Is list of files"

}

and the output:

output

getFileSummary : The term 'getFileSummary' is not recognized as the name of a cmdlet, function, script file, or 

operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try 

again.

At line:1 char:1

+ getFileSummary 'D:\p'

+ ~~~~~~~~~~~~~~

    + CategoryInfo          : ObjectNotFound: (getFileSummary:String) [], CommandNotFoundException

    + FullyQualifiedErrorId : CommandNotFoundException

NULL


Learn more about SQL Server tools