hw to know data file contents - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

hw to know data file contents

Author

Topic

Madhav
Starting Member

38 Posts

Posted - 2007-07-06 : 06:55:51

hi all ,
i want two data files data1.mdf and data2.mdf
in my database called 'data'.
i had some 100 tables, 100 views in my data base.
I want to know which table is in which datafile.

is there any procedure to find out this.
help me guys.

b.veenings
Yak Posting Veteran

96 Posts

Posted - 2007-07-06 : 07:21:37

right click on a table in EM and choose properties there is shown on wich filegroup the table is located.

i would also say select * from sysobjects but i can't get any info from that table but there is your objects overhead.

Need an SQL consultant?
check www.veeningsengineering.nl

Madhav
Starting Member

38 Posts

Posted - 2007-07-06 : 07:49:00

thanks,
but it is same as sp_help <table_name>.
is there any other way to see what tables are there in a file group.

quote:
Originally posted by b.veenings

right click on a table in EM and choose properties there is shown on wich filegroup the table is located.

i would also say select * from sysobjects but i can't get any info from that table but there is your objects overhead.

Need an SQL consultant?
check www.veeningsengineering.nl

kishore_pen
Starting Member

49 Posts

Posted - 2007-07-06 : 08:44:21

Hi Madhav,

Can you try the following way:

-- Code Starts

declare @ObjId int
, @RCnt int
, @Cnt int
, @FGName varchar (128)

-- Creating temp. table
declare @Result table
( ObjID int
, ObjName sysname
, Type char (2)
, FGName varchar (128)
)

begin
insert into @Result (ObjID, ObjName, Type)
select Id, Name, XType
from dbo.Sysobjects with (nolock)
where xtype in ('U', 'V') -- user tables & views

select @Cnt = 1
, @RCnt = 0

select @RCnt = count (*)
from @Result

while (@Cnt <= @RCnt)
begin

set rowcount @Cnt

select @ObjId = ObjID
from @Result
order by objID

set rowcount 0

select @FGName = s.groupname
from sysfilegroups s with (nolock), sysindexes i with (nolock)
where i.ID = @ObjID
and i.indid < 2
and i.groupid = s.groupid

update r
set FGName = @FGName
from @Result r
where ObjID = @ObjId

set @Cnt = @Cnt + 1
end

select *
from @Result
end
go

-- Code Ends
Kishore.P

b.veenings
Yak Posting Veteran

96 Posts

Posted - 2007-07-06 : 08:55:59

try this one:

select * from sysfilegroups
cross join sysobjects

Need an SQLDB consultant?
check www.veeningsengineering.nl

Madhav
Starting Member

38 Posts

Posted - 2007-07-06 : 09:02:30

guys...i got this query.

select so.name,s.groupname, f.filename
from sysfilegroups s, sysindexes i, sysobjects so, sysfiles f
where i.id = so.id
and s.groupid = f.groupid
and i.indid < 2
and i.groupid = s.groupid
and xtype = 'u'

i think its working fine
thanks guys
for ur effort.

quote:
Originally posted by kishore_pen

Hi Madhav,

Can you try the following way:

-- Code Starts

declare @ObjId int
, @RCnt int
, @Cnt int
, @FGName varchar (128)

-- Creating temp. table
declare @Result table
( ObjID int
, ObjName sysname
, Type char (2)
, FGName varchar (128)
)

begin
insert into @Result (ObjID, ObjName, Type)
select Id, Name, XType
from dbo.Sysobjects with (nolock)
where xtype in ('U', 'V') -- user tables & views

select @Cnt = 1
, @RCnt = 0

select @RCnt = count (*)
from @Result

while (@Cnt <= @RCnt)
begin

set rowcount @Cnt

select @ObjId = ObjID
from @Result
order by objID

set rowcount 0

select @FGName = s.groupname
from sysfilegroups s with (nolock), sysindexes i with (nolock)
where i.ID = @ObjID
and i.indid < 2
and i.groupid = s.groupid

update r
set FGName = @FGName
from @Result r
where ObjID = @ObjId

set @Cnt = @Cnt + 1
end

select *
from @Result
end
go

-- Code Ends
Kishore.P

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources