Getting a file name from Query Analyzer

Hi all,

im trying to write a stored procedure that will basically browse a folder and get me the first file that it sees. Is there any way that I can do this in TSQL or using CLR in C#? I was thinking something along the lines of using the dos dir command and triyng to pipe it into a variable, not sure how to go about doing this. Any suggestions?

dir /b ...gives me the bare file names, but it lists all the files in the folder, any way that i can just get the first file ( i dont really care what file).

create table #filelist
(
files varchar(500)
)

truncate table #filelist

insert #filelist
EXEC xp_cmdshell 'dir c:*.* /b'

select top 1 * from #filelist

|||

This might help out:

http://stevekass.com/blog/wp-content/Folders/sql/TextDriver.htm

You can use TOP 1 to get just one file name.

Steve Kass

Drew University

http://www.stevekass.com

YoungEngineer@.discussions.microsoft.com wrote:

> Hi all,

>

> im trying to write a stored procedure that will basically browse a

> folder and get me the first file that it sees. Is there any way that I

> can do this in TSQL or using CLR in C#? I was thinking something along

> the lines of using the dos dir command and triyng to pipe it into a

> variable, not sure how to go about doing this. Any suggestions?

>

> dir /b ...gives me the bare file names, but it lists all the files in

> the folder, any way that i can just get the first file ( i dont really

> care what file).

>

>

>

>

GetDate()

I would like to exec master..xp_fixeddrives which will give you the list of
physical drives sqlserver sees. Output is Drives, FreeSpace from
xp_fixeddrives.
I would like to write this information to a table with Drive,
FreeSpace,SampleTime.
The SampleTime will be the getdate() function.
Please help me with this issue.
ThanksCREATE TABLE #DriveData
(
Drive CHAR(2),
FreeSpace BIGINT,
SampleTime DATETIME NOT NULL DEFAULT GETDATE()
);
INSERT #DriveData
(
Drive,
FreeSpace
)
EXEC master..xp_fixeddrives;
SELECT Drive, FreeSpace, SampleTime
FROM #DriveData;
DROP TABLE #DriveData;
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:23A81366-D8DD-4833-B25C-96DF0561090D@.microsoft.com...
>I would like to exec master..xp_fixeddrives which will give you the list of
> physical drives sqlserver sees. Output is Drives, FreeSpace from
> xp_fixeddrives.
> I would like to write this information to a table with Drive,
> FreeSpace,SampleTime.
> The SampleTime will be the getdate() function.
> Please help me with this issue.
> Thanks
>
>|||CREATE TABLE #diskspace(
drivename VARCHAR(50),
MBFree INT)
INSERT INTO #diskspace(drivename,MBFree) exec master..xp_fixeddrives
SELECT * FROM #diskspace|||CREATE TABLE #diskspace(
drivename VARCHAR(50),
MBFree INT)
INSERT INTO #diskspace(drivename,MBFree) exec master..xp_fixeddrives
SELECT * FROM #diskspace|||Note the addition of the date field to the table.
CREATE TABLE #diskspace(
drivename VARCHAR(50),
MBFree INT,
sampledate DATETIME default getdate()
)sql

Get the description of INFORMATION_SCHEMA.COLUMNS

I understand that sp_help and sp_columns can give a description of a table
for me.
However, sp_help sees only the tables in the current schema, and
sp_columns 'COLUMNS', 'INFORMATION_SCHEMA'
returns an empty result, too.
How can I query the descriptions of tables in other than the current schema?
USE master
EXEC sp_help 'INFORMATION_SCHEMA.COLUMNS'
EXEC sp_columns 'COLUMNS'
David Portas
SQL Server MVP

Get the description of INFORMATION_SCHEMA.COLUMNS

I understand that sp_help and sp_columns can give a description of a table
for me.
However, sp_help sees only the tables in the current schema, and
sp_columns 'COLUMNS', 'INFORMATION_SCHEMA'
returns an empty result, too.
How can I query the descriptions of tables in other than the current schema?
USE master
EXEC sp_help 'INFORMATION_SCHEMA.COLUMNS'
EXEC sp_columns 'COLUMNS'
David Portas
SQL Server MVP

Get the description of INFORMATION_SCHEMA.COLUMNS

I understand that sp_help and sp_columns can give a description of a table
for me.
However, sp_help sees only the tables in the current schema, and
sp_columns 'COLUMNS', 'INFORMATION_SCHEMA'
returns an empty result, too.
How can I query the descriptions of tables in other than the current schema?USE master
EXEC sp_help 'INFORMATION_SCHEMA.COLUMNS'
EXEC sp_columns 'COLUMNS'
--
David Portas
SQL Server MVP
--

Get the description of INFORMATION_SCHEMA.COLUMNS

I understand that sp_help and sp_columns can give a description of a table
for me.
However, sp_help sees only the tables in the current schema, and
sp_columns 'COLUMNS', 'INFORMATION_SCHEMA'
returns an empty result, too.
How can I query the descriptions of tables in other than the current schema?USE master
EXEC sp_help 'INFORMATION_SCHEMA.COLUMNS'
EXEC sp_columns 'COLUMNS'
David Portas
SQL Server MVP
--