Get DB Files in directoy

,

the function ThousendSeperator_FN am using to add a thousend seperator for a number like:

select dbo.ThousendSeperator_FN('1234567')

and the result 1,234,567

--

to execute the procedure as the following:

exec dbo.PathDBFile_SP 'c:'

this procedure will search for any database file in c:\ and its sub directory.

CREATE PROCEDURE [dbo].[PathDBFile_SP](@SearchPath VARCHAR(200))
AS

BEGIN
 DECLARE @exec INT
 SELECT @exec = ToBeExecuted
 FROM ProcedureConfiguration
 WHERE ProcedureName = OBJECT_NAME(@@PROCID)
 IF ( @exec = 1 )
 begin
SELECT * INTO ##temp236 FROM sys.[database_files]
EXEC sp_msforeachdb'insert into ##temp236 select * from [?].sys.database_files'

DECLARE @path VARCHAR(200)
SET @path=@SearchPath
SET @path=REPLACE(@path,'''','')
IF ((SELECT SUBSTRING(@path,LEN(@path),LEN(@path)-1)) NOT LIKE '\')
BEGIN
    SET @path=@path+'\'

END


    SET @path=@path+'%'
    SELECT DISTINCT physical_name AS FilePath,[name] AS DBName,
    dbo.ThousendSeperator_FN([size]) AS FILESize
    FROM ##temp236
    WHERE physical_name LIKE @path
    ORDER BY 3
    
    DROP TABLE ##temp236
    END
    ELSE 
 BEGIN
 PRINT 'The Procedure couldnt be executed please check the configuration table'
 END
    END
    

Rate

1.8 (5)

Share

Share

Rate

1.8 (5)