none
List all filegroups for all databases ? RRS feed

  • Question

  • Hello

    I would like to SELECT all filegroup on an SQL server instance, is that possible?
    Or only per database?

    Thanks,
    Jesper

    Wednesday, May 27, 2009 2:18 PM

Answers

  • Yep, just for the OP to be clear that this is after all unsuported and undocumented. :-)
    (Or write your own version, as already outlined. Not that difficult...)
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    • Marked as answer by Progrez Wednesday, May 27, 2009 4:00 PM
    Wednesday, May 27, 2009 2:47 PM
    Moderator
  • Do you mean you want to do something like:

     SELECT * FROM [MyFileGroup]

    IF so, then yes it isn't possible.

    If you want to just see the filegroups defined, you can do this:

    SELECT * FROM  sys.filegroups

    Michael Asher
    • Marked as answer by Progrez Wednesday, May 27, 2009 4:00 PM
    Wednesday, May 27, 2009 2:24 PM
  • Per database, use sys.filegroups as suggested. There's no single SELECT statement that can return all for the instance since this information isn't stored in one single tale or exposed in one signle view. And since you can have x number of database and you need to visit each databases' sys.filegroups it is difficult to write one query to do that. But just write a procedure that loop pver each database and use dynamic SQL to contruct the selectnt SELECT statement over sys.filegroups, add the result to a temp table and finally select from that temp table.
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    • Marked as answer by Progrez Wednesday, May 27, 2009 4:00 PM
    Wednesday, May 27, 2009 2:35 PM
    Moderator
  • sounds like another job for my favorite unsupported & undocumented sproc sp_MSforeachdb!

    CREATE TABLE  #fg  name sysname )
    EXEC  sp_msforeachdb  '
    use ?
    insert #fg select db_name()
    insert into #fg select name  from sys.filegroups 
    insert #fg select ''--------''
    '
    SELECT 
        
    FROM  #fg
    DROP TABLE  #fg

    • Edited by Nick Kavadias Wednesday, May 27, 2009 3:05 PM
    • Marked as answer by Progrez Wednesday, May 27, 2009 4:00 PM
    Wednesday, May 27, 2009 2:37 PM
  • Hi,
    Based on Tibork comments.
    SET NOCOUNT ON
    DECLARE
    @I INT
    SET
    @I=0
    DECLARE @Count INT
    SELECT
    @Count=COUNT([Name]) FROM SYS.DATABASES
    CREATE TABLE Temp
    (ID int identity(1,1),[Name] VARCHAR(50))
    INSERT Temp
    ([Name])
    SELECT [Name]
    FROM SYS.DATABASES
    WHILE (@I<@Count)
    BEGIN
    DECLARE @SQL varchar(4000)
    DECLARE @DatabaseName VARCHAR(50)
    SET @I=@I+1
    SELECT @DatabaseName=[Name]
    FROM Temp
    WHERE ID=@I
    SET @SQL=N'USE '+@DatabaseName+'
    GO
    SELECT * FROM sys.filegroups'
    SELECT @SQL
    END

    Thanks
    Ramesh.M

    • Marked as answer by Progrez Wednesday, May 27, 2009 4:00 PM
    Wednesday, May 27, 2009 3:02 PM
  • I don't believe so no.  Sql Server is structured to give databases a degree of autonomy; every db is expected to manage its own filegroups
    Michael Asher
    • Marked as answer by Progrez Wednesday, May 27, 2009 4:00 PM
    Wednesday, May 27, 2009 3:45 PM

All replies

  • Do you mean you want to do something like:

     SELECT * FROM [MyFileGroup]

    IF so, then yes it isn't possible.

    If you want to just see the filegroups defined, you can do this:

    SELECT * FROM  sys.filegroups

    Michael Asher
    • Marked as answer by Progrez Wednesday, May 27, 2009 4:00 PM
    Wednesday, May 27, 2009 2:24 PM
  • Per database, use sys.filegroups as suggested. There's no single SELECT statement that can return all for the instance since this information isn't stored in one single tale or exposed in one signle view. And since you can have x number of database and you need to visit each databases' sys.filegroups it is difficult to write one query to do that. But just write a procedure that loop pver each database and use dynamic SQL to contruct the selectnt SELECT statement over sys.filegroups, add the result to a temp table and finally select from that temp table.
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    • Marked as answer by Progrez Wednesday, May 27, 2009 4:00 PM
    Wednesday, May 27, 2009 2:35 PM
    Moderator
  • sounds like another job for my favorite unsupported & undocumented sproc sp_MSforeachdb!

    CREATE TABLE  #fg  name sysname )
    EXEC  sp_msforeachdb  '
    use ?
    insert #fg select db_name()
    insert into #fg select name  from sys.filegroups 
    insert #fg select ''--------''
    '
    SELECT 
        
    FROM  #fg
    DROP TABLE  #fg

    • Edited by Nick Kavadias Wednesday, May 27, 2009 3:05 PM
    • Marked as answer by Progrez Wednesday, May 27, 2009 4:00 PM
    Wednesday, May 27, 2009 2:37 PM
  • Yep, just for the OP to be clear that this is after all unsuported and undocumented. :-)
    (Or write your own version, as already outlined. Not that difficult...)
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    • Marked as answer by Progrez Wednesday, May 27, 2009 4:00 PM
    Wednesday, May 27, 2009 2:47 PM
    Moderator
  • Hi,
    Based on Tibork comments.
    SET NOCOUNT ON
    DECLARE
    @I INT
    SET
    @I=0
    DECLARE @Count INT
    SELECT
    @Count=COUNT([Name]) FROM SYS.DATABASES
    CREATE TABLE Temp
    (ID int identity(1,1),[Name] VARCHAR(50))
    INSERT Temp
    ([Name])
    SELECT [Name]
    FROM SYS.DATABASES
    WHILE (@I<@Count)
    BEGIN
    DECLARE @SQL varchar(4000)
    DECLARE @DatabaseName VARCHAR(50)
    SET @I=@I+1
    SELECT @DatabaseName=[Name]
    FROM Temp
    WHERE ID=@I
    SET @SQL=N'USE '+@DatabaseName+'
    GO
    SELECT * FROM sys.filegroups'
    SELECT @SQL
    END

    Thanks
    Ramesh.M

    • Marked as answer by Progrez Wednesday, May 27, 2009 4:00 PM
    Wednesday, May 27, 2009 3:02 PM
  • Thanks you all for your suggestions!

    Since there isn't a single table where this informations is available, is there any standard procedures that will do this, or do i HAVE script my own?

    Jesper
    Wednesday, May 27, 2009 3:41 PM
  • There's nothing that comes with the product, if that is what you mean.
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    • Marked as answer by Progrez Wednesday, May 27, 2009 4:00 PM
    • Unmarked as answer by Naomi NModerator Tuesday, November 17, 2015 12:41 PM
    Wednesday, May 27, 2009 3:45 PM
    Moderator
  • I don't believe so no.  Sql Server is structured to give databases a degree of autonomy; every db is expected to manage its own filegroups
    Michael Asher
    • Marked as answer by Progrez Wednesday, May 27, 2009 4:00 PM
    Wednesday, May 27, 2009 3:45 PM
  • Okay, bad news... but thanks for quick replies!

    Jesper
    Wednesday, May 27, 2009 4:00 PM
  • Hi,

    just found this thread and I wondered about: why not use master.sys.master_files? That view contains information about all files in the server. Checked it with 2005 and 2008.

    Cheers,
    Ralf
    Wednesday, July 29, 2009 12:14 PM
  • Hi,

    just found this thread and I wondered about: why not use master.sys.master_files? That view contains information about all files in the server. Checked it with 2005 and 2008.

    Cheers,
    Ralf

    Close, but that doesn't give us filegroup name (only id). :-)
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Wednesday, July 29, 2009 12:37 PM
    Moderator
  • SQL 2008 R2 script:

    SELECT            [SDB].name                         AS [Database Name]
                    ,    [SFG].name                          AS [File Group Name]
                    ,    [F].name                              AS [File Name]
                    ,    [SDBF].name                        AS [Database File Name]
                    ,    [SDBF].physical_name
    FROM            [master].sys.master_files        AS [F]
    INNER JOIN        sys.databases                    AS [SDB]
                    ON [SDB].database_id = [F].database_id
    INNER JOIN        sys.database_files                AS [SDBF]
                    ON                [SDBF].[file_id] = [F].[file_id]
    INNER JOIN        sys.filegroups                    AS [SFG]
                    ON [sfg].data_space_id = [F].data_space_id

    -- Optional

    WHERE            [SDB].name = N'<Databasename>'
                    AND     [SFG].name LIKE '<FileGroupName Prefix%';   

    Tuesday, May 24, 2011 7:38 AM
  • Sorry, but that query doesn't work correctly for databases outside the current database (since filegroup infor is stored in each databases' sys.database_file and sys.filegroups views).
    Tibor Karaszi, SQL Server MVP | web | blog
    Tuesday, May 24, 2011 8:48 AM
    Moderator
  • Beatiful solution!

    ps. I modified the definition of the script to present result in more 'normalized' layout

    CREATE TABLE  #fg  (db_name sysname,file_name sysname )
    
    EXEC sp_msforeachdb ' 
    use ? 
    insert #fg 
    	(db_name
    	,file_name)
    select 
    	db_name()
    	,name  
    from 
    	sys.filegroups  
    ';
    SELECT
    	*
    FROM #fg;
    
    DROP TABLE #fg;

    Kind Regards,

    KamilZet_

    Thursday, May 14, 2015 12:06 PM
  • SELECT db_name(database_id) as DatabaseName,name,type_desc,physical_name FROM sys.master_files

    Regards,

    Yashwant Vishwakarma

    yashwant-vishwakarma.strikingly.com/


    Tuesday, November 17, 2015 7:17 AM
  • You are right Ralf, I executed sys.master_files query and getting all desired details.

    Tuesday, November 17, 2015 7:29 AM