22,207
社区成员
发帖
与我相关
我的任务
分享
--比如求d:\h目录下文件的情况
CREATE TABLE #a(id int identity(1,1),a VARCHAR(20))
CREATE TABLE #b(id int identity(1,1),NAME VARCHAR(20),SIZE INT,Cd VARCHAR(8),Ct VARCHAR(6),Pd VARCHAR(8),Pt VARCHAR(6),Prd VARCHAR(8),Prt VARCHAR(6),t INT)
EXEC('insert #a exec master..xp_cmdshell "dir d:\h\ /B"')
exec MASTER.dbo.xp_execresultset N'select ''insert #b exec master..xp_getfiledetails ''''d:\h\''+a+'''''''' from #a WHERE a IS NOT NULL ',N'master'
SELECT * FROM #a a INNER JOIN #b b ON a.id=b.id
DROP TABLE #a,#b
C:\Documents and Settings\Admin\My Documents\capture>for %I in (dir *.*) d
o @echo %I "," %~zI
dir ","
2009-10-08_143507.png "," 23392
2009-10-08_143539.png "," 21834
2009-10-08_143557.png "," 14639
然后
declare @s nvarchar(1000)
set @s = '2009-10-08_143557.png "," 14639'
select substring(@s,0,charindex(',',@s)),substring(@s,charindex(',',@s)+1,len(@s)-charindex(',',@s))
%~1 - expands %1 removing any surrounding quotes (")
%~f1 - expands %1 to a fully qualified path name
%~d1 - expands %1 to a drive letter only
%~p1 - expands %1 to a path only
%~n1 - expands %1 to a file name only
%~x1 - expands %1 to a file extension only
%~s1 - expanded path contains short names only
%~a1 - expands %1 to file attributes
%~t1 - expands %1 to date/time of file
%~z1 - expands %1 to size of file
%~$PATH:1 - searches the directories listed in the PATH environment variable and expands %1 to the fully qualified name of the first one found. If the environment variable name is not defined or the file is not found by the search, then this modifier expands to the empty string
--========+++++++++++++++++++++++++++++++++++==========
--======= 每天都在进步,却依然追不上地球的自传=========
--======= By: zc_0101 At:2009-10-14 11:18:51=========
--========++++++++++++++++++++++++++++++++++++=========
--> 测试数据: #a
if object_id('tempdb.dbo.#a') is not null drop table #a
create table #a (id int identity,col1 varchar(8000))
insert into #a
select '2009-08-01 04:30 27696697 All200901$837_20090801.bcp.gz ' union all
select '2009-09-01 04:29 24224724 All200902$250_20090901.bcp.gz ' union all
select '2009-10-01 04:29 26425563 All200903$663_20091001.bcp.gz ' union all
select '2009-10-01 04:29 0 0 Copy of bcp_error.log '
----------------查询------------
--自定义函数
CREATE FUNCTION F_SQLSERVER_SPLIT(@Long_str varchar(8000),@split_str varchar(100))
RETURNS @tmp TABLE(
ID inT IDENTITY PRIMARY KEY,
short_str varchar(8000)
)
AS
BEGIN
DECLARE @long_str_Tmp varchar(8000),@short_str varchar(8000),@split_str_length int
SET @split_str_length = LEN(@split_str)
IF CHARINDEX(@split_str,@Long_str)=1
SET @long_str_Tmp=SUBSTRING(@Long_str,@split_str_length+1,LEN(@Long_str)-@split_str_length)
ELSE
SET @long_str_Tmp=@Long_str
IF CHARINDEX(REVERSE(@split_str),REVERSE(@long_str_Tmp))>1
SET @long_str_Tmp=@long_str_Tmp+@split_str
ELSE
SET @long_str_Tmp=@long_str_Tmp
IF CHARINDEX(@split_str,@long_str_Tmp)=0
Insert INTO @tmp select @long_str_Tmp
ELSE
BEGIN
WHILE CHARINDEX(@split_str,@long_str_Tmp)>0
BEGIN
SET @short_str=SUBSTRING(@long_str_Tmp,1,CHARINDEX(@split_str,@long_str_Tmp)-1)
DECLARE @long_str_Tmp_LEN INT,@split_str_Position_END int
SET @long_str_Tmp_LEN = LEN(@long_str_Tmp)
SET @split_str_Position_END = LEN(@short_str)+@split_str_length
SET @long_str_Tmp=REVERSE(SUBSTRING(REVERSE(@long_str_Tmp),1,@long_str_Tmp_LEN-@split_str_Position_END))
IF @short_str<>'' Insert INTO @tmp select @short_str
END
END
IF (SELECT MAX(ID) FROM @tmp)>4
BEGIN
DECLARE @str varchar(8000)
SELECT @str=ISNULL(@str+' ','')+short_str FROM @tmp WHERE ID>=4
UPDATE @tmp SET short_str=@str WHERE ID=4
DElETE FROM @tmp WHERE ID>4
END
RETURN
END
--正式查询
declare @s varchar(max)
select @s=isnull(@s+' union all'+char(13),'')+' select short_str FileDate,(select short_str from dbo.f_sqlserver_split((select col1 from #a where id='+ltrim(id)+'),'' '') where id=3) FileSize'+char(13)+
',(select short_str from dbo.f_sqlserver_split((select col1 from #a where id='+ltrim(id)+'),'' '') where id=4) FileName'+char(13)+
' from dbo.f_sqlserver_split((select col1 from #a where id='+LTRIM(ID)+'),'' '') where id=1' from #a
exec(@s)
----------------结果--------------
/*
FileDate FileSize FileName
2009-08-01 27696697 All200901$837_20090801.bcp.gz
2009-09-01 24224724 All200902$250_20090901.bcp.gz
2009-10-01 26425563 All200903$663_20091001.bcp.gz
2009-10-01 0 0 Copy of bcp_error.log
*/
declare @str varchar(8000),@FileName sysname,@FileSize int,@FileTime smalldatetime
set @str= '2009-10-01 04:29 80 0 Copy of bc$p_error.log'
set @FileSize=substring(@str,23,14)
set @FileName=substring(@str,37,len(@str)-35)
select @FileSize, @FileName
exec xp_dirtree 'c:\' , 0
exec xp_dirtree 'c:\' , 1
declare @str varchar(8000),@FileName sysname,@FileSize int,@FileTime smalldatetime
set @str= '2009-10-01 04:29 80 0 Copy of bc$p_error.log'
--这个@str是通过dir命令得到的,如何得到下面的结果呢?
@FileSize=80
@FileName='0 Copy of bc$p_error.log'
SELECT SUBSTRING(@str,37,50)
declare @str varchar(8000),@FileName sysname,@FileSize int,@FileTime smalldatetime
set @str= '2009-10-01 04:29 80 0 Copy of bc$p_error.log'
set @FileSize=substring(@str,34,2)
set @FileName=''''+substring(@str,37,24)+''''
print @FileSize
print @FileName
declare @str varchar(8000),@FileName sysname,@FileSize int,@FileTime smalldatetime
set @str= '2009-10-01 04:29 80 0 Copy of bc$p_error.log'
--这个@str是通过dir命令得到的,如何得到下面的结果呢?
@FileSize=80
@FileName='0 Copy of bc$p_error.log'
SELECT SUBSTRING(@str,34,50)