Post reply

sp_Msforeachdb search for stored procs

  • September 11, 2008 at 9:30 am

    #71976

    I am finding myself (in a new job and trying to find my way around) looking for where this and that stored procedure and tables are located. Is there a script out there somewhere that someone can share with me to help me find stuff on this server???

    Thank you so much!! 😛

    Thank you!!,

    Angelindiego

  • September 12, 2008 at 8:41 am

    #871032

    Not much detail, but this might help.

    sp_msforeachdb 'select ''?'',name from ?.dbo.sysobjects where name like ''storeprocname'' and xtype = ''P'''

  • September 12, 2008 at 8:50 am

    #871043

    You can always just select out from sysobjects. It will display all the objects in the db you are currently in. The xtype indicates whether it is a systemtable (S), Usertable (U), Storeproc (P) or view (V). You can just select out where name =, like, in etc.

    Telammica

  • September 12, 2008 at 11:03 am

    #871168

    thank you for the suggestions. I have alot of db's and would rather do the foreachdb option, so that I don't have to query each db separately. TOO MANY!! LOL....

    I will give your option a try, and thank you again!! Have a great weekend!

    Thank you!!,

    Angelindiego

  • September 12, 2008 at 11:05 am

    #871170

    If you add a use ? into the one example you will be able to search all db's as well. So, example;

    sp_MSForeachdb @command1 = 'use ? select ''?'', name from sysobjects where name = ''procname'' and xtype = ''P'''

    Syntax may be off a bit since I am not in QA but....

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • September 12, 2008 at 11:14 am

    #871188

    thank you David!!

    Thank you!!,

    Angelindiego

  • September 12, 2008 at 11:18 am

    #871200

    Quite welcome. I am in a "new" environment as well which is huge and people are always coming up and talking about some table or index like I should know where it is. So, I have become pretty close with that query.... 🙂

    Have a great weekend!

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • September 12, 2008 at 1:01 pm

    #871284

    Hi David

    I dont think your suggestion will work though, since the 'use database' will not actually switch the database, you need to put 'go' after the use part and I dont think that will work in the way you used it.

    Telammica

Viewing 8 posts - 1 through 7 (of 7 total)

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