Post reply

Correlate dbname and his dbfile (data+log)

  • February 8, 2006 at 3:49 am

    #111893

    Hi guys,

    I need to retrieve the names of db files.

    the problem is that I would like to retrieve them with SQL.

    I know there is the system table: sysfiles, but I have to set the db before to run it....

    use mydb

    select * from sysfile.

    The problem is that the db name is a variable and I do know his name...

    So...how can I correlated sysfiles with db name?

    thank a lot

  • February 8, 2006 at 4:07 am

    #619470

    instead of sysfile select the name from master.sysdatabases

  • February 8, 2006 at 4:10 am

    #619471

    I'm not a fan of dynamic SQL, but this is a place where it will help.

    DECLARE @DBName varchar(20)

    SET @DBName = 'msdb'

    DECLARE @sSQL VARCHAR(500)

    SET @sSQL = 'SELECT name, filename FROM ' + @DBName + '.dbo.sysfiles'

    EXECUTE (@sSQL)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 8, 2006 at 4:16 am

    #619473

    thank...

    very easy....

  • February 8, 2006 at 4:58 am

    #619480

    Though undocumented, here's another way which avoids dynamic SQL.

    -- list name and filenames for all db's on the server

    exec sp_msForEachDB 'SELECT name, filename FROM ?.dbo.sysfiles'

    /Kenneth

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

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