SQL Server Performance Forum – Threads Archive

get filegroup for a text image column?

Hi, I’m trying to find out the name of the file group that a text column is currently on in SQL 2005. In SQL Server 2000 if you create the following table: CREATE TABLE [dbo].[test_with_text](
[col1] [int],
[col2] [text] COLLATE Latin1_General_CI_AS NULL
) TEXTIMAGE_ON [MyFileGroup]
In SQL 2000 TEXT columns have an entry in the sysindexes table with an indid of 255. This doesn’t happen in SQL 2005. If I then want to find out the name of the filegroup that col2 is placed on I can run: SELECT groupname FROM sysfilegroups f
JOIN sysindexes i on i.groupid = f.groupid
WHERE indid = 255 AND object_name(id) = ‘test_with_text’
Does anyone know how to get at the filegroup that text columns in a specific table are on – in SQL 2005? I can’t see anything obvious in syscolumns and sysindexes no longer seems to hold text column data. Thanks,
Karl Grambow www.sqldbcontrol.com

Not sure if this helps you. But the TEXT data type is deprecated and will be replaced in a future version. It is replaced by VARCHAR(MAX) and VARBINARY(MAX). Probably you find your answer when searching for these data types. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

Thanks Frank, I knew about the deprecation. I managed to find a solution that looks at the sys.allocation_units table. It probably takes into account the future deprecation because in this table there are only three possible allocation_unit types (LOB_DATA, IN_ROW_DATA and ROW_OVERFLOW_DATA). For those that are interested: select s.name
from sys.allocation_units au
join sys.partitions p ON au.container_id = p.partition_id
join sys.data_spaces s on s.data_space_id = au.data_space_id
where p.object_id = object_id(‘test_with_text’)
and au.type_desc = ‘LOB_DATA’
Regards,

quote:Originally posted by FrankKalis Not sure if this helps you. But the TEXT data type is deprecated and will be replaced in a future version. It is replaced by VARCHAR(MAX) and VARBINARY(MAX). Probably you find your answer when searching for these data types. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

Karl Grambow www.sqldbcontrol.com

Good to know! Bookmarked! Another thing to watch out for when finally moving to 2005. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>

]]>