| How to find in what stored procedures or functions the necessary string is found? |
In order not to open each separately.
| alm2, |
[ name ] SELECT [ name ] [ sys ].[ objects ] FROM [ sys ]. [ Objects ] [ type ] = 'P' WHERE [ type ] = 'P' OBJECT_DEFINITION ([ object_id ]) LIKE N '%НУЖНАЯ СТРОКА%' ; AND OBJECT_DEFINITION ([ object_id ]) LIKE N '% NEED LINE%' ;
and there it will be seen :)
|select * from syscomments where text like '% something%'|
| This is not that! |
I need not a list of procedures for a given pattern, but a list of procedures, within which there is a line in my text that I need!
The first answer is already written. Have you even started the script?
The search string may be stored in parts on adjacent rows.
and for functions add 'FN', 'IF', 'TF'
|just the direction to search, no more. good or bad this idea can determine only one who builds the final query|
Well, about the triggers of the vehicle did not ask :)
| alm2, |
select BODY.ID, BODY. [TEXT], OBJ.NAME
from syscomments BODY
INNER JOIN sys.objects OBJ ON BODY.ID = OBJ.object_id
BODY. [TEXT] like '% what needs to be found in the body of the object%'
If all the recommended tips did not suit you,
Go to the procedures section, press F7, select all CTRL + A procedures, right-click the Script Stored Procedure as -> Create to ....
In a minute, a script of all procedures will be displayed in a separate window, where you will find the text you need using Ctrl + F.
So in one place, you can get what you want :)
If all the recommended tips did not suit you, we set up a free tool and use it, for example:
|I will raise Temko, and if I do not have rights to all the procedures, will the script also search for a string in them and give out a name?|
| Sintetik, |
Have you been banned in Google or is religion not allowed to use search?
I did not ask about the objects of the system dictionary, it is logical that if there are no rights to them, then the select will not work
I asked if I do not have rights to the XXX user procedure and I don’t even see it in the object lists, but I have query rights to the dictionary, will there be a search through the text of the XXX procedure?
Well, what have all this?
it seemed to you that the TS "is a member of the predefined database roles db_owner, db_ddladmin and db_securityadmin"
or does he have ALTER, CONTROL, TAKE OWNERSHIP or VIEW DEFINITION?
he clearly wrote that he only had permissions for certain procedures.
so what procedures are available for EXECUTE, exactly those procedures are a friend and will "see" in sys.sql_modules
you understood everything correctly
You will not have access to the text of procedures for which you do not have rights.
it will only be accessible to the text of the procedures that you "see" in the Object Explorer
thanks, I'm just not the owner and admin database
those. If I ask to run the query from the role of "db_owner, db_ddladmin and db_securityadmin", then there will be a search for all user procedures in all schemes, what is in the database?
and it is enough just to have VIEW DEFINITION.
role membership is optional.
as an option, let the person with the rights write you a search procedure with a parameter.
and in the procedure will indicate EXECUTE AS OWNER.
now it is enough to issue an EXEC for this procedure to someone like you,
and will search through all the procedures on behalf of that user.
Secondly, all of the above GUARANTEES access to all the definitions of the procedures, which is what the author of the question requires, if, of course, you did not understand this ...
Well, of course. And in my answer, something was somehow wrong about the same thing was written ...
You really decide. Or "it is enough to have just VIEW DEFINITION", or "well, and what’s all of this"
No, dear smoker, EXECUTE is what he has.
or what other rights did he get for the procedure?
think of such a fantastic example
when the user is given something different for some procedures.
and to make some sense
and he has this access to ALL PROCEDURES.
learning to read written:
and thirdly, where is the answer to the question
What texts will the TC see?
my answer is to this question: what procedures are available EXECUTE,
they will see.
and your quoting proves only that
what you did not understand: the vehicle does not have any of the above.
but this does not mean that the vehicle will not find anything.
because it is quite possible that what is required is precisely in those procedures for which there are rights.
and available EXECUTE just enough to find the desired
| Yasha123, |
Why did not you warn something that you suffer from semantic dyslexia ....
I will try to "translate" the dialogue into a language that you understand ...
The author asked: “I have [some] rights for some procedures. Will I be able to search the text in ANY procedures? ”
To which he gave me an answer with a quote from the documentation: “to search for a text in any procedures, any rights from the list are needed”
I hope, now you will be clearer. If not, then it is no longer on the technical forum to discuss, but at a reception at a speech therapist at best, and at a psychiatrist at worst.
This part refers to my advice
as a friend to search the texts of all procedures,
not having rights to them.
but if you do not like it, then you can simply issue it in the EXECUTE database.
for some reason I do not see it on your list, how is it?
and after all the question at the HARDWARE just also arose in connection with existence at it EXECUTE.
for he just "sees" some of the procedures, and can search in their texts.
and it turns out that he is looking for, having nothing from your list