Server-side trace to find SELECT *

Question

Server-side trace to find SELECT *

webrunner

SSC-Dedicated

Points: 31059
More actions
March 27, 2014 at 1:59 pm

#396490

Hello experts,
Is there any efficient way to set up a server-side trace to find queries that use 'SELECT *'?
The reason I ask is that I need to change a view, and it's in a database set to SQL 2000 compatibility level. We have seen issues where queries that use 'SELECT *' from that view break if we change it, for the understandable reason that '*' has now changed if we add, remove, or modify a column in the underlying tables.
I can search static code via text searches of the codebases that I know of, but I'm curious if I could improve the testing by finding any other queries that come to the server while I am tracing it.
Thanks for any help!
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323482 More actions · Answer 1

since you are taking about VIEWS breaking, that really has nothing to do with a trace, right? it really has to do with source code of views/procs/etc

i typically refresh every view with sp_refreshview after a script update as a matter of maintenance.

i think you could easily find any proc or view with a simple query right now, right?

im stripping out the four common whitespaces and looking for 'SELECT*' for example below

SELECT object_name(object_id),definition from sys.sql_modules

where

REPLACE(

REPLACE(REPLACE(definition,' ','')

,CHAR(9),'')

,CHAR(13),'')

,CHAR(13),'') LIKE '%SELECT*%'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 2

Setup a server side trace with tuning template and apply column filters on DB Name and App ID. Adding filters will reduce the amount of data collected. If you want to collect all user's queries, just exclude SQL Server service and Admin Ids ..

--

SQLBuddy

SpeedySQL Ten Centuries Points: 1019 More actions · Answer 3

You can set up a server-side trace for the event SQL:StmtStarting and filter on the textdata column to look for something like "%SELECT * FROM %schemaName.viewToBeAltered%". Depending upon how many statements in this format are run against your server, it might or might not be very efficient. Keep a close eye on it and put a small size limit on the trace.

SpeedySQL

Lowell SSC Guru Points: 323482 More actions · Answer 4

--

--fix any views that happen to have changes to their underlying tables they query from

declare c1 cursor for

select name from sys.views

open c1

fetch next from c1 into @viewname

While @@fetch_status <> -1

begin

select @isql ='EXEC sp_refreshview ' + quotename(@viewname) + ';'

print @isql

exec(@isql)

fetch next from c1 into @viewname

end

close c1

deallocate c1

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

webrunner SSC-Dedicated Points: 31059 More actions · Answer 5

Thanks, everyone, for your replies!!

Lowell, I didn't know about sp_refreshview - that sounds like a huge help.

Thanks again,

webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html