June 9, 2004 at 3:09 am
I need to find out where a query is coming from. I have 15 databases on a server, and all have similar tables.
I am geting error messages in log file creating sql dump files. Before I can send dump files to Microsoft, I need to find out where
query is coming from. The dump files store the first 255 characters of the query. Would the query be in stored procedures or views???
June 9, 2004 at 4:38 am
Hi,
Have you tried SQL Profiler to trace this query. You can catch all the info needed if it runs.
Andy.
June 9, 2004 at 6:49 am
you probably want to check out syscomments
Max
June 10, 2004 at 7:35 pm
This query should return to you the name of the table and the query name:
You might want to create a cursor using:
SELECT so.name, sob.name as 'Stored Procedure name' FROM sysobjects so LEFT OUTER JOIN (sysobjects sob left outer join sysdepends on sob.id = sysdepends.id) on sysdepends.depid = so.id WHERE so.xtype = 'u' AND sob.xtype = 'p'AND NOT so.name = 'dtproperties'
Then loop through the cursor feeding the query name into:
select ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_DEFINITION From INFORMATION_SCHEMA.ROUTINES where ROUTINE_TYPE='PROCEDURE' and ROUTINE_NAME = 'query name from the cursor'
If you know the query name skip the use of a cursor and simply use the second query. Taking its output into a word doc I have gotten back typically as many as 2,386 character without counting spaces and 2,773 counting spaces in a single stored procedure.(Yes we write long complex stored procedures)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply