반응형
SQL Server를 사용하여 폴더 내의 파일을 나열하는 방법
Server 파일을 SQL xp_cmdshell저장 프로시저?
xp_dirtree를 사용할 수 있습니다.
세 가지 매개 변수가 필요합니다.
루트 디렉터리의 경로, 파일과 폴더를 가져올 최대 깊이, 마지막 하나는 폴더만 표시하거나 폴더와 파일을 모두 표시하는 것입니다.
예:EXEC xp_dirtree 'C:\', 2, 1
xp_DirTree를 사용하여 수행한 다음 필요한 경우 전체 파일 경로를 생성합니다.
다음은 데이터베이스를 테스트 서버에 자동으로 복원하는 데 사용하는 스크립트의 발췌입니다.폴더 및 모든 하위 폴더에서 백업 파일을 검색한 다음 전체 경로를 반환합니다.
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
sysadmin에게 다음과 같은 권한을 부여하지 않으려면xp_dirtree대신 다음을 수행합니다.
SQLCLR
파일 목록을 결과 집합으로 반환하는 외부 액세스 권한이 있는 SQLCLR 어셈블리를 만듭니다.이것을 하는 방법에는 많은 예가 있습니다.
여기 순수 SQL을 사용하여 SQLCLR을 만드는 것이 있습니다. 조나단 케하야스가 쓴 것입니다.자세한 설명.
과 같이 .)SELECT * FROM master.dbo.os_directory_info('C:\', default))
디렉터리 나열 함수를 만드는 SQL 스크립트
/*
-- 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)
*/
원본 기사:SQLCLR을 위한 xp_cmdshell 거래(Part 1) - 디렉터리 내용 나열
다음에 비해 몇 가지 주요 이점xp_cmdshell그리고.xp_dirtree:
- 런타임에 sysadmin 권한이 필요하지 않습니다.
- 문서화되지 않은 상태에서 기능이 사라질 위험이 없습니다.
- 제한된 공격면
원하는 경우 CLR 기능/어셈블리를 사용하여 이를 수행할 수 있습니다.
- SQL Server CLR 어셈블리 프로젝트를 만듭니다.
- 속성으로 이동하여 Connection의 권한 수준이 외부로 설정되었는지 확인합니다.
- 어셈블리에 SQL 함수를 추가합니다.
다음은 결과 집합의 형식을 표와 같이 선택할 수 있는 예제입니다.
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;
}
};
그리고 SQL 쿼리.
use MyDb
select * From GetFiles('C:\Temp\');
그러나 데이터베이스에서 다음 SQL 명령을 사용하여 CLR 어셈블리 기능을 사용하도록 설정해야 합니다.
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
: CLR 어셈예리):XP_CMDShell)는 기본적으로 비활성화되어 있으므로 XP Cmd Shell을 사용하지 않는 이유가 권한이 없기 때문이라면 이 옵션도 계속 사용할 수 있습니다.참고로.
저는 이것에 대한 괜찮은 쉬운 해결책을 찾기 위해 오랫동안 찾아다녔고 결국 터무니없이 복잡한 CLR 솔루션을 발견했고 그래서 저만의 간단한 VB를 쓰기로 결정했습니다.설치된 템플릿의 데이터베이스 탭에서 새 VB CLR 프로젝트를 생성한 다음 새 SQL CLR VB 사용자 정의 함수를 추가하기만 하면 됩니다.이름을 CLRGetFilesInDir.vb로 변경했습니다.안에 코드가 있어요
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
또한 프로젝트 속성 창의 어셈블리 이름을 CLRExcelFiles로 변경하고 기본 네임스페이스를 CLRGetExcelFiles로 변경했습니다.
참고: SQL Server 2012보다 적은 것을 사용하는 경우에는 대상 프레임워크를 3.5로 설정합니다.
프로젝트를 컴파일한 다음 \bin\release의 CLExcelFiles.dll을 자신의 것이 아닌 SQL Server 시스템의 C:\temp와 같은 위치로 복사합니다.
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
그럼 끝냅시다
SELECT * FROM dbo.fnGetFiles('\\<SERVERNAME>\<$SHARE>\<folder>\' , '*.xls')
참고: SQLCLR 탭의 Properties에서 EXTERNAL_ACCESS로 권한 수준을 변경했지만, (재작성)할 때마다 실행해야 했습니다.
ALTER ASSEMBLY [CLRFilesInDirAssembly]
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO
그리고 wullah! 그것은 효과가 있을 것입니다.
CREATE TABLE #dbspace 세부 정보(서버 이름 varchar(100), dbname varchar(100), physical FileName varchar(100), FilePath varchar(200), allocated_space_mb decimal(10,2), used_space_mb decimal(10,2)
DELL @name VARCHAR(200) DELL @path VARCHAR(1000)
다음에 대한 db_cursor 커서 선언
sys.messages에서 이름을 선택합니다. 여기서 이름은 'master', 'model', 'msdb') 및 state_messages = 'ONLINE' 순으로 이름을 지정합니다.
OPEN db_cursor
db_cursor에서 @name으로 다음 가져오기
반면 @@FETCH_STATUS = 0
시작한다.
set @path = 'use ['+ @name+']'+char(10)+'를 #dbspace 세부 정보에 삽입합니다.
@@SERVERNAME, db_name() DBName, 이름 AS FileName, 파일 이름으로 물리적_name, 파일 경로, size/128 AS allocated_space_m, (size/128)-(size/128 - CAST(파일 속성) ASINT(이름, ''Space Used'' AS INT)/128)를 사용_mbspace_m, size/128 CAST(데이터베이스에서 사용 가능)를 선택합니다.
exec(@path)
FETCH NEXT FROM db_cursor INTO @name
끝.
CLOSE db_cursor
DELOCALATE db_cursor
서버 이름, 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) 100.0) <0> <0> ELSE (할당된 경우) 0 (free_mb_mb_mb_mb_mb_mb_mb_mb_mb_mbEND AS INT). #dbspace에서 백분율로 자유로운 경우 FilePath 세부 정보(예: 'H:%' 및 --dbname = 'CentralD')WH' order by free_space_mb desc --% free desc
드롭 테이블 #dbspace 세부 정보
/*
--dbspacedetails #dbspacedetails ----여기서 FilePath('f:\data%') --free_space_mbdesk 순으로 선택합니다.
(free_space_mb1.0/allocated_space_mb*1.0)*100.0을 #dbspacedetails에서 백분율로 선택합니다. 여기서 FilePath는 'E:\'와 같습니다.DATA01%' 및 --dbname = 'CentralD'WH' order by free_space_mb desc -- -- free desc의 백분율 순서
*/
매우 간단합니다. SQLCMD 구문을 사용하십시오.
SSMS에서 SQLCMD 모드를 활성화해야 합니다. 쿼리 -> SQLCMD 모드에서 확인하십시오.
실행 시도:
!!DIR
!!:GO
또는 아마도:
dir "!!DIR "c:/temp"
!!:GO
언급URL : https://stackoverflow.com/questions/11559846/how-to-list-files-inside-a-folder-with-sql-server
반응형
'programing' 카테고리의 다른 글
| 스프링 데이터 탄력적 검색을 사용한 탄력적 검색 Rest 클라이언트 (0) | 2023.06.25 |
|---|---|
| Oracle Optimizer가 동일한 SELECT에서 여러 힌트를 사용합니까? (0) | 2023.06.25 |
| Springboot : BeanDefinitionStore 예외:구성 클래스를 구문 분석하지 못했습니다. (0) | 2023.06.25 |
| 트랜잭션 범위가 너무 일찍 완료됨 (0) | 2023.06.25 |
| 팬더 read_excel에서 Excel 셀 배경색을 얻으시겠습니까? (0) | 2023.06.25 |