Go to Main page..

How to find in what stored procedures or functions the necessary string is found (Microsoft SQL Server





How to find in what stored procedures or functions the desired string occurs [new]
How to find in what stored procedures or functions the necessary string is found?

In order not to open each separately.
Re: How to find in what stored procedures or functions the necessary line is encountered [new]
alm2,

for seed:
 [ 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 :)
Re: How to find in what stored procedures or functions the necessary line is encountered [new]
select * from syscomments where text like '% something%'
Re: How to find in what stored procedures or functions the necessary line is encountered [new]
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!
Re: How to find in what stored procedures or functions the necessary line is encountered [new]
alm2
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!
You also wrote! OBJECT_DEFINITION ()!
Re: How to find in what stored procedures or functions the necessary line is encountered [new]
alm2
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?
Re: How to find in what stored procedures or functions the necessary line is encountered [new]
a1ex
select * from syscomments where text like '% something%'
It is a bad idea. There are lines of procedure, cut into pieces. By 4000 characters.
The search string may be stored in parts on adjacent rows.
Re: How to find in what stored procedures or functions the necessary line is encountered [new]
Good E - Eh
alm2,

for seed:
 [ 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 :)


and for functions add 'FN', 'IF', 'TF'
Re: How to find in what stored procedures or functions the necessary line is encountered [new]
just the direction to search, no more. good or bad this idea can determine only one who builds the final query
Re: How to find in what stored procedures or functions the necessary line is encountered [new]
yeah
Good E - Eh
alm2,

for seed:
 [ 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 :)


and for functions add 'FN', 'IF', 'TF'
Then the triggers - 'TR'
Re: How to find in what stored procedures or functions the necessary line is encountered [new]
iap
yeah
skipped ...


and for functions add 'FN', 'IF', 'TF'
Then the triggers - 'TR'


Well, about the triggers of the vehicle did not ask :)
Re: How to find in what stored procedures or functions the necessary line is encountered [new]
alm2,

select BODY.ID, BODY. [TEXT], OBJ.NAME
from syscomments BODY
INNER JOIN sys.objects OBJ ON BODY.ID = OBJ.object_id
where
BODY. [TEXT] like '% what needs to be found in the body of the object%'
Re: How to find in what stored procedures or functions the necessary line is encountered [new]
alm2,
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 :)
Re: How to find in what stored procedures or functions the necessary line is encountered [new]
Barclay
alm2,
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:
https://www.red-gate.com/products/sql-development/sql-search/index
Re: How to find in what stored procedures or functions the necessary line is encountered [new]
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?
Re: How to find in what stored procedures or functions the necessary line is encountered [new]
Sintetik,

Have you been banned in Google or is religion not allowed to use search?

https://docs.microsoft.com/ru-ru/sql/t-sql/functions/object-definition-transact-sql?view=sql-server-2017
Permissions

Definitions of system objects are visible to all.

User object definitions are visible to the owner of the object or participants,
who are granted the following permissions: ALTER, CONTROL, TAKE OWNERSHIP, or VIEW DEFINITION .
These permissions are implicitly granted to members of the db_owner, db_ddladmin, and db_securityadmin predefined database roles .
Re: How to find in what stored procedures or functions the necessary line is encountered [new]
Schukina Anna
Definitions of system objects are visible to all.

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?
Re: How to find in what stored procedures or functions the necessary line is encountered [new]
Schukina Anna
Sintetik,

Have you been banned in Google or is religion not allowed to use search?

https://docs.microsoft.com/ru-ru/sql/t-sql/functions/object-definition-transact-sql?view=sql-server-2017
Permissions

Definitions of system objects are visible to all.

User object definitions are visible to the owner of the object or participants,
who are granted the following permissions: ALTER, CONTROL, TAKE OWNERSHIP, or VIEW DEFINITION .
These permissions are implicitly granted to members of the db_owner, db_ddladmin, and db_securityadmin predefined database roles .


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
Re: How to find in what stored procedures or functions the necessary line is encountered [new]
Sintetik
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?

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
Re: How to find in what stored procedures or functions the necessary line is encountered [new]
Yasha123
Sintetik
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?

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?
Re: How to find in what stored procedures or functions the necessary line is encountered [new]
Sintetik
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?

Yes.
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.
Re: How to find in what stored procedures or functions the necessary line is encountered [new]
Yasha123
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
Firstly, about EXECUTE - this is your own idea. A comrade only said that he had “not all the procedures”. What kind of rights - not specified. Therefore, if anyone seemed, then, most likely, you.
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 ...
Re: How to find in what stored procedures or functions the necessary line is encountered [new]
Yasha123
Yes.
and it is enough just to have VIEW DEFINITION.

Well, of course. And in my answer, something was somehow wrong about the same thing was written ...

Schukina Anna
Sintetik,

Have you been banned in Google or is religion not allowed to use search?

https://docs.microsoft.com/ru-ru/sql/t-sql/functions/object-definition-transact-sql?view=sql-server-2017
Permissions

Definitions of system objects are visible to all.

User object definitions are visible to the owner of the object or participants,
who are granted the following permissions: ALTER, CONTROL, TAKE OWNERSHIP, or VIEW DEFINITION .
These permissions are implicitly granted to members of the db_owner, db_ddladmin, and db_securityadmin predefined database roles.


You really decide. Or "it is enough to have just VIEW DEFINITION", or "well, and what’s all of this"

Re: How to find in what stored procedures or functions the necessary line is encountered [new]
Schukina Anna
Firstly, about EXECUTE - this is your own idea. A comrade only said that he had “not all the procedures”. What kind of rights - not specified.

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

Schukina Anna
Secondly, all of the above GUARANTEES access to all definitions of procedures.

that's it.
and he has this access to ALL PROCEDURES.
learning to read written:
author
I do not have all the procedures

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
Re: How to find in what stored procedures or functions the necessary line is encountered [new]
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.
Re: How to find in what stored procedures or functions the necessary line is encountered [new]
Schukina Anna
You really decide. Or "it is enough to have just VIEW DEFINITION", or "well, and what’s all of this"

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



Old articles you may read here
Go to Main page






ZZZZZZZZZZZZ