June 7, 2011 at 12:49 am
Hi,
I've inheritted a software, working with ACCESS on the front end and SQL Server as the back end. now i've noticed a process from time to time that does something to a table in the SQL database and i want to locate it and kill it, but it's not in the Front-end code and i'm thinking it's in a stored procedure or function or trigger in the SQL backend.
I know the table name i'm looking for but i dont know how to search through all the stored procedure available in the database (there are a lot of them) without having to open each one and searching the string.
is there a "find" option i can run to search through the text of all the stored procedures and functions? is there an easy way i can script them and then search them useing the windows based "contains" find option? (to script i would need to script all the database objects, including the tables to see if its located in the trigger)
seems like a simple thing to find a string but i cant find the option in SQL to do it.
any help here would be hot.
June 7, 2011 at 12:58 am
Heya
I suppose the easiest way to figure out what is going on will be to use a tool called SQL profiler you can normally find this under client tools under the SQL server install.
With that you can see what triggers run on what tables and there execution time. If it is a sp causing you issues it may lean towards an indexing issue or something like that. But find the culprit with SQL profiler and go from there.
Sam
June 7, 2011 at 12:58 am
You can use a query like this:
SELECT OBJECT_NAME(id)
FROM syscomments
WHERE text LIKE '%whateveryoulookfor%'
Hope this helps
Gianluca
-- Gianluca Sartori
June 7, 2011 at 1:00 am
thx Sam,
however, i believe i found the method to do this on my own.
select * from sys.all_sql_modules
where [definition] like '%mystring%'
June 7, 2011 at 1:02 am
June 7, 2011 at 1:34 am
thx Gianluca
your way is more elegant as i can see the object names.
June 7, 2011 at 1:59 am
Actually syscomments is a compatibility view for SQL2000 and it should not be used. It splits the object definition on multiple rows when it exceeds 4000 characters. sys.sql_modules is the correct replacement for this view in SQL 2005+
SELECT OBJECT_NAME(object_id)
FROM sys.sql_modules
WHERE definition LIKE '%whateveryoulookfor%'
-- Gianluca Sartori
June 7, 2011 at 2:57 am
There's also information_schema.routines if you're an ANSI fan, but watch out for long definitions.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply