filter sqldatasource: filegroup

Showing posts with label filegroup. Show all posts

Friday, March 23, 2012

How to find what Tables and Indexes are on what filegroup

Hello,
Is there a query that can be written to determine what Tables and indexes
are on a particular file group?
Thanks
Anubis.sp_help 'YourTable' will return the filegroup in one of the
resultsets that is returned. There are also some
undocumented ways such as:
sp_objectfilegroup @.objid
or querying the system tables...something like:
SELECT so.name, sfg.groupname
FROM sysobjects so
INNER JOIN sysindexes si
ON so.id=si.id
INNER JOIN sysfilegroups sfg
ON si.groupid=sfg.groupid
WHERE si.indid < 2
AND so.type = 'U'
-Sue
On Tue, 14 Jun 2005 12:17:47 +1000, "Anubis"
<anubis@.iwwd.com> wrote:
>Hello,
>Is there a query that can be written to determine what Tables and indexes
>are on a particular file group?
>Thanks
>Anubis.
>|||This is a multi-part message in MIME format.
--010807000500020704010007
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Depends on what info you want to find out. But it will be some
variation on this:
select object_name(i.[id]) as tablename, i.*
from dbo.sysindexes as i
inner join dbo.sysfilegroups as g on g.groupid = i.groupid
where g.groupname = 'PRIMARY'
If it's just names you're after then the column list in the select
statement will be something like "select object_name(i.[id]) as
tablename, i.[name] as indexname ..." but there's heaps of other info
you can find out from dbo.sysindexes, like the index type (clustered,
nonclustered, heap, LOB data - all from the indid), etc.
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Anubis wrote:
>Hello,
>Is there a query that can be written to determine what Tables and indexes
>are on a particular file group?
>Thanks
>Anubis.
>
>
--010807000500020704010007
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Depends on what info you want to find out. But it will be some
variation on this: 
</tt>
<blockquote><tt>select object_name(i.[id]) as tablename, i.*</tt> 
<tt>from dbo.sysindexes as i</tt> 
<tt> inner join dbo.sysfilegroups as g on g.groupid = i.groupid</tt> 
<tt>where g.groupname = 'PRIMARY' 
</tt></blockquote>
<tt>If it's just names you're after then the column list in the select
statement will be something like "select object_name(i.[id]) as
tablename, i.[name] as indexname ..." but there's heaps of other info
you can find out from dbo.sysindexes, like the index type (clustered,
nonclustered, heap, LOB data - all from the indid), etc.</tt> 
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson | database administrator
| mallesons stephen jaques 
T +61 (2) 9296 3668 | F +61
(2) 9296 3885 | M +61
(408) 675 907 
E
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> | W <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a> 
</div>
 
 
Anubis wrote:
<blockquote cite="miduyBFlhIcFHA.1148@.tk2msftngp13.phx.gbl" type="cite">
<pre wrap="">Hello,
Is there a query that can be written to determine what Tables and indexes
are on a particular file group?
Thanks
Anubis.
</pre>
</blockquote>
</body>
</html>
--010807000500020704010007--|||I wrote a script that does exactly what you ask (and more)
http://education.sqlfarms.com/ShowPost.aspx?PostID=48
for your convenient download. Enjoy.
The script lists all the tables and indexes filgroup, as well as provides
other information.
--
Omri Bahat
SQL Farms Solutions
www.sqlfarms.com

How to find what Tables and Indexes are on what filegroup

How to find what is in a file??

All,
sql 2000, server 2000
I have a filegroup that I want to get rid of, I delted all
the indexes using that filegroup. But there is still .38
mb being used and it won't let me drop it. Any ideaa, how
do I see what is in that file, so I can drop the filegroup?
Thanks in advance,
Gary AbbottIf U perform dbcc checkfilegroup (filegroupname) this checks for allocation errors in the filegroups in the process it also displays the objects in the file group. . . .
ensure that you perform this in a test environment or during your scheduled maintenance jobs
PS. This might be , i think it is an undocumented dbc|||<anonymous@.discussions.microsoft.com> wrote in message
news:2e5b01c3fcfd$d0d373f0$a501280a@.phx.gbl...
> I have a filegroup that I want to get rid of, I delted all
> the indexes using that filegroup. But there is still .38
> mb being used and it won't let me drop it. Any ideaa, how
> do I see what is in that file, so I can drop the filegroup?
Try the following query:
select object_name(id) as tablename,name as
indexname,FILEGROUP_NAME(groupid) as filegroupname
from sysindexes
where groupid>1|||Your query worked great, but the filegroup I am trying to
delete was not in there. I'm sure it is empty, but it
won't let me delete it. I had already deleted several
other filegroups, so I am sure I am doing it right. It's
like there is a remnant of some index, even though there
are none. It is 0.38 MB, which is larger than the other
datafiles I had cleaned out, then deleted. Any other ideas?
Gary Abbott
>--Original Message--
><anonymous@.discussions.microsoft.com> wrote in message
>news:2e5b01c3fcfd$d0d373f0$a501280a@.phx.gbl...
>> I have a filegroup that I want to get rid of, I delted
all
>> the indexes using that filegroup. But there is still .38
>> mb being used and it won't let me drop it. Any ideaa,
how
>> do I see what is in that file, so I can drop the
filegroup?
>Try the following query:
>select object_name(id) as tablename,name as
>indexname,FILEGROUP_NAME(groupid) as filegroupname
>from sysindexes
>where groupid>1
>
>.
>|||I found the answer I was looking for, DBCC Shrinkfile,
with the emptyfile option.
>--Original Message--
>Your query worked great, but the filegroup I am trying to
>delete was not in there. I'm sure it is empty, but it
>won't let me delete it. I had already deleted several
>other filegroups, so I am sure I am doing it right. It's
>like there is a remnant of some index, even though there
>are none. It is 0.38 MB, which is larger than the other
>datafiles I had cleaned out, then deleted. Any other
ideas?
>Gary Abbott
>
>>--Original Message--
>><anonymous@.discussions.microsoft.com> wrote in message
>>news:2e5b01c3fcfd$d0d373f0$a501280a@.phx.gbl...
>> I have a filegroup that I want to get rid of, I delted
>all
>> the indexes using that filegroup. But there is
still .38
>> mb being used and it won't let me drop it. Any ideaa,
>how
>> do I see what is in that file, so I can drop the
>filegroup?
>>Try the following query:
>>select object_name(id) as tablename,name as
>>indexname,FILEGROUP_NAME(groupid) as filegroupname
>>from sysindexes
>>where groupid>1
>>
>>.
>.
>

How to find what is in a file??

All,
sql 2000, server 2000
I have a filegroup that I want to get rid of, I delted all
the indexes using that filegroup. But there is still .38
mb being used and it won't let me drop it. Any ideaa, how
do I see what is in that file, so I can drop the filegroup?
Thanks in advance,
Gary AbbottIf U perform dbcc checkfilegroup (filegroupname) this checks for allocation
errors in the filegroups in the process it also displays the objects in the
file group. . . .
ensure that you perform this in a test environment or during your scheduled
maintenance jobs.
PS. This might be , i think it is an undocumented dbcc|||<anonymous@.discussions.microsoft.com> wrote in message
news:2e5b01c3fcfd$d0d373f0$a501280a@.phx.gbl...
> I have a filegroup that I want to get rid of, I delted all
> the indexes using that filegroup. But there is still .38
> mb being used and it won't let me drop it. Any ideaa, how
> do I see what is in that file, so I can drop the filegroup?
Try the following query:
select object_name(id) as tablename,name as
indexname,FILEGROUP_NAME(groupid) as filegroupname
from sysindexes
where groupid>1|||Your query worked great, but the filegroup I am trying to
delete was not in there. I'm sure it is empty, but it
won't let me delete it. I had already deleted several
other filegroups, so I am sure I am doing it right. It's
like there is a remnant of some index, even though there
are none. It is 0.38 MB, which is larger than the other
datafiles I had cleaned out, then deleted. Any other ideas?
Gary Abbott

>--Original Message--
><anonymous@.discussions.microsoft.com> wrote in message
>news:2e5b01c3fcfd$d0d373f0$a501280a@.phx.gbl...
all
how
filegroup?
>Try the following query:
>select object_name(id) as tablename,name as
>indexname,FILEGROUP_NAME(groupid) as filegroupname
>from sysindexes
>where groupid>1
>
>.
>|||I found the answer I was looking for, DBCC Shrinkfile,
with the emptyfile option.

>--Original Message--
>Your query worked great, but the filegroup I am trying to
>delete was not in there. I'm sure it is empty, but it
>won't let me delete it. I had already deleted several
>other filegroups, so I am sure I am doing it right. It's
>like there is a remnant of some index, even though there
>are none. It is 0.38 MB, which is larger than the other
>datafiles I had cleaned out, then deleted. Any other
ideas?
>Gary Abbott
>
>all
still .38
>how
>filegroup?
>.
>

Wednesday, March 7, 2012

How to find filegroups for a given Table and Table's indexes

I am using SQL Server 2005 Developer Edition.

I want a list of the following things from the database: -

Table Name , FileGroup Table resides on

Table Name, Index Name, FileGroup index resides on

To put it simply, consider the following example:-

Lets say I have a table XYZ in my database created on Filegroup F1. It has a PK PK1 nonclustered index on Filegroup F2.

List1

XYZ F1

List2

XYZ PK1 F2

Please do not tell me of sp_help <table> option

Regards

Imtiaz

For the table XYZ

select OBJECT_NAME(i.object_id) [Table_Name], ds.name [Filegroup_Name] from sys.indexes i join sys.filegroups ds on (ds.data_space_id=i.data_space_id) where object_name(i.object_id) = 'XYZ' and i.index_id=0

--This will work when the table does not have any clustered index on it.

For the non clustered index,

select OBJECT_NAME(i.object_id) [Table_Name], i.name [Index_Name], ds.name [Filegroup_name] from sys.indexes i join sys.filegroups ds on (ds.data_space_id=i.data_space_id) where object_name(i.object_id) = 'XYZ' and i.name='PK1'

For more such catalog view queries

http://msdn2.microsoft.com/en-us/library/ms345522.aspx

Friday, March 23, 2012

How to find what Tables and Indexes are on what filegroup

How to find what Tables and Indexes are on what filegroup

How to find what Tables and Indexes are on what filegroup

How to find what is in a file??

How to find what is in a file??

Wednesday, March 7, 2012

How to find filegroups for a given Table and Table's indexes

filter sqldatasource

Blog Archive

About Me