May 11, 2006 at 8:55 am
Hi,
Mssql 2000/SP4
Filegroups (3): PRIMARY; FG2; FG3
How do I confirm/determine that tables/indexes are on a given Filegroup?
Many thanks. Jeff
May 11, 2006 at 10:11 am
As far as I know, you can determine if a clustered index and therefore the table data is on a filegroup with this script:
http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=1499
May 11, 2006 at 12:36 pm
Hi Steve,
that's exactly want i'm looking for. It beats trying work-arounds "sp_helpfilegroup".
Many thanks. Jeff
May 12, 2006 at 6:29 am
I believe you can also right-click on the table in question and do an All Tasks => Generate SQL Script. It will include an ON [filegroup]. Just in case there's no PK...
May 30, 2006 at 6:24 am
I've tried the script and although it works fine on Master and Msdb I can't get it work on the User databases.
The error I'm getting is
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'Name'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'IndID'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'Name'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'IndID'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'Name'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'IndID'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'GroupName'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'Name'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'Name'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'Name'.
Any ideas ??
May 30, 2006 at 2:43 pm
This should work. You may want to join to sysobjects to limit the scope to just user tables??
select distinct object_name(id), si.groupid, sf.groupname
from sysindexes si, sysfilegroups sf
where si.groupid=sf.groupid
Recommend only using this with caution. Microsoft recommends not querying system tables directly and the results could change in the future.
Brian
September 13, 2006 at 10:18 am
You must be using it on a database that has a case sensitive collation
try
select 'Object name' = case si.indid
when 1 then so.name
else si.name
end,
'Object Type' = case
when si.indid 1 then so.name
else ' '
end,
'FileGroup name' = sfg.groupname,
'System Filename' = sf.name
from sysfilegroups sfg inner join sysfiles sf
on sfg.groupid = sf.groupid
inner join sysindexes si
on sfg.groupid = si.groupid
inner join sysobjects so
on si.id = so.id
where so.type = 'U'
and si.name not like '#_%' escape '#'
and so.name not in ('dtproperties')
order by 2 desc, 3, 1
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply