question

Hi All, I was watching a "How-To" Indexing video online and attempted to re-create the code for personal use. I re-created two separate queries (see below). The first worked without a flow and I was very pleased with the results. Now, the second query gave me a lot of trouble. For some reason I got the following error: **"Msg 9500, Level 16, State 1, Line 5 The data type 'system' used in the VALUE method is invalid."** I work with SQL Server 2008. This issue might be related to permissions, but I am not quite sure. Any help would be appreciated. Also, if someone can improve or has a better way of retrieving the same data, I would love to hear your suggestions. Thank You! FYI - I am VERY new to SQL, meaning I would like to know the "standard" way of doing this. Want to develop good habits early! /*QUERY 1*/ /*******************Generate Index performance Status*******************/ SELECT usi.database_id, usi.object_id, usi.index_id, i.name, usi.user_seeks, usi.user_scans, usi.user_lookups, usi.user_updates FROM sys.dm_db_index_usage_stats usi INNER JOIN sys.indexes i ON usi.object_id = i.object_id AND usi.index_id = i.index_id WHERE usi.database_id = DB_ID() AND usi.object_id = OBJECT_ID('TableName') /*QUERY 2*/ ;WITH XMLNAMESPACES(DEFAULT N' http://schemas.microsoft.com/sqlserver/2004/07/showplan') SELECT o.value('(IndexScan/Object/@Schema)[1]', 'system') AS SchemaName ,o.value('(IndexScan/Object/@Table)[1]', 'system') AS TableName ,o.value('(IndexScan/Object/@Index)[1]', 'system') AS IndexName ,REPLACE(o.query( 'for $column in IndexScan/DefineValues/DefineValue/ColumnReference Return string($column/@Column)' ).value('.', 'varchar(max)'),' ', ', ') AS KeyLookupColumns, o.value(N'@PhysicalOp', N'varchar(50)') AS hysical_operator, o.value(N'@LogicalOp', N'varchar(50)') AS logical_operator, plan_handle, qp.query_plan FROM Sys.dm_exec_cached_plans cp CROSS APPLY Sys.dm_exec_plan_attributes(cp.plan_handle) as pa CROSS APPLY Sys.dm_exec_query_plan(cp.plan_handle) as qp CROSS APPLY qp.query_plan.nodes('//StmtSimple') as s(stmt) CROSS APPLY qp.query_plan.nodes(N'//RelOp') as r(o) WHERE query_plan.exist('.//relOp/IndexScan[@Lookup="1"]')=1 AND o.exist('./IndexScan[@Lookup="1"]')=1 AND pa.attribute = 'dbid' AND pa.value = DB_ID() GO