Concerning Deprecated features (More than two-part column name)

Question

The defintion about the Deprecated features (More than two-part column name) is that

A query used a 3-part or 4-part name in the column list. Change the query to use the standard-compliant 2-part names. Occurs once per compilation.

May I ask for some example for this feature?

And since I have some code that use some function of sql server, for example:

Select xml.Item.value('[something]', 'varchar(20)') AS something from xml

Do the part xml.Item.value(...) regard as 3-part or 4-part name in the column list ??

And since i just look through the store procedures in my database manually to search for this deprecated feature, are there any other way (such as query) to find out those things in my database?

Thanks a lot

Answer 1

Here are the referencing options (this is not same for all database objects though):

1. 1-part naming: tablename (schema: default schema of the logged on user (ex: dbo), database: current)

2. 2-part naming: schemaname.tablename (database current)

3. 3-part naming: servername.schemaname.tablename

4. 4-part naming: linkedserver.servername.schemaname.tablename

For your example the part xml.Item.value(...) will not hold true for the 3-part naming culture - as value() is a method to retrieve a value from an xml type column or variable.

For your second part you can use this query to find out all the deprecated features being used and running in your database -

SELECT OBJECT_NAME,counter_name,instance_name AS 'Deprecated Feature',cntr_value AS 'Number of Times Used'
FROM sys.dm_os_performance_counters WHERE OBJECT_NAME LIKE '%:Deprecated%' AND cntr_value > 0
ORDER BY 'Number of Times Used' DESC

Footnote: Per Microsoft why the feature is being deprecated this is what I get - helping the optimizer, reducing clutter, and standardizing code.

Edit: Follow the link - it looks useful for identification of deprecated features used. Now if you want to find some specific set of text/keywords in your stored procedure then you can try any method listed here - link.