How to make two filegroups to one filegroup

Hello,
I have to filegroups like
database_data1 stored on d:
database_data2 stored on e:
How can I delete the database_data2 and tell SQL 2005 to copy all
data to database_data1?
Thanks for any help in advance!
A. KlemtOne way...
Assuming you have clustered indexes on all you tables is to drop and
recreate the index on the database_data1 filegroup this will also move
all the data over to database_data1
http://sqlservercode.blogspot.com/|||Hi
It is pretty much described in the BOL under "Moving database file"
"Andreas Klemt" <aklemt68@.hotmail.com> wrote in message
news:Oxkia5$JGHA.3200@.tk2msftngp13.phx.gbl...
> Hello,
> I have to filegroups like
> database_data1 stored on d:
> database_data2 stored on e:
> How can I delete the database_data2 and tell SQL 2005 to copy all
> data to database_data1?
> Thanks for any help in advance!
> A. Klemt
>

how to know which filegroup a table is using with a SQL query

there are several filegroups
I want to know which filegroup a table is using with a SQL query
(i think there may be a SYSTEM TABLE I can query to know it)
Benny
select
object_name(i.id) as table_name,
groupname as [filegroup]
from sysfilegroups s, sysindexes i
where i.id in (object_id('tablename'))
and i.indid < 2
and i.groupid = s.groupid
"Benny" <wuyuebing@.gmail.com> wrote in message
news:1178688427.945823.281780@.e65g2000hsc.googlegr oups.com...
> there are several filegroups
> I want to know which filegroup a table is using with a SQL query
> (i think there may be a SYSTEM TABLE I can query to know it)
>
|||On May 9, 1:54 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:[vbcol=seagreen]
> Benny
> select
> object_name(i.id) as table_name,
> groupname as [filegroup]
> from sysfilegroups s, sysindexes i
> where i.id in (object_id('tablename'))
> and i.indid < 2
> and i.groupid = s.groupid
> "Benny" <wuyueb...@.gmail.com> wrote in message
> news:1178688427.945823.281780@.e65g2000hsc.googlegr oups.com...
>
from your query, I found it is based on index's filegroup,
so how about if some table do not have index?
and how about .indid >= 2 ?

how to know which filegroup a table is using with a SQL query

there are several filegroups
I want to know which filegroup a table is using with a SQL query
(i think there may be a SYSTEM TABLE I can query to know it)Benny
select
object_name(i.id) as table_name,
groupname as [filegroup]
from sysfilegroups s, sysindexes i
where i.id in (object_id('tablename'))
and i.indid < 2
and i.groupid = s.groupid
"Benny" <wuyuebing@.gmail.com> wrote in message
news:1178688427.945823.281780@.e65g2000hsc.googlegroups.com...
> there are several filegroups
> I want to know which filegroup a table is using with a SQL query
> (i think there may be a SYSTEM TABLE I can query to know it)
>|||On May 9, 1:54 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Benny
> select
> object_name(i.id) as table_name,
> groupname as [filegroup]
> from sysfilegroups s, sysindexes i
> where i.id in (object_id('tablename'))
> and i.indid < 2
> and i.groupid = s.groupid
> "Benny" <wuyueb...@.gmail.com> wrote in message
> news:1178688427.945823.281780@.e65g2000hsc.googlegroups.com...
> > there are several filegroups
> > I want to know whichfilegroupa table is using with a SQL query
> > (i think there may be a SYSTEM TABLE I can query to know it)
from your query, I found it is based on index's filegroup,
so how about if some table do not have index?
and how about .indid >= 2 ?

how to know which filegroup a table is using with a SQL query

there are several filegroups
I want to know which filegroup a table is using with a SQL query
(i think there may be a SYSTEM TABLE I can query to know it)Benny
select
object_name(i.id) as table_name,
groupname as [filegroup]
from sysfilegroups s, sysindexes i
where i.id in (object_id('tablename'))
and i.indid < 2
and i.groupid = s.groupid
"Benny" <wuyuebing@.gmail.com> wrote in message
news:1178688427.945823.281780@.e65g2000hsc.googlegroups.com...
> there are several filegroups
> I want to know which filegroup a table is using with a SQL query
> (i think there may be a SYSTEM TABLE I can query to know it)
>|||On May 9, 1:54 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:[vbcol=seagreen]
> Benny
> select
> object_name(i.id) as table_name,
> groupname as [filegroup]
> from sysfilegroups s, sysindexes i
> where i.id in (object_id('tablename'))
> and i.indid < 2
> and i.groupid = s.groupid
> "Benny" <wuyueb...@.gmail.com> wrote in message
> news:1178688427.945823.281780@.e65g2000hsc.googlegroups.com...
>
>
from your query, I found it is based on index's filegroup,
so how about if some table do not have index?
and how about .indid >= 2 ?

How to know allocation place for each object?

Hi all of you,

My current dutie is try to obtain for each table their filegroup.

I'm seeing sysobjects table but I can't see nothing related to do with

Thanks a lot for your time,

Ok, if you run this query you obtain such name but it is not enought for my goal:

sp_help <table>

|||

Not sure if this is something you're after, this will list each object with their associated filegroup, you can filter sysobjects for tables only, not pretty unfortunately but works:

SET NOCOUNT ON

DECLARE @.sqltxt varchar(4000);

DECLARE @.tblName varchar(4000);

DECLARE GetFG_Cursor CURSOR FOR

Select 'sp_objectfilegroup ' + CAST(id AS VARCHAR(4000)), name from sysobjects;

OPEN GetFG_Cursor;

FETCH NEXT FROM GetFG_Cursor INTO @.sqltxt,@.tblName;

WHILE @.@.FETCH_STATUS = 0

BEGIN

PRINT 'Table Name: ' + @.tblName;

EXECUTE(@.sqltxt);

FETCH NEXT FROM GetFG_Cursor INTO @.sqltxt,@.tblName;

END

CLOSE GetFG_Cursor;

DEALLOCATE GetFG_Cursor;

|||

hi xrayb,

That's a good approximation. Thanks indeed.

It'll be helpful.