How do I see details on filegroups in SQL Server 2005 beyond what is shown in db properties view?

check
sp_spaceused

http://msdn.microsoft.com/en-us/library/ms188776.aspx

--

and
Collecting SQL Server database usage information for free space and trending
http://www.mssqltips.com/tip.asp?tip=1426

Sorry for the delay...I have been messing with the code you sent.  Neither code has all the parts I am looking for...each one is missing an element.
I much prefer the coding on the second link, as it gives me alot of great information, but the one thing missing from it, that is the critical piece for me, is the tables in the database associated with that space being reported.  It lists the database name, the filegroup name, the space it takes, percentage of space, when it was made....all sorts of great stuff, just not the name of the table associated with the space...is there an easy way to put that in the code??
I am extremely new to SQL Server, so I am not aware of what it can/can't do, or how to do most of it.
Thanks,
matt

please clarify: 1st script is for db level info
so do you need tables size too?
if 'yes'
it is something like this one:
http://www.mssqltips.com/tip.asp?tip=1177

ok, so that last link provide for the table size is terrific.  now, is there an easy way to combine the functionality of that into the programming of this link?
http://www.mssqltips.com/tip.asp?tip=1426
That would truly give me everything (and more) for what I am looking for, but it would give it to me in one output file that I could then take to my Sys Admin team to try and manage some of the elements of the database in a better way.
I noted you mentioned the 1st script you listed (http://msdn.microsoft.com/en-us/library/ms188776.aspx) gave db level info, but I wasn't sure what to plug in where, so I didn't really see much detail comparted to that of the 2nd link you gave me (http://www.mssqltips.com/tip.asp?tip=1426).  As I said, I am very new to SQL Server so I don't really understand much of the T-SQL language yet.  I know enough to get our group things they need based on some programming that was setup by an expert before he left our company, but I have ALOT to learn obviously.
The whole mission for me is to try and keep the various filegroups for my database to at or below 10GB in memory.  So as data in a filegroup approaches that level, I then create a new one and build into it for awhile, and so on.  The guy that built this database, stated that guideline before he left, BUT, he didn't follow it when he was building the database, so I am trying to go in and clean up the mess so to speak.  There are a couple filegroups that are closer to 30GB in space, but I can't seem to figure out what the heck is in them, so I want a tool that shows me all the elements of a particular database...the table names, the size of the tables, the size of the indexes associated, the filegroup they belong to and how much space in that filegroup that particular table and index are taking up, etc.
So this last link you gave me, gives me the table and index data i am looking for, now i am hoping to add on the filegroup information as well, which I noticed was in the 2nd script you sent me, so now wondering how I can somehow combine the two scripts into one file output.

file group info for tables?
can you please post prototype of result that you expect?

If I go into my database on SQL Server 2005 and right click its names to go to Properties, the following screen pops-up when I choose "Files" tab.  These show the filegroups and files, their size and location.  I want to see the data on this screen, but I want it broken down one more layer.  So for instance, the Logic Name and Filegroup called "a1-build" in the screenshot has 3193MB used up of the overall server space.  I want to then see what tables and indexes are being allocated to that particular Logic Name and Filegroup, with each table/index name listed, along with how much memory it is using.  So if table a1 and a1_lvl1 of my database make up the total of 3193MB, I would see each one listed showing that a1 table uses 1004MB, its one index uses 996MB, and then a1_lvl1, with no indexes of its own, takes up the remaining 1193MB of space on this particular 3193MB of memory space.
Does that work, or should I put it into an excel spreadsheet so you can see it clearer?
Just let me know.
Thanks,
Matt

Database-Properties-Window-SQL-2.doc

I am at a loss on this question at this point.  Though the answers provided thus far do offer some good information, it is not what I am looking for, and I don't have enough experience in SQL programming to mesh the two together somehow and produce the final snapshot.
I keep being told this question is open and I know they will close it soon if I don't chime in, so I guess this is my chime in again for help?

Thanks,
matt

something like these - just need to wrap:

go
--use [pubs]
go
SELECT
CAST(cast(g.name as varbinary(256)) AS sysname) AS [Name],
g.data_space_id AS [ID],
g.is_default AS [IsDefault],
g.is_read_only AS [ReadOnly],
CAST(ISNULL((select sum(gs.size)*convert(float,8) from sys.database_files gs where gs.data_space_id = g.data_space_id), 0) AS float) AS [Size]
FROM
sys.filegroups AS g
ORDER BY
[Name] ASC
go
 
 
			create table #tmpspc (Fileid int, FileGroup int, TotalExtents int, UsedExtents int, Name sysname, FileName nchar(520))
			insert #tmpspc EXEC ('dbcc showfilestats')
		
 
 
SELECT
CAST(cast(g.name as varbinary(256)) AS sysname) AS [FileGroup_Name],
s.name AS [Name],
(tspc.TotalExtents - tspc.UsedExtents)*convert(float,64) AS [AvailableSpace],
s.physical_name AS [FileName],
CAST(CASE s.is_percent_growth WHEN 1 THEN s.growth ELSE s.growth*8 END AS float) AS [Growth],
CAST(CASE when s.growth=0 THEN 99 ELSE s.is_percent_growth END AS int) AS [GrowthType],
s.file_id AS [ID],
CAST(CASE s.file_id WHEN 1 THEN 1 ELSE 0 END AS bit) AS [IsPrimaryFile],
CASE when s.max_size=-1 then -1 else s.max_size * CONVERT(float,8) END AS [MaxSize],
s.size * CONVERT(float,8) AS [Size],
CAST(tspc.UsedExtents*convert(float,64) AS float) AS [UsedSpace],
CAST(case s.state when 6 then 1 else 0 end AS bit) AS [IsOffline],
s.is_read_only AS [IsReadOnly],
s.is_media_read_only AS [IsReadOnlyMedia],
s.is_sparse AS [IsSparse]
FROM
sys.filegroups AS g
INNER JOIN sys.master_files AS s ON (s.type = 0 and s.database_id = db_id() and (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)
INNER JOIN #tmpspc tspc ON tspc.Fileid = s.file_id
ORDER BY
[FileGroup_Name] ASC,[Name] ASC
 
			drop table #tmpspc
		
 
go
 
 
			create table #tmplogspc (DatabaseName sysname, LogSize float, SpaceUsedPerc float, Status bit)
			insert #tmplogspc EXEC ('dbcc sqlperf(logspace)')
		
 
 
SELECT
s.name AS [Name],
s.physical_name AS [FileName],
CAST(CASE s.is_percent_growth WHEN 1 THEN s.growth ELSE s.growth*8 END AS float) AS [Growth],
CAST(CASE when s.growth=0 THEN 99 ELSE s.is_percent_growth END AS int) AS [GrowthType],
s.file_id AS [ID],
CASE when s.max_size=-1 then -1 else s.max_size * CONVERT(float,8) END AS [MaxSize],
s.size * CONVERT(float,8) AS [Size],
tspclog.LogSize * tspclog.SpaceUsedPerc * 10.24 AS [UsedSpace],
CAST(case s.state when 6 then 1 else 0 end AS bit) AS [IsOffline],
s.is_read_only AS [IsReadOnly],
s.is_media_read_only AS [IsReadOnlyMedia],
s.is_sparse AS [IsSparse]
FROM
sys.master_files AS s
INNER JOIN #tmplogspc tspclog ON 
			tspclog.DatabaseName = db_name()
		
WHERE
(s.type = 1 and s.database_id = db_id())
ORDER BY
[Name] ASC
 
			drop table #tmplogspc
		

Select allOpen in new window

OK, so that second SELECT-CAST group is awesome!  Love that one alot and will use it for certain for sys admin work.  The data shown from that select statement series is all about the filegroups and its extended attributes, which is terrific....it is the one step further part that seems to be the hard part...and maybe what I am asking for isn't even possible in one query.  In fact, if this part I am about to explain again needs to be in a seperate query, I am good with that and can just run the two jobs seperately.  This one first to show which filegroups need attention, and the second one, the one I am about to explain again, for WHERE to attack the administration work.
In the query results from the SELECT-CAST statement provided, one line specified the filegroup "a1_build".  It shows that 6,601,600 Kb of memory are being used in that filegroup.  What I want to know via a query, is what tables and indexes in the database are being attributed to that filegroup.  I can find that out by opening every table and every index in the database and looking at the properties to see what filegroup it is attributed to, but that is very time consuming and completely silly.  I would prefer to just kick off a query that would show me something like this:
Filegroup   Tablename/Index   MemoryUsedByTable/Index    TotalMemoryInFilegroup  
a1_build     a1_lvl1                      500,000                                      6,601,600
a1_build     a2_lvl1                      1,500,000                                   6,601,600
a1_build     a_master                  3,100,000                                   6,601,600
a1_build     item_loc                    900,000                                      6,601,600
a1_build     a3                               601,600                                      6,601,600
Then if there was another filegroup called "demand" with 2,000,000 Kb, I would want to see the detail listed on that filegroup using the same format, all in the same output table.

Filegroup   Tablename/Index   MemoryUsedByTable/Index    TotalMemoryInFilegroup  
a1_build     a1_lvl1                      500,000                                      6,601,600
a1_build     a2_lvl1                      1,500,000                                   6,601,600
a1_build     a_master                  3,100,000                                   6,601,600
a1_build     item_loc                    900,000                                      6,601,600
a1_build     a3                               601,600                                      6,601,600
demand      history                       1,350,000                                   2,000,000
demand      fcst_id                        650,000                                      2,000,000
 Additional detail that administrators out there think is useful, by all means put it in.  I have no issue with excess data, just hoping to get this core portion of data for quick database management as filegroups grow to large.
Does this make sense?

Thanks,
Matt

just checking in to see if there is any chatter on this one....it appears the experts may be a bit stumped on this one??
Cheers,
matt

OK, going to give this one more week, and then I guess I have no choice but to close the ticket as unanswered.  While there is some good stuff here, it is not what I asked for, so I don't see how I can award points, unless someone else has a suggestion.
I am open to ideas.

Thanks,
matt

Hello Moderators....I would LOVE to close this question so I stop getting your reminders, but I don't have a complete answer yet.  There is some good information in here relating to what I asked, but not the correct solution I am looking for, so how to end the question?
I think some credit should be given to those who worked on it, even though it isn't exactly what I wanted.  To close it without giving credit to them for their work seems plain wrong.  But I don't want to say an answer is right and mislead future readers either.
Help?

Thanks,
Matt

I would REALLY LOVE to close the question with the right answer...that would just make my month I tell ya.
 
 

Thanks Vee_Mod...I really appreciate all the help!!

Cheers,
Matt

I haven't been able to see all the code so far, but will check it tomorrow.

But it sounds like you're talking about about how Oracle handles tables and tablespaces. I'm not sure if that even exists in SQL.

I was wrong -- this can be done. Learning something new.

I think the issue is that you can't get there from here in one step. Looking at the stuff that shows the table sizes, and the stuff that shows the database info, both are using some sort of temp table that doesn't retain the data afterward.

The one to many of the databases to the tables would give you a very redundant report depending on the tables and design of your DB(s).

Also it doesn't give you a true trending other than manually copying the data to somewhere else.

It would be relatively easy to turn these around to store the data in permanent tables in this (or another) server. Then you can make them a stored procedure and run them on a regular schedule from the SQL Agent.  After the data is stored, you can and then use some reporting tool such as Crystal, Access or whatever to link the DB header overall and reporting on the structure underneath. That is instead of going to mildly Cartesian/de-normalized table design.

--create permanent table
CREATE TABLE ReportingDB.dbo.TableSizing(    
        tablename sysname
,       row_count INT
,       reserved VARCHAR(50) collate database_default
,       data VARCHAR(50) collate database_default
,       index_size VARCHAR(50) collate database_default
,       unused VARCHAR(50) collate database_default 
,       Database_Name VARCHAR(50)
,       DateCollected DateTime Default GetDate()
--------------------------------------------------
-- create the custom stored proc to get the data
-- and put it in the perm table. Then schedule a
-- SQL agent job to run this as an sp_MSforeachdb 
-- on the server. Convert the other script and just
-- schedule it for a regular run.
--------------------------------------------------
CREATE CP_TableSizing
AS
BEGIN try 
DECLARE @table_name VARCHAR(500) ; 
DECLARE @schema_name VARCHAR(500) ; 
DECLARE @tab1 TABLE(
        tablename VARCHAR (500) collate database_default
,       schemaname VARCHAR(500) collate database_default
); 
 
INSERT INTO @tab1 
SELECT t1.name
,       t2.name 
FROM sys.tables t1 
INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id );   
 
DECLARE c1 CURSOR FOR 
SELECT t2.name + '.' + t1.name  
FROM sys.tables t1 
INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id );   
 
OPEN c1; 
FETCH NEXT FROM c1 INTO @table_name;
WHILE @@FETCH_STATUS = 0 
BEGIN  
        SET @table_name = REPLACE(@table_name, '[',''); 
        SET @table_name = REPLACE(@table_name, ']',''); 
 
        -- make sure the object exists before calling sp_spacedused
        IF EXISTS(SELECT OBJECT_ID FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(@table_name))
        BEGIN
                INSERT INTO ReportingDB.dbo.TableSizing EXEC sp_spaceused @table_name, false, db_name,
					 cast(convert(varchar,getdate(),101) as datetime) ;
        END
        
        FETCH NEXT FROM c1 INTO @table_name; 
END; 
CLOSE c1; 
DEALLOCATE c1; 
SELECT t1.*
,       t2.schemaname 
FROM @temp_table t1 
INNER JOIN @tab1 t2 ON (t1.tablename = t2.tablename )
ORDER BY  schemaname,tablename;
END try 
BEGIN catch 
SELECT -100 AS l1
,       ERROR_NUMBER() AS tablename
,       ERROR_SEVERITY() AS row_count
,       ERROR_STATE() AS reserved
,       ERROR_MESSAGE() AS data
,       1 AS index_size, 1 AS unused, 1 AS schemaname 
END catch

Select allOpen in new window

I really like the idea of a stored procedure to see trending jimpen...brilliant!  Unfortunately, I am very new to SQL Server, so when I tried to copy this and paste it into a new query on my database in SQL Server 2005, it gave the following errors.  I looked through, but wasn't sure what it refers too...I have noticed SQL's error coding is far from descriptive....at least it points out line numbers...a step of from Access that just says NO, but doesn't help when you are a novice like myself.

Msg 156, Level 15, State 1, Line 18
Incorrect syntax near the keyword 'CREATE'.
Msg 156, Level 15, State 1, Line 50
Incorrect syntax near the keyword 'convert'.
Msg 1087, Level 15, State 2, Line 59
Must declare the table variable "@temp_table".
 
I know before someone asked for a prototype, so I made one manually...took forever, and was outdated the next day, which is why I am looking for something I can run live and dump to a table or external DB.  Below is the attachment of my efforts.  While repetitive on some lines, it gives the basic idea.  From what I can see on your code, it looks like it would grab most of this, except maybe the filegroup name was missing, which would be great for knowing what tables are in what filegroup, when the filegroup gets to big or needs to be moved....kind of the point behind the whole thing really.  I am being asked to keep each filegroup under 10GB in size, so when the filegroup gets bigger than that, I want to know what tables are in that filegroup so I can then attempt to move them eslewhere, shrink the db or even delete if needed.  A script/query would be ALOT better than looking at the properties of every table and index on the server as you may have guessed.
Thanks,
Matt

SQL-Mapping.xls

Took me a while to dig this out. Can you test the query on yours as I don't have any DBs with filegroups.

SELECT t1.name 
,       t2.name ,  FG.FileGroupNm, FG.TblType
FROM sys.tables t1 
INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id ) 
LEFT JOIN (SELECT o.[name] AS TblName, o.[type] as TblType,  f.[name] As FileGroupNm
	FROM sys.indexes i
	INNER JOIN sys.filegroups f
	ON i.data_space_id = f.data_space_id
	INNER JOIN sys.all_objects o
	ON i.[object_id] = o.[object_id]
	--WHERE i.data_space_id = 2 --* New FileGroup*
	GROUP BY o.[name], o.[type],  f.[name]) FG 
	ON (t1.name = FG.TblName)

Select allOpen in new window

Hi jimpen,
So that worked for showing me the table names and what filegroup they were in.  The name field just showed 'dbo' in the output, so not certain what it is supposed to show.  As well, TblType showed 'U' for all of the tables...again, not sure what it is supposed to show or what that means...could be completely right, just letting you know.
So is the idea that these jobs would be run seperately, one to get the size and then one to get this breakdown, or were you testing to make sure this part worked first before adding the size of the tables/indexes into the results window?
Thanks again,
Matt

That is part of the larger procedure I'm working on. But I'm tied up in my day job for a moment. Don't abandon hope yet. I can find it useful for myself as well. I just need to get it in the stack.

No worries jimpen.  I am a very patient person and am just grateful for all your help above all else.  I wish I wasn't so SQL challenged so I could help...but I keep learning a bit more each day.

Cheers,
Matt

jimpen, this is pure genius!!  That is exactly what I was hoping to get in the results from this query.  AWESOME!!  Thanks you so much for your time and effort, and it is great to hear that you believe you have use for this type of data as well.  Absolute bonus.
Have a great day and thanks again!!
Cheers,
Matt

A HUGE timesaver for my administration needs.  Hats off jimpen!!

Glad to be of assistance. May all your days get brighter and brighter.

Just to give credit to where its due. The base part of the SP is derived from the work at mssqltips.

Determining space used for each table in a SQL Server database
Written By: Greg Robidoux -- 2/12/2007
http://www.mssqltips.com/tip.asp?tip=1177

You may want to modify the code and put that in as a comment (wrap it with a /** and after with **/ ) to indicate its a comment. And maybe add a link to this Q too. That way anyone who comes after you can get back to the source instead of just saying "WTF?".

great idea jimpen.  I have updated the code accordingly.  Thanks!  Documentation goes a LONG way I have found, so thanks for letting me know.
 
Cheers,
Matt