--drop table #dbfiles

--deallocate cursor1

------

declare cursor1 cursor for

    SELECT name from sys.databases

    where state=0

-- 临时表  www.2cto.com

CREATE TABLE #dbfiles

(

dbname nvarchar(100),

filetype nvarchar(100),

filename nvarchar(200)

)

declare @name  varchar(100) --数据库名

declare @sql nvarchar(1024) --临时sql 语句

open cursor1

fetch next from cursor1 into @name

while @@fetch_status = 0

begin

    set @sql = 'INSERT INTO #dbfiles SELECT ''' + @name + ''',name,filename  from ' + @name + '.sys.sysfiles';

    EXEC(@sql)

    fetch next from cursor1 into @name

end

close cursor1                   --关闭游标

deallocate cursor1

select * from #dbfiles

--where filename like '%ftysdatabase%'

 --F:\ftysdatabase\ty_video.mdf

drop table #dbfiles --删除临时文件