April 16, 2009 at 2:32 am
Hello I am trying to write a query that will bring back a list of tables within a database and the file group name that a table is in. I want the file group name as opposed to the file group locations (NOT c:\sqlserver\).
i.e. I want something like this:
FILEGROUP TABLE
ERP A4521
JBA A4523
Can anybody help ?
April 16, 2009 at 2:46 am
This should work:
SELECT ds.Name,
OBJECT_NAME(i.object_id) AS [TableName]
FROM sys.indexes i JOIN
sys.data_spaces ds
ON i.data_space_id = ds.data_space_id
WHERE index_id 0
In case you have partitioned tables you must also join the sys.partition_schemes view.
[font="Verdana"]Markus Bohse[/font]
April 16, 2009 at 3:10 am
Thanks for the answer. I may have got my terminology wrong here. Different tables in a database have different prefixes:
ie.
erp.test
jba.test
I was those prefixes I was looking for, alongside the tablename
April 16, 2009 at 3:13 am
So you're looking for the schema and the table name.
To get these simply JOIN sys.objects to sys.schemas
[font="Verdana"]Markus Bohse[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply