January 11, 2005 at 9:21 pm
Please let me know which procedure or dictionary view give me name of tables stored on a datafile ?
January 12, 2005 at 1:37 am
You might have a look at INFORMATION_SCHEMA.TABLES in BOL.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
January 12, 2005 at 9:42 am
Not sure about heap tables, but if you have a clustered index you can check which datafile these are stored on, which is the same as your table.
January 12, 2005 at 12:47 pm
Oh, if it is that what Steve mentioned, I think this will help:
SELECT
sfilegrp.groupid AS GroupID
, SUBSTRING(sfilegrp.groupname,1,30) AS FilegroupName
, FILENAME
, SUBSTRING(sobj.name,1,50) AS ObjectName
FROM
SYSOBJECTS sobj
INNER JOIN
SYSINDEXES si
ON
sobj.id = si.id
INNER JOIN
SYSFILEGROUPS sfilegrp
ON
si.groupid = sfilegrp.groupid
INNER JOIN
SYSFILES sfiles
ON
sfilegrp.groupid = sfiles.groupid
WHERE
si.indid = 0
AND
sobj.xtype = 'U'
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
January 12, 2005 at 8:19 pm
Many thanks for the information.
Let me explain my issue, I've database with two datafiles, I moved data of one datafile to primary datafile with the Enterprise Manager as Selected a databbase -> All Tasks -> Shrink database -> Files -> and selected the shrink option 'Empty the file'. With the script I can still see some tables resides on the datafile where it supposed to empty. I'd appreciate if someone shed a light on it.
January 13, 2005 at 8:14 am
I believe you will have to drop and re-create the tables. Enterprise Manager has on option to change the filegroup but if you run a trace in the background, it does re-create a temp table and rename it.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply