April 14, 2014 at 3:11 pm
Hello experts,
I want to capture times when a view (or any query, really, but especially views) has been written to use 'SELECT *'. The more I read, the more this sounds like the bad practice it is described to be, and I want to get a sense of how much of our code has this in it, to recommend fixing it.
My first guess is that I can just filter for SQLText of %SELECT%*%. However, I'm not sure if '*' is treated as a wildcard by SQL Trace itself and will somehow return the wrong trace data.
Could someone let me know either way?
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
April 14, 2014 at 3:53 pm
For existing objects you can search sys.sql_modules:
SELECT
o.object_id
,SchemaName = s.name
,ObjectName = o.name
,o.[type]
,m.[definition]
FROM sys.objects o
INNER JOIN sys.sql_modules m
ON o.object_id = m.object_id
INNER JOIN sys.schemas s
ON o.schema_id = s.schema_id
WHERE o.[type] IN ('P','V','FN','TF')
AND m.[definition] LIKE '%SELECT%*%'
;
--Vadim R.
April 15, 2014 at 8:15 am
This is great! Thanks for your 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 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply