May 5, 2010 at 5:16 am
I just want to fetch the details of all filegroups in a server like database name, size, name of the filegroup, files in the filegroup.
Thanks in advance!!
May 5, 2010 at 5:31 am
sp_helpdb master or the database name
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
May 5, 2010 at 9:36 pm
Thanks for your reply.
But it gives you the details of a particular database.
What I want is to know the filegroup details of all details in a server.
May 5, 2010 at 10:03 pm
sp_helpfile will give you the info you want, but you would need to run it against each database.
Alternately use:
SELECT * FROM dbname.dbo.sysfiles which returns the same information if you know how to interpret the differences.
Again, you would need to run this against each database, but you could write a script to run based on the database names in sys.databases.
Leo
Is it Friday yet?
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
May 5, 2010 at 10:30 pm
You can use this following code to get the details you asked for:
SELECT * FROM dbo.SYSFILES F
LEFT JOIN SYS.FILEGROUPS FG
ON F.GROUPID = FG.DATA_SPACE_ID
Cheers!!
May 5, 2010 at 10:36 pm
Also gives detail of particular database
May 5, 2010 at 10:37 pm
I have DONE that using this piece of code:
SET QUOTED_IDENTIFIER OFF
GO
alter procedure GetFileGroupDetails
AS
BEGIN
SET NOCOUNT ON
DECLARE @iRowCount INT, @iCount INT = 1
DECLARE @sSQL NVARCHAR(255), @sQuery NVARCHAR(500)
DECLARE @sDatabaseName SYSNAME
DECLARE @tblDatabaseName TABLE (Id INT IDENTITY(1,1), DatabaseName SYSNAME)
CREATE TABLE #tblFileGroupDetail (DBName SYSNAME, FileGroupName NVARCHAR(255), FileName NVARCHAR(255), SIZE INT, FilePath NVARCHAR(500))
INSERT INTO @tblDatabaseName (DatabaseName) SELECT name FROM sys.databases
SET @iRowCount = @@ROWCOUNT
SET @sQuery = "INSERT INTO #tblFileGroupDetail SELECT DB_NAME(), fg.name, file_name(df.file_id), df.size, df.physical_name
FROM sys.filegroups fg
INNER JOIN sys.database_files df
ON fg.data_space_id = df.data_space_id"
WHILE (@iCount <= @iRowCount)
BEGIN
SET @sDatabaseName = (SELECT DatabaseName from @tblDatabaseName where Id = @iCount)
SET @sSQL = "USE ["+@sDatabaseName+"] "+ @sQuery
EXEC (@sSQL)
SET @iCount += 1
END
SELECT * FROM #tblFileGroupDetail
DROP TABLE #tblFileGroupDetail
END
May 6, 2010 at 4:58 am
You can use sys.sysaltfiles view, it would give all the required fields. But instead of giving filegroup name it would give groupid.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply