| Author |
Topic |
|
rtutus
Aged Yak Warrior
522 Posts |
 Posted - 2006-12-22 : 15:56:54
|
| Here is my requirement:The script needs to enumerate the the databases in a givin instance of sql server. Then it needs to output the account with permissions on that server. Finally it then needs to search stored procedured for reference to specific file and account names loaded from a csv file. The results should be output to a text file or displayed in an exportable window format.What I don t understand:What do they mean by: account with permissions on that server, which account re they talking aboutand what do they mean by the SP references a specific file and account name? how can an SP reference a file or account name. i have never seen that?Thanks for help. |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-12-22 : 16:00:33
|
Perhaps you should be asking "them" that rather than asking us to guess  |
 |
|
|
rtutus
Aged Yak Warrior
522 Posts |
Posted - 2006-12-22 : 16:23:42
|
| Here is the requirement exactly:The script needs to enumerate the the databases in a givin instance of sql server. Then it needs to output the account with permissions on that server. Finally it then needs to search stored procedured for reference to specific file and account names loaded from a csv file. The results should be output to a text file or displayed in an exportable window format.Does that make sense?Thanks |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-12-22 : 17:53:56
|
| Does that make sense?Not really, no. |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2006-12-22 : 18:26:36
|
all you did was copy/paste from your first post...
www.elsasoft.org |
|
|
|
rtutus
Aged Yak Warrior
522 Posts |
Posted - 2006-12-22 : 20:29:40
|
| that s funny. so sorry i thought i had mentioned a summary the first timeAny way: what I need first just so i can get some guidance to start with:Is there a way in sql to do this:To get all the stored procedures of a given database, then either- (1) put all the stored procedures contents (all the scripts inside the SPs) in a single file or- (2) Put all the scripts that constitute all the SPs in a single big string. Then search that file (if case 1) or that big string (in case 2) and check if it includes a given input pattern (for example check if includes: mySearchedKeyword)Thanks a lot |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2006-12-22 : 21:46:45
|
you can use EM (2000) or Generate Scripts Wizard (2005) to generates scripts. Then it would be a simple matter to search the scripts using a tool such as grep or your favorite code editor.Alternatively, if you like the cmd line and hate the gui like me, you can use a free tool I wrote to generate scripts: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=73884
www.elsasoft.org |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
|
|
rtutus
Aged Yak Warrior
522 Posts |
Posted - 2006-12-22 : 22:07:27
|
| what is SSMS? and can your ScriptDb tool check only SPs instead of all objects?I wil also check the other 2 solutions:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=32319http://vyaskn.tripod.com/code/search_stored_procedure_code.txtThanks a lot |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2006-12-22 : 23:04:46
|
SSMS is sql server management studio is the 2005 replacement for EM and QA.scriptdb scripts all objects. however the source is available so you can modify it to do only sprocs if you want.
www.elsasoft.org |
|
|
|
rtutus
Aged Yak Warrior
522 Posts |
Posted - 2006-12-23 : 12:44:09
|
| thanks a lot amigo, that will help me a lot. |
|
|
|
rtutus
Aged Yak Warrior
522 Posts |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-01-02 : 11:19:37
|
it would be trivial for you to modify scriptdb.exe to generate scripts for all objects in all databases, since the source code is available.
www.elsasoft.org |
|
|
|
rtutus
Aged Yak Warrior
522 Posts |
Posted - 2007-01-02 : 21:03:44
|
| For now I used the script here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=32319 to search for text in combination with MSforeachDB function in order to go through all the databases.If this doesn t provide good results then I ll try using scriptDB.exe in the future.Thanks for your all your advices and happy new year:) |
|
|
|
|
need for a script that searches through sql instan