Have SQL server Display Directory File name.

[EM1107]

I would like to have SQL server find the information in a directory and give me all the detail of the documents that it content in it.

I have created a script that give me all the information but it does not give me the name of the document.

How can I get the script to do that for me.

Here is the script I HAVE DONE:

--THE FOLLOWING IS AN EXAMPLE OF HOW TO USE SQL SERVER TO RETURN OPERATING SYSTEM FILE INFORMATION TO A TABLE
--AND OUTPUT THE RESULTS TO A DELIMITED FILE.

IF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME = 'NTFS_FILEDETAILS')
DROP TABLE NTFS_FILEDETAILS
GO
--ALL FIELDS CREATED AS NVARCHAR TO AVOID DATE OVERFLOW EXCEPT FOR INT.
create table NTFS_FILEDETAILS
(
ALTERNATE_NAME nvarchar(128),
FILE_SIZE int,
CREATION_DATE nvarchar(128),
CREATION_TIME nvarchar(128),
LAST_WRITTEN_DATE nvarchar(128),
LAST_WRITTEN_TIME nvarchar(128),
LAST_ACCESSED_DATE nvarchar(128),
LAST_ACCESSED_TIME nvarchar(128),
FILE_ATTRIBUTES INT
)
GO
Declare @FULLPATH nvarchar(255)
DECLARE @COMMAND nvarchar (255)
set @COMMAND = 'EXEC MASTER.DBO.XP_GETFIELDETAILS'
DECLARE TableCursor CURSOR for
SELECT A.DOCSERVER_LOC + A.PATH + B.PATH AS FULPATH FROM DOCSADM.PROFILE A, DOCSADM.COMPONENTS B
WHERE A.DOCNUMBER = B.DOCNUMBER
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @FULLPATH
while @@FETCH_STATUS = 0

Begin

INSERT INTO NTFS_FILEDETAILS
(
ALTERNATE_NAME,
FILE_SIZE,
CREATION_DATE,
CREATION_TIME,
LAST_WRITTEN_DATE,
LAST_WRITTEN_TIME,
LAST_ACCESSED_DATE,
LAST_ACCESSED_TIME,
FILE_ATTRIBUTES
)
EXEC ('MASTER.DBO.XP_GETFILEDETAILS' +' ' +'"' + @FULLPATH +'"')
fetch next from TableCursor into @FULLPATH
END
CLOSE TableCursor
DEALLOCATE TableCursor

 

[OOBIE]

Not sure this will help but the following list the attributes in a directory



DECLARE @DirCmd VARCHAR(150),@FileName VARCHAR(100)
SELECT @DirCmd = 'dir ' + '\\SERVER\DATA\WORKFILES\'
CREATE TABLE #DirResults (Diroutput VARCHAR(255) NULL)
INSERT #DirResults
EXEC master.dbo.xp_cmdshell @DirCmd
SELECT * FROM #DirResults
Drop table #DirResults

Happy Coding!

 

[funcrecall12]

Hello,

Master Database can provide the Info. even without scripts.

Jerry

 

[EM1107]

If the Master database can profide the inormation, how can you get it to display?