Points to Consider While Writing Queries - Microsoft SQL Server 2012 Performance Tuning - page 257

sysobjects so
ON
sc.object_id=so.id
WHERE
so.xtype in ('P','TR','V')
and
sc.definition LIKE '%*%'
--or to make more precise filter, you can go for
--sc.definition LIKE '%SELECT *%'
ORDER BY
Name

Sys.sql_modules contains the text that has been used to create objects such as functions,
procedures, triggers, views, and so on, but it won't contain the name of the object. Hence
JOIN is required with the Sys.sql_modules system view.

Xtype can filter out the type of object, for example, P represents stored procedure, TR
represents trigger, and V stands for view.

This query displayed above will not give you 100 percent accurate results; it may bring any
definition of an object that has used * , maybe for mathematical expression or comment.
There is one free tool provided by Microsoft that helps you in identifying not only this issue but
many other common mistakes, too. You can download this tool, Microsoft SQL Server 2012
Best Practices Analyzer, from the following URL:

http://www.microsoft.com/download/en/details.aspx?id=29302

How to do it...

Since we have now found how to find objects that have used SELECT * , we can manually
update those objects by replacing * with the appropriate column name. It's always a good
practice to give a column name while generating an object for the first time, so that we don't
need to correct it repeatedly. Let us see how much it is affect if we do not provide a proper
column name and/or filter.

1. Set two STATISTICS options to display the information about query execution:

SET STATISTICS IO ON
SET STATISTICS TIME ON

2. Execute a simple SELECT query on the Sales.SalesOrderDetail table with * to

see the Messages tab displaying information about the execution time and IO load:
SELECT
*
FROM
Sales.SalesOrderDetail
WHERE
SalesOrderID>50000 and OrderQty>1