Search SP's for SELECT * - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Search SP's for SELECT *

Author

Topic

klo
Starting Member

7 Posts

Posted - 2007-10-31 : 16:18:23

Hi People

I've added a date field to a 100 odd tables. I now want to search through all the stored procedures (~2500) and find any occurence of "SELECT * FROM TABLE". I will also need to search for occurences of "INSERT INTO TABLE SELECT".

Has anyone got a fairly elaborate procedure to acheive this?

I've already put something primitive together thats based on doing a number of SELECTs on syscomments and looking for the various expressions in the text field - but I was looking for something a bit more sophisticated as I'm afraid I'll miss something.

I also have access to all the source files so I could also parse them if anyone has some handy utility. I've put together a Perl script to do this but again I'm afraid it might not be bullet-proof.

I'm reckoning this has to have been done before so any help would be appreciated.

Thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-31 : 16:24:46

select * from syscomments where text like '%select_*%'

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-10-31 : 16:26:02

Use the scripting functionality. Generate a script of all database procedures into a new query window as ALTER TO. Then just do find and replace as necessary from the resulting SQL file.

You could feasibly write a procedure that would first generate the script, then check, and then notify you, but you would still have to go into the procedure to handle the correction.

klo
Starting Member

7 Posts

Posted - 2007-10-31 : 16:38:42

Thanks for the replies.

Remember there is 2,500 stored procedures - if I run:

select * from syscomments where text like '%select_*%'

that will probably bring back most of them e.g.

if exists ( SELECT * FROM table) 
and
/* SELECT * FROM table 
and
--SELECT * FROM table 
etc.

... all of the above SELECTs are acceptable so therefore I don't want to bother with those sp's.

dataguru1971 - that is indeed how I intend to generate the sps - but I need to identify them first - as I don't want to be eye-balling 2,500 procedures.

Thanks for the efforts. Any more ideas?

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2007-10-31 : 16:44:15

Generate a script and use awk to find the lines you are interested in.
That way you can filter out Comments and if exists lines.

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-10-31 : 17:23:24

You may have to recompile the SP_'s with Select * as the compiled version would not have the new columns...similar to a view that would use Select *. As they say, select * is not the best way to go for most things, especially compiled code.

As far as the 2500 procedures, can you use sp_depends or a variation of that to determine which sp_'s in the database reference the table?

As long as the altered tables are in the same db as the procedures, the dependency should be accessible..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources