Search "select *" in database - 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 "select *" in database

Author

Topic

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-08-27 : 02:10:53

Hi All,

We always say, we should not use select * from table.

I want to search all stored procedures in database where "select *" is used. How can i do it.

Thanks

mk_garg

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-08-27 : 02:34:19

SELECT * from syscomments where
text like '%select * from%'
Should do the trick....

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-08-27 : 02:40:50

Nope!
It is giving me store procedure which is not using "select * from"

Thanks

mk_garg

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-08-27 : 02:48:20

hmm - strange that.
I've had some problems getting SP text from syscomments.
You might be able to do this using VB and SQLDMO objects

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-08-27 : 02:55:32

How using VB?
I never used SQLDMO. How i can use it?
Thanks

mk_garg

mr_mist
Grunnio

1870 Posts

Posted - 2004-08-27 : 05:07:03

If you download the microsoft best practice analyzer it will identify this for you (amongst other things.)

-------
Moo. :)

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-08-27 : 07:30:02

quote:

Nope!
It is giving me store procedure which is not using "select * from"

Strange, I have never had this issue !?

However you could also do the following:
1. Script all the procs to a file
2. Open file in texteditor
3. Search the text for "select *"

/rockmoose

X002548
Not Just a Number

15586 Posts

Posted - 2004-08-27 : 12:58:44

You sure?

Put it in grid mode then execute it.....

Brett

8-)

EDIT: Then theres always

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=32305&SearchTerms=search,stored,procedures

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-27 : 13:03:54

http://vyaskn.tripod.com/code/search_stored_procedure_code.txt

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-27 : 13:04:53

And don't forget that SELECT * should be used in EXISTS or NOT EXISTS. But that's the only exception.

Tara