Tables and Filegoups - 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

Transact-SQL (2000)

Tables and Filegoups

Author

Topic

daidaluus
Yak Posting Veteran

73 Posts

Posted - 2003-03-06 : 10:16:43

is there a way to find out a specified table on which figroup located?
(not using generate script from EM or QA)

nr
SQLTeam MVY

12543 Posts

Posted - 2003-03-06 : 11:34:08

from sp_objectfilegroup

select Data_located_on_filegroup = s.groupname
from sysfilegroups s, sysindexes i
where i.id = @objid
and i.indid < 2
and i.groupid = s.groupid

use object_id('mytbl') to get the object id.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

daidaluus
Yak Posting Veteran

73 Posts

Posted - 2003-03-06 : 11:56:36

Thanks. it worked fine. but what is sp_objectfilegroup? is it an undocumented sp?

nr
SQLTeam MVY

12543 Posts

Posted - 2003-03-06 : 12:35:27

It's called by sp_help.

If you use sp_help on a table it prints out the filegroup, looking at the sp it calls sp_objectfilegroup to do it.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X002548
Not Just a Number

15586 Posts

Posted - 2003-03-06 : 12:44:11

You can unciver a lot of SQL Server "Secrets" by looking at the sprocs that come with the product.

Try:

master..sp_helptext sp_help

Also, I never understood why the system catalog is built in such a manner that "rules" have to be used to interprete them, and that INFORMATION_SCHEMA is required to make out life easier.

BTW, where are these views stored? I can't seem to locate them.

Brett

8-)

nr
SQLTeam MVY

12543 Posts

Posted - 2003-03-06 : 13:07:06

The information_schema views aren't required. They are there so that code will work across releases and

"The information schema views included in SQL Server conform to the SQL-92 Standard definition for the INFORMATION_SCHEMA."

They are a recent addition.
Wish there was an abbreviation like INF.

They are stored as views in master with INFORMATION_SCHEMA as owner.
This seems to have a fixed uid of 3

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources