List of Tables in a FileGroup

I guess we could play semantic games, but a heap is not an index, even
though there is a row for it in sys.indexes.
And, as I'm sure Russell knows (it was probably just his fingers that didn't
know) the index_id for a heap is 0.
A heap has no concept of leaf pages, it just has the data.

The thing that I would have said 'almost but not quite' about, would be if
the table is partitioned. Then the entire table may not be on a single
filegroup. In the code below, data_space_id may not map to a filegroup, it
may map to a partition scheme, which can be composed of hundred of different
filegroups.

--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
www.SQLTuners.com


"Russell Fields" wrote in message

> rgn,
>
> Almost, but not quite. What actually exists in filegroups are indexes.
> Of course, index_id=1 is the clustered index or index_id=1 is the heap,
> either of which include the leaf (data) pages. (Every index for a table
> could be on a different filegroup.)
>
> So, here is a start:
>
> SELECT OBJECT_NAME(i.id) AS ObjectName,
> i.name AS IndexName, ds.data_space_id, ds.type
> FROM sys.indexes i
> JOIN sys.data_spaces ds
> ON i.data_space_id = ds.data_space_id
>
>
> RLF
>
>
> "rgn" wrote in message
>
>> Is there a way to get a list of all the tables in a particular File
>> Group.
>>
>> rgn
>
 >> Stay informed about: List of Tables in a FileGroup 

Kalen, Thanks both for correcting my mistyping and for expanding the
explanation. - RLF

"Kalen Delaney" wrote in message

>I guess we could play semantic games, but a heap is not an index, even
>though there is a row for it in sys.indexes.
> And, as I'm sure Russell knows (it was probably just his fingers that
> didn't know) the index_id for a heap is 0.
> A heap has no concept of leaf pages, it just has the data.
>
> The thing that I would have said 'almost but not quite' about, would be if
> the table is partitioned. Then the entire table may not be on a single
> filegroup. In the code below, data_space_id may not map to a filegroup, it
> may map to a partition scheme, which can be composed of hundred of
> different filegroups.
>
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> www.SQLTuners.com
>
>
> "Russell Fields" wrote in message
>
>> rgn,
>>
>> Almost, but not quite. What actually exists in filegroups are indexes.
>> Of course, index_id=1 is the clustered index or index_id=1 is the heap,
>> either of which include the leaf (data) pages. (Every index for a table
>> could be on a different filegroup.)
>>
>> So, here is a start:
>>
>> SELECT OBJECT_NAME(i.id) AS ObjectName,
>> i.name AS IndexName, ds.data_space_id, ds.type
>> FROM sys.indexes i
>> JOIN sys.data_spaces ds
>> ON i.data_space_id = ds.data_space_id
>>
>>
>> RLF
>>
>>
>> "rgn" wrote in message
>>
>>> Is there a way to get a list of all the tables in a particular File
>>> Group.
>>>
>>> rgn
>>
>
>
 >> Stay informed about: List of Tables in a FileGroup 

It's just so rare that I find anything that needs correcting in your
explanations ...

--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
www.SQLTuners.com


"Russell Fields" wrote in message

> Kalen, Thanks both for correcting my mistyping and for expanding the
> explanation. - RLF
>
> "Kalen Delaney" wrote in message
>
>>I guess we could play semantic games, but a heap is not an index, even
>>though there is a row for it in sys.indexes.
>> And, as I'm sure Russell knows (it was probably just his fingers that
>> didn't know) the index_id for a heap is 0.
>> A heap has no concept of leaf pages, it just has the data.
>>
>> The thing that I would have said 'almost but not quite' about, would be
>> if the table is partitioned. Then the entire table may not be on a single
>> filegroup. In the code below, data_space_id may not map to a filegroup,
>> it may map to a partition scheme, which can be composed of hundred of
>> different filegroups.
>>
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> www.SQLTuners.com
>>
>>
>> "Russell Fields" wrote in message
>>
>>> rgn,
>>>
>>> Almost, but not quite. What actually exists in filegroups are indexes.
>>> Of course, index_id=1 is the clustered index or index_id=1 is the heap,
>>> either of which include the leaf (data) pages. (Every index for a
>>> table could be on a different filegroup.)
>>>
>>> So, here is a start:
>>>
>>> SELECT OBJECT_NAME(i.id) AS ObjectName,
>>> i.name AS IndexName, ds.data_space_id, ds.type
>>> FROM sys.indexes i
>>> JOIN sys.data_spaces ds
>>> ON i.data_space_id = ds.data_space_id
>>>
>>>
>>> RLF
>>>
>>>
>>> "rgn" wrote in message
>>>
>>>> Is there a way to get a list of all the tables in a particular File
>>>> Group.
>>>>
>>>> rgn
>>>
>>
>>
>
 >> Stay informed about: List of Tables in a FileGroup 

Hi Rgn,

Here is a script that I got from Internet ...

/*==================================================================================

NAME: Get extended filegroup info for all DB tables and views
(location of data pages, indexes, etc)

DESCRIPTION: This script returns the following extended filegroup
info
For all DB tables and indexed views:
1. The table (or view) name
2. The type of the object (i.e., table or view)
3. The filegroup location of the clustered index (when
exists), non-clustered indexes, data pages (if other
than the clustered index), and the NTEXT/TEXT/IMAGE
column data.
4. The indid (from sysindexes) that corresponds to
the attributes listed in (3).
5. The filegroup name and id on which the items listed
in (3) are located.

USER PARAMETERS: NA

RESULTSET: TableOrViewName, [Table/View], AttributeName,
AttributeType,
AttributeIndid, FileGroupName, FileGroupID

RESULTSET SORT: TableOrViewName, AttributeIndid

USING TABLES/VIEWS: INFORMATION_SCHEMA.TABLES
sysindexes

REVISIONS

DATE DEVELOPER DESCRIPTION OF REVISION VERSION
========= =============== =================================
===========
04/18/2005 Omri Bahat Initial release 1.00

==================================================================================
Copyright © SQL Farms Solutions, www.sqlfarms.com. All rights reserved.
This code may be used at no charge as long as this copyright notice is not
removed.
==================================================================================*/

-- Get all DB tables and views filegroup info

SET NOCOUNT ON

-- This table will hold the collected filegroup information
IF OBJECT_ID('tempdb..#tblFileGroupInfo', 'U') IS NOT NULL
DROP TABLE #tblFileGroupInfo

CREATE TABLE #tblFileGroupInfo(
TableOrViewName NVARCHAR(128),
[Table/View] VARCHAR(16),
AttributeName NVARCHAR(128),
AttributeType VARCHAR(128),
AttributeIndid INT,
FileGroupName NVARCHAR(128),
FileGroupID INT)


-- Get the file group information for all indexes of all tables and indexed
views
-- on the database. Note that auto-statistics created by SQL server are
excluded,
-- since they are hardly of interest.
INSERT INTO #tblFileGroupInfo(
TableOrViewName,
[Table/View],
AttributeName,
AttributeType,
AttributeIndid,
FileGroupName,
FileGroupID)
SELECT a.TABLE_NAME,
CASE WHEN a.TABLE_TYPE = 'VIEW' THEN 'View'
ELSE 'Table'
END,
ISNULL(b.[name], 'NA'),
CASE WHEN b.indid = 0 THEN 'Table Row Data (table has no
clustered index)'
WHEN b.indid = 1 THEN 'Clustered Index (and table row data)'
WHEN b.indid = 255 THEN 'TEXT/NTEXT/IMAGE/XML Column Data'
ELSE 'Non Clustered Index'
END,
b.indid,
FILEGROUP_NAME(b.groupid),
b.groupid
FROM INFORMATION_SCHEMA.TABLES a WITH (NOLOCK)
INNER JOIN sysindexes b WITH (NOLOCK)
ON OBJECT_ID(a.TABLE_NAME) = b.[id]
WHERE INDEXPROPERTY(b.[id], b.[name], 'IsAutoStatistics') = 0


SET NOCOUNT OFF

SELECT *
FROM #tblFileGroupInfo WITH (NOLOCK)
ORDER BY TableOrViewName ASC, AttributeIndid ASC

-- Cleanup

IF OBJECT_ID('tempdb..#tblFileGroupInfo', 'U') IS NOT NULL
DROP TABLE #tblFileGroupInfo
GO
HTH,
AbbA


"rgn" wrote in message

> Is there a way to get a list of all the tables in a particular File Group.
>
> rgn
 >> Stay informed about: List of Tables in a FileGroup 

I use this query and it gives me not only tables but in which filegroup all the related indexes too.

select case when indexes.type_desc in ('HEAP','CLUSTERED')
then 'Table-' + indexes.type_desc
else ' NC Index' end as indexType,

rtrim(cast(
case when indexProperty(objects.object_id,indexes.name,'IsUnique') = 1
then 'unique ' else '' end +
case when isNull(objectProperty(object_id(schemas.name + '.'
+ indexes.name),'IsConstraint'),0) = 1
then 'constraint ' else '' end +
case when indexProperty(objects.object_id,indexes.name,'IsAutoStatistics') = 1
then 'auto ' else '' end +
case when indexProperty(objects.object_id,indexes.name,'IsStatistics') = 1
then 'statistics ' else '' end +
case when indexProperty(objects.object_id,indexes.name,'IsHypothetical') = 1
then 'hypothetical ' else '' end
as varchar(30))) as indexProperties,
schemas.name + '.' + objects.name as tableName,
coalesce(indexes.name,'') as indexName,
filegroups.name as filegroup
from sys.indexes as indexes
join sys.objects as objects
on indexes.object_id = objects.object_id
join sys.schemas as schemas
on objects.schema_id = schemas.schema_id
join sys.filegroups as filegroups
on indexes.data_space_id = filegroups.data_space_id
where objectproperty(indexes.object_id,'IsMSShipped') = 0
order by tableName, case when indexes.type_desc in ('HEAP','CLUSTERED') then 0 else 1 end

> On Monday, November 24, 2008 1:03 PM rg wrote:

> Is there a way to get a list of all the tables in a particular File Group.
>
> rgn


>> On Monday, November 24, 2008 1:59 PM Russell Fields wrote:

>> rgn,
>>
>> Almost, but not quite. What actually exists in filegroups are indexes. Of
>> course, index_id=1 is the clustered index or index_id=1 is the heap, either
>> of which include the leaf (data) pages. (Every index for a table could be
>> on a different filegroup.)
>>
>> So, here is a start:
>>
>> SELECT OBJECT_NAME(i.id) AS ObjectName,
>> i.name AS IndexName, ds.data_space_id, ds.type
>> FROM sys.indexes i
>> JOIN sys.data_spaces ds
>> ON i.data_space_id = ds.data_space_id
>>
>>
>> RLF
>>
>>
>> "rgn" wrote in message
>>


>>> On Monday, November 24, 2008 2:37 PM Kalen Delaney wrote:

>>> I guess we could play semantic games, but a heap is not an index, even
>>> though there is a row for it in sys.indexes.
>>> And, as I'm sure Russell knows (it was probably just his fingers that didn't
>>> know) the index_id for a heap is 0.
>>> A heap has no concept of leaf pages, it just has the data.
>>>
>>> The thing that I would have said 'almost but not quite' about, would be if
>>> the table is partitioned. Then the entire table may not be on a single
>>> filegroup. In the code below, data_space_id may not map to a filegroup, it
>>> may map to a partition scheme, which can be composed of hundred of different
>>> filegroups.
>>>
>>> --
>>> HTH
>>> Kalen Delaney, SQL Server MVP
>>> www.InsideSQLServer.com
>>> www.SQLTuners.com
>>>
>>>
>>> "Russell Fields" wrote in message
>>>


>>>> On Monday, November 24, 2008 2:53 PM Russell Fields wrote:

>>>> Kalen, Thanks both for correcting my mistyping and for expanding the
>>>> explanation. - RLF


>>>>> On Monday, November 24, 2008 4:45 PM Kalen Delaney wrote:

>>>>> it is just so rare that I find anything that needs correcting in your
>>>>> explanations ...
>>>>>
>>>>> --
>>>>> HTH
>>>>> Kalen Delaney, SQL Server MVP
>>>>> www.InsideSQLServer.com
>>>>> www.SQLTuners.com


>>>>>> On Monday, November 24, 2008 11:11 PM Abba wrote:

>>>>>> Hi Rgn,
>>>>>>
>>>>>> Here is a script that I got from Internet ...
>>>>>>
>>>>>> /*==================================================================================
>>>>>>
>>>>>> NAME: Get extended filegroup info for all DB tables and views
>>>>>> (location of data pages, indexes, etc)
>>>>>>
>>>>>> DESCRIPTION: This script returns the following extended filegroup
>>>>>> info
>>>>>> For all DB tables and indexed views:
>>>>>> 1. The table (or view) name
>>>>>> 2. The type of the object (i.e., table or view)
>>>>>> 3. The filegroup location of the clustered index (when
>>>>>> exists), non-clustered indexes, data pages (if other
>>>>>> than the clustered index), and the NTEXT/TEXT/IMAGE
>>>>>> column data.
>>>>>> 4. The indid (from sysindexes) that corresponds to
>>>>>> the attributes listed in (3).
>>>>>> 5. The filegroup name and id on which the items listed
>>>>>> in (3) are located.
>>>>>>
>>>>>> USER PARAMETERS: NA
>>>>>>
>>>>>> RESULTSET: TableOrViewName, [Table/View], AttributeName,
>>>>>> AttributeType,
>>>>>> AttributeIndid, FileGroupName, FileGroupID
>>>>>>
>>>>>> RESULTSET SORT: TableOrViewName, AttributeIndid
>>>>>>
>>>>>> USING TABLES/VIEWS: INFORMATION_SCHEMA.TABLES
>>>>>> sysindexes
>>>>>>
>>>>>> REVISIONS
>>>>>>
>>>>>> DATE DEVELOPER DESCRIPTION OF REVISION VERSION
>>>>>> ========= =============== =================================
>>>>>> ===========
>>>>>> 04/18/2005 Omri Bahat Initial release 1.00
>>>>>>
>>>>>> ==================================================================================
>>>>>> Copyright ?? SQL Farms Solutions, www.sqlfarms.com. All rights reserved.
>>>>>> This code may be used at no charge as long as this copyright notice is not
>>>>>> removed.
>>>>>> ==================================================================================*/
>>>>>>
>>>>>> -- Get all DB tables and views filegroup info
>>>>>>
>>>>>> SET NOCOUNT ON
>>>>>>
>>>>>> -- This table will hold the collected filegroup information
>>>>>> IF OBJECT_ID('tempdb..#tblFileGroupInfo', 'U') IS NOT NULL
>>>>>> DROP TABLE #tblFileGroupInfo
>>>>>>
>>>>>> CREATE TABLE #tblFileGroupInfo(
>>>>>> TableOrViewName NVARCHAR(128),
>>>>>> [Table/View] VARCHAR(16),
>>>>>> AttributeName NVARCHAR(128),
>>>>>> AttributeType VARCHAR(128),
>>>>>> AttributeIndid INT,
>>>>>> FileGroupName NVARCHAR(128),
>>>>>> FileGroupID INT)
>>>>>>
>>>>>>
>>>>>> -- Get the file group information for all indexes of all tables and indexed
>>>>>> views
>>>>>> -- on the database. Note that auto-statistics created by SQL server are
>>>>>> excluded,
>>>>>> -- since they are hardly of interest.
>>>>>> INSERT INTO #tblFileGroupInfo(
>>>>>> TableOrViewName,
>>>>>> [Table/View],
>>>>>> AttributeName,
>>>>>> AttributeType,
>>>>>> AttributeIndid,
>>>>>> FileGroupName,
>>>>>> FileGroupID)
>>>>>> SELECT a.TABLE_NAME,
>>>>>> CASE WHEN a.TABLE_TYPE = 'VIEW' THEN 'View'
>>>>>> ELSE 'Table'
>>>>>> END,
>>>>>> ISNULL(b.[name], 'NA'),
>>>>>> CASE WHEN b.indid = 0 THEN 'Table Row Data (table has no
>>>>>> clustered index)'
>>>>>> WHEN b.indid = 1 THEN 'Clustered Index (and table row data)'
>>>>>> WHEN b.indid = 255 THEN 'TEXT/NTEXT/IMAGE/XML Column Data'
>>>>>> ELSE 'Non Clustered Index'
>>>>>> END,
>>>>>> b.indid,
>>>>>> FILEGROUP_NAME(b.groupid),
>>>>>> b.groupid
>>>>>> FROM INFORMATION_SCHEMA.TABLES a WITH (NOLOCK)
>>>>>> INNER JOIN sysindexes b WITH (NOLOCK)
>>>>>> ON OBJECT_ID(a.TABLE_NAME) = b.[id]
>>>>>> WHERE INDEXPROPERTY(b.[id], b.[name], 'IsAutoStatistics') = 0
>>>>>>
>>>>>>
>>>>>> SET NOCOUNT OFF
>>>>>>
>>>>>> SELECT *
>>>>>> FROM #tblFileGroupInfo WITH (NOLOCK)
>>>>>> ORDER BY TableOrViewName ASC, AttributeIndid ASC
>>>>>>
>>>>>> -- Cleanup
>>>>>>
>>>>>> IF OBJECT_ID('tempdb..#tblFileGroupInfo', 'U') IS NOT NULL
>>>>>> DROP TABLE #tblFileGroupInfo
>>>>>> GO
>>>>>> HTH,
>>>>>> AbbA
>>>>>>
>>>>>>
>>>>>> "rgn" wrote in message
>>>>>>


>>>>>> Submitted via EggHeadCafe - Software Developer Portal of Choice
>>>>>> ComponentOne Studio for ASP.NET AJAX - Free License Giveaway
>>>>>> http://www.eggheadcafe.com/tutorials/aspnet/ce98ce1f-2b5d-4ec8-b6d5-a1...651514e
 >> Stay informed about: List of Tables in a FileGroup 

Actually, from what I was told by a Microsoft engineer a heap is an index.

Supposedly it still has a clustered index. The index is just based on some random hashing algorithm. Basically all tables in SQL Server, heaps or otherwise, are what Oracle would call an Index Organized Table.

I have never verified this but that was what I was told.

> On Monday, November 24, 2008 1:03 PM rg wrote:

> Is there a way to get a list of all the tables in a particular File Group.
>
> rgn


>> On Monday, November 24, 2008 1:59 PM Russell Fields wrote:

>> rgn,
>>
>> Almost, but not quite. What actually exists in filegroups are indexes. Of
>> course, index_id=1 is the clustered index or index_id=1 is the heap, either
>> of which include the leaf (data) pages. (Every index for a table could be
>> on a different filegroup.)
>>
>> So, here is a start:
>>
>> SELECT OBJECT_NAME(i.id) AS ObjectName,
>> i.name AS IndexName, ds.data_space_id, ds.type
>> FROM sys.indexes i
>> JOIN sys.data_spaces ds
>> ON i.data_space_id = ds.data_space_id
>>
>>
>> RLF
>>
>>
>> "rgn" wrote in message
>>


>>> On Monday, November 24, 2008 2:37 PM Kalen Delaney wrote:

>>> I guess we could play semantic games, but a heap is not an index, even
>>> though there is a row for it in sys.indexes.
>>> And, as I'm sure Russell knows (it was probably just his fingers that didn't
>>> know) the index_id for a heap is 0.
>>> A heap has no concept of leaf pages, it just has the data.
>>>
>>> The thing that I would have said 'almost but not quite' about, would be if
>>> the table is partitioned. Then the entire table may not be on a single
>>> filegroup. In the code below, data_space_id may not map to a filegroup, it
>>> may map to a partition scheme, which can be composed of hundred of different
>>> filegroups.
>>>
>>> --
>>> HTH
>>> Kalen Delaney, SQL Server MVP
>>> www.InsideSQLServer.com
>>> www.SQLTuners.com
>>>
>>>
>>> "Russell Fields" wrote in message
>>>


>>>> On Monday, November 24, 2008 2:53 PM Russell Fields wrote:

>>>> Kalen, Thanks both for correcting my mistyping and for expanding the
>>>> explanation. - RLF


>>>>> On Monday, November 24, 2008 4:45 PM Kalen Delaney wrote:

>>>>> it is just so rare that I find anything that needs correcting in your
>>>>> explanations ...
>>>>>
>>>>> --
>>>>> HTH
>>>>> Kalen Delaney, SQL Server MVP
>>>>> www.InsideSQLServer.com
>>>>> www.SQLTuners.com


>>>>>> On Monday, November 24, 2008 11:11 PM Abba wrote:

>>>>>> Hi Rgn,
>>>>>>
>>>>>> Here is a script that I got from Internet ...
>>>>>>
>>>>>> /*==================================================================================
>>>>>>
>>>>>> NAME: Get extended filegroup info for all DB tables and views
>>>>>> (location of data pages, indexes, etc)
>>>>>>
>>>>>> DESCRIPTION: This script returns the following extended filegroup
>>>>>> info
>>>>>> For all DB tables and indexed views:
>>>>>> 1. The table (or view) name
>>>>>> 2. The type of the object (i.e., table or view)
>>>>>> 3. The filegroup location of the clustered index (when
>>>>>> exists), non-clustered indexes, data pages (if other
>>>>>> than the clustered index), and the NTEXT/TEXT/IMAGE
>>>>>> column data.
>>>>>> 4. The indid (from sysindexes) that corresponds to
>>>>>> the attributes listed in (3).
>>>>>> 5. The filegroup name and id on which the items listed
>>>>>> in (3) are located.
>>>>>>
>>>>>> USER PARAMETERS: NA
>>>>>>
>>>>>> RESULTSET: TableOrViewName, [Table/View], AttributeName,
>>>>>> AttributeType,
>>>>>> AttributeIndid, FileGroupName, FileGroupID
>>>>>>
>>>>>> RESULTSET SORT: TableOrViewName, AttributeIndid
>>>>>>
>>>>>> USING TABLES/VIEWS: INFORMATION_SCHEMA.TABLES
>>>>>> sysindexes
>>>>>>
>>>>>> REVISIONS
>>>>>>
>>>>>> DATE DEVELOPER DESCRIPTION OF REVISION VERSION
>>>>>> ========= =============== =================================
>>>>>> ===========
>>>>>> 04/18/2005 Omri Bahat Initial release 1.00
>>>>>>
>>>>>> ==================================================================================
>>>>>> Copyright ?? SQL Farms Solutions, www.sqlfarms.com. All rights reserved.
>>>>>> This code may be used at no charge as long as this copyright notice is not
>>>>>> removed.
>>>>>> ==================================================================================*/
>>>>>>
>>>>>> -- Get all DB tables and views filegroup info
>>>>>>
>>>>>> SET NOCOUNT ON
>>>>>>
>>>>>> -- This table will hold the collected filegroup information
>>>>>> IF OBJECT_ID('tempdb..#tblFileGroupInfo', 'U') IS NOT NULL
>>>>>> DROP TABLE #tblFileGroupInfo
>>>>>>
>>>>>> CREATE TABLE #tblFileGroupInfo(
>>>>>> TableOrViewName NVARCHAR(128),
>>>>>> [Table/View] VARCHAR(16),
>>>>>> AttributeName NVARCHAR(128),
>>>>>> AttributeType VARCHAR(128),
>>>>>> AttributeIndid INT,
>>>>>> FileGroupName NVARCHAR(128),
>>>>>> FileGroupID INT)
>>>>>>
>>>>>>
>>>>>> -- Get the file group information for all indexes of all tables and indexed
>>>>>> views
>>>>>> -- on the database. Note that auto-statistics created by SQL server are
>>>>>> excluded,
>>>>>> -- since they are hardly of interest.
>>>>>> INSERT INTO #tblFileGroupInfo(
>>>>>> TableOrViewName,
>>>>>> [Table/View],
>>>>>> AttributeName,
>>>>>> AttributeType,
>>>>>> AttributeIndid,
>>>>>> FileGroupName,
>>>>>> FileGroupID)
>>>>>> SELECT a.TABLE_NAME,
>>>>>> CASE WHEN a.TABLE_TYPE = 'VIEW' THEN 'View'
>>>>>> ELSE 'Table'
>>>>>> END,
>>>>>> ISNULL(b.[name], 'NA'),
>>>>>> CASE WHEN b.indid = 0 THEN 'Table Row Data (table has no
>>>>>> clustered index)'
>>>>>> WHEN b.indid = 1 THEN 'Clustered Index (and table row data)'
>>>>>> WHEN b.indid = 255 THEN 'TEXT/NTEXT/IMAGE/XML Column Data'
>>>>>> ELSE 'Non Clustered Index'
>>>>>> END,
>>>>>> b.indid,
>>>>>> FILEGROUP_NAME(b.groupid),
>>>>>> b.groupid
>>>>>> FROM INFORMATION_SCHEMA.TABLES a WITH (NOLOCK)
>>>>>> INNER JOIN sysindexes b WITH (NOLOCK)
>>>>>> ON OBJECT_ID(a.TABLE_NAME) = b.[id]
>>>>>> WHERE INDEXPROPERTY(b.[id], b.[name], 'IsAutoStatistics') = 0
>>>>>>
>>>>>>
>>>>>> SET NOCOUNT OFF
>>>>>>
>>>>>> SELECT *
>>>>>> FROM #tblFileGroupInfo WITH (NOLOCK)
>>>>>> ORDER BY TableOrViewName ASC, AttributeIndid ASC
>>>>>>
>>>>>> -- Cleanup
>>>>>>
>>>>>> IF OBJECT_ID('tempdb..#tblFileGroupInfo', 'U') IS NOT NULL
>>>>>> DROP TABLE #tblFileGroupInfo
>>>>>> GO
>>>>>> HTH,
>>>>>> AbbA
>>>>>>
>>>>>>
>>>>>> "rgn" wrote in message
>>>>>>


>>>>>>> On Friday, October 08, 2010 11:53 AM Sushil Patel wrote:

>>>>>>> I use this query and it gives me not only tables but in which filegroup all the related indexes too.
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> select case when indexes.type_desc in ('HEAP','CLUSTERED')
>>>>>>>
>>>>>>> then 'Table-' + indexes.type_desc
>>>>>>>
>>>>>>> else ' NC Index' end as indexType,
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> rtrim(cast(
>>>>>>>
>>>>>>> case when indexProperty(objects.object_id,indexes.name,'IsUnique') = 1
>>>>>>>
>>>>>>> then 'unique ' else '' end +
>>>>>>>
>>>>>>> case when isNull(objectProperty(object_id(schemas.name + '.'
>>>>>>>
>>>>>>> + indexes.name),'IsConstraint'),0) = 1
>>>>>>>
>>>>>>> then 'constraint ' else '' end +
>>>>>>>
>>>>>>> case when indexProperty(objects.object_id,indexes.name,'IsAutoStatistics') = 1
>>>>>>>
>>>>>>> then 'auto ' else '' end +
>>>>>>>
>>>>>>> case when indexProperty(objects.object_id,indexes.name,'IsStatistics') = 1
>>>>>>>
>>>>>>> then 'statistics ' else '' end +
>>>>>>>
>>>>>>> case when indexProperty(objects.object_id,indexes.name,'IsHypothetical') = 1
>>>>>>>
>>>>>>> then 'hypothetical ' else '' end
>>>>>>>
>>>>>>> as varchar(30))) as indexProperties,
>>>>>>>
>>>>>>> schemas.name + '.' + objects.name as tableName,
>>>>>>>
>>>>>>> coalesce(indexes.name,'') as indexName,
>>>>>>>
>>>>>>> filegroups.name as filegroup
>>>>>>>
>>>>>>> from sys.indexes as indexes
>>>>>>>
>>>>>>> join sys.objects as objects
>>>>>>>
>>>>>>> on indexes.object_id = objects.object_id
>>>>>>>
>>>>>>> join sys.schemas as schemas
>>>>>>>
>>>>>>> on objects.schema_id = schemas.schema_id
>>>>>>>
>>>>>>> join sys.filegroups as filegroups
>>>>>>>
>>>>>>> on indexes.data_space_id = filegroups.data_space_id
>>>>>>>
>>>>>>> where objectproperty(indexes.object_id,'IsMSShipped') = 0
>>>>>>>
>>>>>>> order by tableName, case when indexes.type_desc in ('HEAP','CLUSTERED') then 0 else 1 end


>>>>>>> Submitted via EggHeadCafe
>>>>>>> Microsoft ASP.NET For Beginners
>>>>>>> http://www.eggheadcafe.com/training-topic-area/ASP-NET/7/ASP.aspx
 >> Stay informed about: List of Tables in a FileGroup 

Donald Denney (donald.f.denney@questdiagnostics.com) writes:
> Actually, from what I was told by a Microsoft engineer a heap is an index.

A heap is not en index. However, if a table is a heap, there is an entry
in sys.indexes with index_id = 0, and if you run
sys.dm_db_index_physical_stats, you will see fragmentation data also for
the heap.



--
Erland Sommarskog, SQL Server MVP, esquel.TakeThisOut@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
 >> Stay informed about: List of Tables in a FileGroup