Post reply

Data and log file names

  • November 26, 2007 at 2:50 pm

    #69460

    Is there any way to view all the data file and log names (path info as well, to see the where the actual physical files being stored) for entire SQL server.

    Just like stored proc sp_helpfile but this shows the info only for current db.

    I could do select filename from sysdatabases but this again only shows the data file names only

    not the log file names. Any help would be really appreciated. Thanks

  • November 26, 2007 at 2:54 pm

    #754287

    try this SQL:

    select * from sys.sysaltfiles

  • November 26, 2007 at 2:55 pm

    #754288

    The script in the link below will give you the information you want.

    Get Server Database File Information

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89058

  • November 26, 2007 at 2:56 pm

    #754290

    Thanks alot

  • November 27, 2007 at 10:49 pm

    #754830

    Script solving your sp_helpfile issue..

    DECLARE @db varchar(1024)

    DECLARE @statement varchar(1024)

    CREATE TABLE helpfile

    (

    name varchar(22),

    fileid int,

    filename varchar(4000),

    filegroup nvarchar(100),

    size varchar(15),

    maxsize varchar(100),

    growth varchar(15),

    usage varchar(200) )

    DECLARE db CURSOR FOR

    SELECT name FROM master..sysdatabases

    OPEN db

    FETCH NEXT FROM db INTO @db

    WHILE(@@FETCH_STATUS = 0)

    BEGIN

    SET @statement = @db+'..sp_helpfile'

    INSERT INTO helpfile EXEC (@statement)

    FETCH NEXT FROM db INTO @db

    END

    CLOSE db

    DEALLOCATE db

    SELECT * FROM helpfile

    DROP TABLE helpfile

    "-=Still Learning=-"

    Lester Policarpio

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply