Re: sp_prepexec and sys_comments

C,

Syscomments is for code that is persisted in a SQL Server database, such as
a stored procedure, function, view, etc. Prepared statements are not to be
found there.

There is a system table/view available in 2000 and 2005 that may help you in
some cases:
select * from master.dbo.syscacheobjects

This is deprecated in SQL Server 2005, so you should investigate the new
dynamic management views for 2005. This page from the BOL has a link to a
discussion of the new views that you should be using:
http://msdn2.microsoft.com/en-us/library/ms187815.aspx

RLF



<codefragment@xxxxxxxxxxxxxx> wrote in message
news:bd4ad615-e8fa-453f-b72f-bfee5d874e25@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
(apologies for the cross posting but 5 days with no joy in other
newsgroup)

Hi
I'm sure lots of people have asked this before, but I can't find
any
threads so:

Assuming you haven't had your profiler open for long enough is there
any way of getting to the procedure
code that was prepared with sp_prepexec in query analyser?
If not why not? (I'm sure theres a good reason but I'd like to
know)

i.e. something in syscomments or similiar?


thanks


C


.

Relevant Pages

  • Re: Renaming Stored Prcedures via EM
    ... Internally SQL Server keeps the stored procedure name in two places. ... Database object names are in table called sysobjects. ... stored in syscomments. ...
    (microsoft.public.sqlserver.programming)
  • Re: Stored procedure Replication
    ... A stored procedure is stored in syscomments, ... we can't replicate system tables. ... Paul Ibison SQL Server MVP, ...
    (microsoft.public.sqlserver.replication)
  • RE: Problem when converting from mssql6.5 to mssql2000
    ... If the stored procedure text is missing from the syscomments table the ... upgrade will not be able to upgrade the stored procedures. ... Connect to the SQL Server 6.5 server and run the following command from ...
    (microsoft.public.sqlserver.setup)
  • Re: How to get list of EventClasses in MSSQLServer2000
    ... statement inside a stored procedure has completed.') ... SQL Server statement or stored procedure.') ... Plan','Displays the plan tree of the Transact-SQL statement executed.') ... Login','Occurs when a SQL Server login is added or removed; ...
    (microsoft.public.sqlserver.security)
  • Re: Accessing FoxPro Free Table
    ... which the SQL Server service is running. ... account, ... > If you are creating a stored procedure and you want> to make sure that the procedure definition cannot be> viewed by other users, you can use the WITH ENCRYPTION> clause. ... The procedure definition is then stored in an> unreadable form. ...
    (microsoft.public.data.odbc)
Loading