December 17, 2008 at 11:04 am
I have the following query for retrieving filegroup information on database indexes, but I would like to filter out system tables, such as sysowners, sysschobjs etc.
Is that possible?
SELECT
object_name(I.[object_id]) AS tableName
,I.[name] AS indexName
,F.[name] AS fileGroupName
FROM sys.indexes I JOIN sys.filegroups F
ON I.data_space_id = F.data_space_id
WHERE I.type = 2;
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
December 17, 2008 at 12:21 pm
join to the sysobjects table, sytem tables have an XTYPE = 'S'
---------------------------------------------------------------------
December 17, 2008 at 12:49 pm
george sibbald (12/17/2008)
join to the sysobjects table, sytem tables have an XTYPE = 'S'
Thank you sir!
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
December 17, 2008 at 12:54 pm
my pleasure.
---------------------------------------------------------------------
December 17, 2008 at 12:59 pm
Since you're on 2005, join to sys.tables. Only user tables appear in that view.
sysobjects is deprecated in 2005 and above and will be removed in a future version.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 17, 2008 at 1:32 pm
GilaMonster (12/17/2008)
Since you're on 2005, join to sys.tables. Only user tables appear in that view.sysobjects is deprecated in 2005 and above and will be removed in a future version.
Good point, thanks!
I will use George's suggestion for our sql 2000 systems.
Thank you both for your timely responses.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
December 17, 2008 at 2:41 pm
Complete query:
SELECT
object_name(I.[object_id]) AS tableName
, I.[name] AS indexName
, F.[name] AS fileGroupName
, I.type
FROM sys.indexes I JOIN sys.filegroups F
ON I.data_space_id = F.data_space_id
JOIN sys.tables T
ON I.object_id = T.object_id
WHERE I.type in (1,2,3)
MJ
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply