How do I list files inside a folder in SQL Server without using the xp_cmdshell stored procedure?
How to list files inside a folder with SQL Server
Asked Answered
I need to show the files on a screen. An end user that runs a certain process needs to see the files inside this folder before invoking the process. The screen is built using inhouse applications, if i have a SQL query that list that without using xp_cmdshell my problem will be sorted. –
Seem
You can use xp_dirtree
It takes three parameters:
Path of a Root Directory, Depth up to which you want to get files and folders and the last one is for showing folders only or both folders and files.
EXAMPLE: EXEC xp_dirtree 'C:\', 2, 1
Do you maybe know how to list files if they are in folder where folder path contains unicode character (as š,č,ć,ž...)? –
Syntax
@Daylight It should work fine, never had problems with those characters. –
Tomas
I have a problem. If some of these characters are listed in the path then it doesn't work. –
Syntax
@HemusSan: (Bit late to react, but maybe it helps someone else) You probably need to prefix the string with an
N so the server knows it's unicode. E.g. EXEC xp_dirtree N'c:\path\with\strange\characters', 10, 1 –
Alanealanine It's noteworthy that
xp_dirtree requires the sysadmin fixed server role to run. Otherwise it will return nothing. –
Enplane I have a network drive. When running EXEC xp_dirtree again that drive, I get nothing back. Any idea? Thanks! –
Voigt
Can be done using xp_DirTree, then looping through to generate a full file path if required.
Here is an extract of a script I use to restore databases to a test server automatically. It scans a folder and all subfolders for any backup files, then returns the full path.
DECLARE @BackupDirectory SYSNAME = @BackupFolder
IF OBJECT_ID('tempdb..#DirTree') IS NOT NULL
DROP TABLE #DirTree
CREATE TABLE #DirTree (
Id int identity(1,1),
SubDirectory nvarchar(255),
Depth smallint,
FileFlag bit,
ParentDirectoryID int
)
INSERT INTO #DirTree (SubDirectory, Depth, FileFlag)
EXEC master..xp_dirtree @BackupDirectory, 10, 1
UPDATE #DirTree
SET ParentDirectoryID = (
SELECT MAX(Id) FROM #DirTree d2
WHERE Depth = d.Depth - 1 AND d2.Id < d.Id
)
FROM #DirTree d
DECLARE
@ID INT,
@BackupFile VARCHAR(MAX),
@Depth TINYINT,
@FileFlag BIT,
@ParentDirectoryID INT,
@wkSubParentDirectoryID INT,
@wkSubDirectory VARCHAR(MAX)
DECLARE @BackupFiles TABLE
(
FileNamePath VARCHAR(MAX),
TransLogFlag BIT,
BackupFile VARCHAR(MAX),
DatabaseName VARCHAR(MAX)
)
DECLARE FileCursor CURSOR LOCAL FORWARD_ONLY FOR
SELECT * FROM #DirTree WHERE FileFlag = 1
OPEN FileCursor
FETCH NEXT FROM FileCursor INTO
@ID,
@BackupFile,
@Depth,
@FileFlag,
@ParentDirectoryID
SET @wkSubParentDirectoryID = @ParentDirectoryID
WHILE @@FETCH_STATUS = 0
BEGIN
--loop to generate path in reverse, starting with backup file then prefixing subfolders in a loop
WHILE @wkSubParentDirectoryID IS NOT NULL
BEGIN
SELECT @wkSubDirectory = SubDirectory, @wkSubParentDirectoryID = ParentDirectoryID
FROM #DirTree
WHERE ID = @wkSubParentDirectoryID
SELECT @BackupFile = @wkSubDirectory + '\' + @BackupFile
END
--no more subfolders in loop so now prefix the root backup folder
SELECT @BackupFile = @BackupDirectory + @BackupFile
--put backupfile into a table and then later work out which ones are log and full backups
INSERT INTO @BackupFiles (FileNamePath) VALUES(@BackupFile)
FETCH NEXT FROM FileCursor INTO
@ID,
@BackupFile,
@Depth,
@FileFlag,
@ParentDirectoryID
SET @wkSubParentDirectoryID = @ParentDirectoryID
END
CLOSE FileCursor
DEALLOCATE FileCursor
You have to edit code to:
--no more subfolders in loop so now prefix the root backup folder SELECT @BackupFile = @BackupDirectory + '\' + @BackupFile otherwise FileNamePath is incorrect. –
Syntax Hi Steve, can you adapt the insert clause so that only certain file types get added to the temp table? Something like 'where right(filename,3) = pdf' –
Meghannmegiddo
To avoid giving the sysadmin permissions that xp_dirtree requires, do the following instead:
SQLCLR
Create a SQLCLR assembly with external access permission that returns the list of files as a result set. There are many examples of how to do this.
Here's one that creates the SQLCLR using pure SQL; it's by Jonathan Kehayias. Full explanation.
(Once installed, call it like this: SELECT * FROM master.dbo.os_directory_info('C:\', default))
SQL Script to create a directory-listing function
/*
-- To uninstall:
USE [master]
GO
DROP FUNCTION [dbo].[os_directory_info]
DROP ASSEMBLY SQLCLRNet_DirectoryBrowser
DROP USER SQLCLRNet_ExampleLogin
DROP LOGIN SQLCLRNet_ExampleLogin
DROP ASYMMETRIC KEY SQLCLRNet_ExampleKey
GO
*/
/*
* This script creates a function that lists the contents of the given directory.
* It uses a .NET CLR instead of the unsecure option of using xp_cmdshell or xp_dirtree which require sysadmin priveleges.
* It is the handywork of Jonathan Kehayias. You can find the complete explanation and source code here: https://www.sqlservercentral.com/articles/trading-in-xp_cmdshell-for-sqlclr-part-1-list-directory-contents
*
* Once installed, call the function using something like:
* SELECT *
* FROM master.dbo.os_directory_info('C:\', default)
*/
-- Enable Common Language Runtimes (.NET code plugins)
-- (Microsoft Docs on this: https://learn.microsoft.com/en-us/sql/relational-databases/clr-integration/clr-integration-enabling?view=sql-server-ver15)
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;
GO
USE [master]
GO
/****** Object: SqlAssembly [SQLCLRNet_DirectoryBrowser] Script Date: 01/23/2009 22:19:49 ******/
IF EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'SQLCLRNet_DirectoryBrowser')
DROP ASSEMBLY [SQLCLRNet_DirectoryBrowser]
GO
/****** Object: SqlAssembly [SQLCLRNet_DirectoryBrowser] Script Date: 01/23/2009 22:19:49 ******/
CREATE ASSEMBLY [SQLCLRNet_DirectoryBrowser]
AUTHORIZATION [dbo]
-- Hexadecimal representation of Precompiled Binary below. (Source code here: https://www.sqlservercentral.com/articles/trading-in-xp_cmdshell-for-sqlclr-part-1-list-directory-contents)
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103008C887A490000000000000000E00002210B0108000010000000080000000000000E2F000000200000004000000000400000200000000200000400000000000000040000000000000000800000000200001B020100030040850000100000100000000010000010000000000000100000000000000000000000C02E00004B000000004000006004000000000000000000000000000000000000006000000C000000E02D00001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000140F0000002000000010000000020000000000000000000000000000200000602E7273726300000060040000004000000006000000120000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001800000000000000000000000000004000004200000000000000000000000000000000F02E0000000000004800000002000500642200007C0B00000900000000000000000000000000000050200000800000000000000000000000000000000000000000000000000000000000000000000000897E69923F70A4BADA0B6E1E1BC164F4F71B7C6FFC97C8DA03DAA4AD965FC428DA61F34B05E1DA6E73AC47D2B64C10791C9945AD4A8AD582286E71B6AB8D9215417732F17E43650EBB0C3B31ABE323C7E627DE10E021072588858B456BF9293EFC2A9D5BDC719A0ED62237A9075402DE93B44C3A99AA3396E7D670E39B875079133002004D00000001000011000F00281000000A731100000A0A0F01281200000A2D160F01281000000A7E1300000A281400000A16FE012B01160C082D09066F1500000A0B2B10060F01281000000A6F1600000A0B2B00072A0000001330020024010000020000110002751A00000114FE0316FE010C083A8D0000000002741A0000010A03066F1700000A281800000A8103000001047E1900000A810400000105066F1A00000A281B00000A81050000010E04066F1C00000A281D00000A81060000010E05066F1E00000A281D00000A81060000010E06066F1F00000A281D00000A81060000010E07066F2000000A8C1C0000016F2100000A281800000A8103000001003882000000000274190000010B03076F1700000A281800000A8103000001047E2200000A8104000001057E2300000A81050000010E04076F1C00000A281D00000A81060000010E05076F1E00000A281D00000A81060000010E06076F1F00000A281D00000A81060000010E07076F2000000A8C1C0000016F2100000A281800000A8103000001002A1E02282400000A2A42534A4201000100000000000C00000076322E302E35303732370000000005006C000000D8020000237E0000440300007C04000023537472696E677300000000C00700000800000023555300C8070000100000002347554944000000D8070000A403000023426C6F620000000000000002000001471502000900000000FA013300160000010000001C00000002000000030000000A000000240000000C00000002000000010000000200000000000A0001000000000006004E0047000600680055000A00950080000A00B10080000A00BC0080000A00C50080000600280109010600940182010600AB0182010600C80182010600E701820106000002820106001902820106003402820106004F02820106006802090106007C0282010600A80295024B00BC0200000600EB02CB0206000B03CB020A005F034403060088037E030600A10347000600BA037E030600DC037E0306000B04470006004A047E0300000000010000000000010001000100100029000000050001000100D0200000000096009F000A0001002C21000000009100D100130003005C22000000008618EB002D000B0000000100F10000000200F60000000100FD00020002000101020003003501020004004201020005004701020006005601020007006801020008007B013900EB002D004100EB0031004900EB0031005100EB0031005900EB0031006100EB0031006900EB0031007100EB0031007900EB0031008100EB0036008900EB0031009100EB003B00A100EB004100A900EB002D00B100EB002D00190074039E02B900EB00310019009603A202C100A803A602C100AE03A902B900C903AF02B900C903B502C900E5039E021900EE03C4022100FA03CA02D1000004CE022900EE03D202C9001404D8023100EE03DD02C9002504D802C9003704D802C9005904E402090068049E0221007104CA0229007604E9020900EB002D0020007B00E8002E003300F5022E001B0015032E00230015032E002B001B032E001300F5022E003B003E032E00430015032E00530015032E00630073032E006B007C032E0073008503BC02ED020480000001000000EE0CDE9C0100000046002903000002000000000000000000000001003E000000000002000000000000000000000001007400000000000000003C4D6F64756C653E0053514C434C524E65745F4469726563746F727942726F777365722E646C6C0055736572446566696E656446756E6374696F6E73006D73636F726C69620053797374656D004F626A6563740053797374656D2E436F6C6C656374696F6E730049456E756D657261626C650053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C537472696E67006F735F6469726563746F72795F696E666F0053716C426F6F6C65616E0053716C496E7436340053716C4461746554696D65006F735F6469726563746F72795F696E666F5F46696C6C526F77002E63746F7200706174680066696C746572006F626A00616C746E616D650053797374656D2E52756E74696D652E496E7465726F705365727669636573004F75744174747269627574650069735F6469726563746F72790073697A65006372656174656461746574696D65006C61737477726974656461746574696D65006C6173746163636573736461746574696D65006174747269620053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C7475726541747472696275746500436F6D56697369626C6541747472696275746500417373656D626C7956657273696F6E4174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650053514C434C524E65745F4469726563746F727942726F77736572004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E417474726962757465006765745F56616C75650053797374656D2E494F004469726563746F7279496E666F006765745F49734E756C6C00537472696E6700456D707479006F705F457175616C6974790046696C6553797374656D496E666F0047657446696C6553797374656D496E666F730046696C65496E666F006765745F4E616D65006F705F496D706C696369740046616C7365006765745F4C656E677468004461746554696D65006765745F4372656174696F6E54696D65006765745F4C617374577269746554696D65006765745F4C61737441636365737354696D650046696C6541747472696275746573006765745F4174747269627574657300546F537472696E670054727565004E756C6C000000032000000000009ECB0FEF72F31F419C9076BDA15576330008B77A5C561934E0890800021209110D110D190008011C10110D10111110111510111910111910111910110D03200001042001010E042001010205200101114D042001010880A00024000004800000940000000602000000240000525341310004000001000100F7671782FE28927CFC962926986C6805F2FCF63C5AE36629B96FF4E04219D3AB0CBAA70305FD298B110328AFAC7ACADC6F845C263467870B4C90E707CC84F80396E8DAA766E6FE5738F73BF896119BB97C3B8B4B7841309E1E65C2FC214CBBF4AA511652033F15820F76EF404CA389D639AA8D256EBDF82E705A122C4385ABB581B401000500540E044E616D65116F735F6469726563746F72795F696E666F540E1146696C6C526F774D6574686F644E616D65196F735F6469726563746F72795F696E666F5F46696C6C526F775455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A446174614163636573730000000054020F497344657465726D696E697374696301540E0F5461626C65446566696E6974696F6E80B76E616D65206E766172636861722834303030292C2069735F6469726563746F7279206269742C2073697A655F696E5F627974657320626967696E742C206372656174655F64617465206461746574696D652C0D0A2020202020202020202020202020202020202020202020206C6173745F7772697474656E5F746F206461746574696D652C206C6173745F6163636573736564206461746574696D652C2061747472696275746573206E766172636861722834303030290320000E0320000202060E050002020E0E0520001D12650620011D12650E070703125D120902050001110D0E030611110320000A05000111150A042000116D0600011119116D04200011710306111507070312691265021F01001A53514C434C524E65745F4469726563746F727942726F7773657200000501000000002201001D4F53492052657374617572616E7420506172746E6572732C20496E632E00003401002F436F7079726967687420C2A9204F53492052657374617572616E7420506172746E6572732C20496E632E203230303900000801000701000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301000000008C887A490000000002000000C4000000FC2D0000FC0F000052534453A92E044D99B24A418E5662F67CF1EFCC03000000433A5C446F63756D656E747320616E642053657474696E67735C6A6B656861796961735C4D7920446F63756D656E74735C56697375616C2053747564696F20323030385C50726F6A656374735C53514C434C524E65745F4469726563746F727942726F777365725C53514C434C524E65745F4469726563746F727942726F777365725C6F626A5C44656275675C53514C434C524E65745F4469726563746F727942726F777365722E70646200E82E00000000000000000000FE2E0000002000000000000000000000000000000000000000000000F02E00000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500204000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000080400000000000000000000080434000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE0000010000000100DE9CEE0C00000100DE9CEE0C3F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00468030000010053007400720069006E006700460069006C00650049006E0066006F0000004403000001003000300030003000300034006200300000005C001E00010043006F006D00700061006E0079004E0061006D006500000000004F00530049002000520065007300740061007500720061006E007400200050006100720074006E006500720073002C00200049006E0063002E00000060001B000100460069006C0065004400650073006300720069007000740069006F006E0000000000530051004C0043004C0052004E00650074005F004400690072006500630074006F0072007900420072006F0077007300650072000000000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0033003300310030002E00340030003100350038000000000060001F00010049006E007400650072006E0061006C004E0061006D0065000000530051004C0043004C0052004E00650074005F004400690072006500630074006F0072007900420072006F0077007300650072002E0064006C006C000000000084002F0001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020004F00530049002000520065007300740061007500720061006E007400200050006100720074006E006500720073002C00200049006E0063002E00200032003000300039000000000068001F0001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530051004C0043004C0052004E00650074005F004400690072006500630074006F0072007900420072006F0077007300650072002E0064006C006C000000000058001B000100500072006F0064007500630074004E0061006D00650000000000530051004C0043004C0052004E00650074005F004400690072006500630074006F0072007900420072006F0077007300650072000000000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0033003300310030002E00340030003100350038000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0033003300310030002E0034003000310035003800000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000103F00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE
GO
-- Create the Asymmetric Key from the Assembly. (More about `CREATE ASYMMETRIC KEY`: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-asymmetric-key-transact-sql?view=sql-server-ver15#:~:text=An%20asymmetric%20key%20is%20a,generates%20a%20new%20key%20pair.&text=The%20private%20key%20can%20be,1024%2C%20or%202048%20bits%20long.)
CREATE ASYMMETRIC KEY SQLCLRNet_ExampleKey
FROM ASSEMBLY [SQLCLRNet_DirectoryBrowser]
GO
-- Create the Login from the Asymmetric Key
CREATE LOGIN SQLCLRNet_ExampleLogin
FROM ASYMMETRIC KEY SQLCLRNet_ExampleKey
GO
-- Grant the External Access Privilege to the Login
GRANT EXTERNAL ACCESS ASSEMBLY TO SQLCLRNet_ExampleLogin
GO
-- Create the database user for Authorization on the Assembly
CREATE USER SQLCLRNet_ExampleLogin FOR LOGIN SQLCLRNet_ExampleLogin
GO
-- Set Authorization to the Database User
ALTER AUTHORIZATION ON ASSEMBLY::[SQLCLRNet_DirectoryBrowser] TO SQLCLRNet_ExampleLogin
GO
-- Set the Assembly for External Access
ALTER ASSEMBLY [SQLCLRNet_DirectoryBrowser] WITH PERMISSION_SET = EXTERNAL_ACCESS
GO
-- Create the TSQL Function that maps to the Assembly
CREATE FUNCTION [dbo].[os_directory_info](@path [nvarchar](max), @filter [nvarchar](100) = null)
RETURNS TABLE (
[name] [nvarchar](max) NULL,
[is_directory] [bit] NULL,
[size_in_bytes] [bigint] NULL,
[create_date] [datetime] NULL,
[last_written_to] [datetime] NULL,
[last_accessed] [datetime] NULL,
[attributes] [nvarchar](max) NULL
) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLCLRNet_DirectoryBrowser].[UserDefinedFunctions].[os_directory_info]
/*
You can now run this function using something like
SELECT *
FROM master.dbo.os_directory_info('C:\', default)
*/
Original article: Trading in xp_cmdshell for SQLCLR (Part 1) - List Directory Contents
Some major benefits over xp_cmdshell and xp_dirtree:
- No need for sysadmin permissions at runtime.
- No risk of feature going away do to it being undocumented.
- Limited attack surface
Another example: Yet another TVF: returning files from a directory
If you want you can achieve this using a CLR Function/Assembly.
- Create a SQL Server CLR Assembly Project.
- Go to properties and ensure the permission level on the Connection is setup to external
- Add A Sql Function to the Assembly.
Here's an example which will allow you to select form your result set like a table.
public partial class UserDefinedFunctions
{
[SqlFunction(DataAccess = DataAccessKind.Read,
FillRowMethodName = "GetFiles_FillRow", TableDefinition = "FilePath nvarchar(4000)")]
public static IEnumerable GetFiles(SqlString path)
{
return System.IO.Directory.GetFiles(path.ToString()).Select(s => new SqlString(s));
}
public static void GetFiles_FillRow(object obj,out SqlString filePath)
{
filePath = (SqlString)obj;
}
};
And your SQL query.
use MyDb
select * From GetFiles('C:\Temp\');
Be aware though, your database needs to have CLR Assembly functionaliy enabled using the following SQL Command.
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
CLR Assemblies (like XP_CMDShell) are disabled by default so if the reason for not using XP Cmd Shell is because you don't have permission, then you may be stuck with this option as well... just FYI.
The main reason I can't use SQL xp_cmdshell is because of security reasons, I thought there might be a way to achieve this through a SQL query without writing any VB, C# or any other programming language. Thanks anyway... –
Seem
nope. You're stuck... anything that allows SQL Server to externally access resources out of process is gonna be locked down by default. –
Granicus
@JohnW.Mnisi and anyone else looking for a pure SQL approach, see Jonathan Kehayias's solution. –
Enplane
I hunted around for ages to find a decent easy solution to this and in the end found some ridiculously complicated CLR solutions so decided to write my own simple VB one. Simply create a new VB CLR project from the Database tab under Installed Templates, and then add a new SQL CLR VB User Defined Function. I renamed it to CLRGetFilesInDir.vb. Here's the code inside it...
Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.IO
-----------------------------------------------------------------------------
Public Class CLRFilesInDir
-----------------------------------------------------------------------------
<SqlFunction(FillRowMethodName:="FillRowFiles", IsDeterministic:=True, IsPrecise:=True, TableDefinition:="FilePath nvarchar(4000)")> _
Public Shared Function GetFiles(PathName As SqlString, Pattern As SqlString) As IEnumerable
Dim FileNames As String()
Try
FileNames = Directory.GetFiles(PathName, Pattern, SearchOption.TopDirectoryOnly)
Catch
FileNames = Nothing
End Try
Return FileNames
End Function
-----------------------------------------------------------------------------
Public Shared Sub FillRowFiles(ByVal obj As Object, ByRef Val As SqlString)
Val = CType(obj, String).ToString
End Sub
End Class
I also changed the Assembly Name in the Project Properties window to CLRExcelFiles, and the Default Namespace to CLRGetExcelFiles.
NOTE: Set the target framework to 3.5 if you are using anything less that SQL Server 2012.
Compile the project and then copy the CLRExcelFiles.dll from \bin\release to somewhere like C:\temp on the SQL Server machine, not your own.
In SSMS:-
CREATE ASSEMBLY <your assembly name in here - anything you like>
FROM 'C:\temp\CLRExcelFiles.dll';
CREATE FUNCTION dbo.fnGetFiles
(
@PathName NVARCHAR(MAX),
@Pattern NVARCHAR(MAX)
)
RETURNS TABLE (Val NVARCHAR(100))
AS
EXTERNAL NAME <your assembly name>."CLRGetExcelFiles.CLRFilesInDir".GetFiles;
GO
then call it
SELECT * FROM dbo.fnGetFiles('\\<SERVERNAME>\<$SHARE>\<folder>\' , '*.xls')
NOTE: Even though I changed the Permission Level to EXTERNAL_ACCESS on the SQLCLR tab under Project Properties, I still needed to run this every time I (re)created it.
ALTER ASSEMBLY [CLRFilesInDirAssembly]
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO
and wullah! that should work.
Here is some super-simple code using xp_dirtree:
-- Declare a table variable:
DECLARE @fileList TABLE([FileName] VARCHAR(50), [depth] TINYINT, [isFile] BIT);
-- Fill the table with a file list:
INSERT INTO @fileList
EXEC xp_dirtree '\\yourDrive\someFolder\', 2, 1
-- Get the list of files in the table:
SELECT [FileName],
depth,
isFile
FROM @fileList
ORDER BY [FileName];
CREATE TABLE #dbspacedetails
(
servername varchar(100),
dbname varchar(100),
physicalFileName varchar(100),
FilePath varchar(200),
allocated_space_mb decimal(10,2),
used_space_mb decimal(10,2),
free_space_mb decimal(10,2)
)
DECLARE @name VARCHAR(200)
DECLARE @path VARCHAR(1000)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE name NOT IN ('master','model','msdb')
and state_desc = 'ONLINE' order by name asc
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
set @path = 'use ['+ @name+']'+char(10)+'insert into #dbspacedetails
select
@@SERVERNAME SERVERNAME,
db_name() DBName,
name AS FileName,
Physical_name as FilePath,
size/128 AS allocated_space_m,
(size/128)-((size/128 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128)) as used_space_mb,
size/128 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128 AS free_space_mb
from sys.database_files'
exec(@path)
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
select
servername,
dbname,
physicalFileName,
FilePath,
allocated_space_mb,
used_space_mb,
free_space_mb,
CAST (CASE WHEN (((free_space_mb*1.0/(CASE WHEN allocated_space_mb = 0.00 THEN 1 ELSE allocated_space_mb END)*1.0)*100.0)) <0 THEN 0 ELSE
((free_space_mb*1.0/(CASE WHEN allocated_space_mb = 0.00 THEN 1 ELSE allocated_space_mb END)*1.0)*100.0) END AS INT) as percentfree
from #dbspacedetails
where FilePath like 'H:\%'-- and
--dbname = 'CentralDWH'
order by free_space_mb desc
--order by percentfree desc
drop table #dbspacedetails
/*
--select * from #dbspacedetails
----where FilePath like 'f:\data%'
-- order by free_space_mb desc
select *, ((free_space_mb*1.0/allocated_space_mb*1.0)*100.0) as percentfree
from #dbspacedetails
--where FilePath like 'E:\DATA01%'-- and
--dbname = 'CentralDWH'
order by free_space_mb desc
-- -- order by percentfree desc
*/
As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. –
Kaffiyeh
----create a work table
CREATE TABLE [dbo].[File_List](
[subdirectory] [varchar](250) NULL,
[Depth] [int] NULL,
[File] [int] NULL
) ON [PRIMARY]
declare @BasePath varchar(255) = 'I:\Test'
--- insert and execute system Procedure
insert into [dbo].[File_List]
EXEC master.sys.xp_dirtree @BasePath, 0, 1;
-- you will have all the files listed in the work table
There are seven existing answers to this question, including a top-voted, accepted answer with over eighty votes. Are you certain your solution hasn't already been given? If not, why do you believe your approach improves upon the existing proposals, which have been validated by the community? Offering an explanation is always useful on Stack Overflow, but it's especially important where the question has been resolved to the satisfaction of both the OP and the community. Help readers out by explaining what your answer does different and when it might be preferred. –
Nl
Very easy, just use the SQLCMD-syntax.
Remember to enable SQLCMD-mode in the SSMS, look under Query -> SQLCMD Mode
Try execute:
!!DIR
!!:GO
or maybe:
!!DIR "c:/temp"
!!:GO
SQLCMD
!! commands are executed on the client's machine, not the server. –
Rhombus © 2022 - 2024 — McMap. All rights reserved.