Presents your 
SQL SERVER E-NEWSLETTER for January 2, 2003
<------------------------------------------->

IDENTIFY STORED PROCEDURES WITH SYSCOMMENTS

If you need to identify a stored procedure that is using Data
Manipulation Language (DML) against a certain table, you can use syscomments
to find the stored procedures. You can also use syscomments to find information
regarding views that use specific tables.

Syscomments is a system table, and it's not advisable to include these
types of queries in production code. However, using syscomments as part of
administrative utilities used on an ad hoc basis is acceptable. Note
that you will not be able to use syscomments on procedures that have been
encrypted.

Syscomments contains entries for each view, rule, default, trigger,
CHECK constraint, DEFAULT constraint, and stored procedure.

The following simple script demonstrates using syscomments to generate a
report. The script consists of executing the system procedure
sp_helptext against a procedure and a view based on a table that's 
created in the script.

-- Create table MyTable.
CREATE TABLE dbo.MyTable
(pkey1 INT IDENTITY NOT NULL
   CONSTRAINT pkMyTable PRIMARY KEY,
col1 INT NULL)
GO
-- Create procedure to insert and select frrom MyTable.
CREATE PROCEDURE dbo.MyProc
AS
BEGIN
   INSERT dbo.MyTable (col1) VALUES (123)
   INSERT dbo.MyTable (col1) VALUES (234)
   INSERT dbo.MyTable (col1) VALUES (345)

   SELECT * FROM dbo.MyTable
END
GO
-- Create view to select from MyTable
CREATE VIEW dbo.MyView
AS
SELECT pkey1, col1 FROM dbo.MyTable
GO
-- Create script to find procs and views ussing DML on MyTable.
SELECT DISTINCT
   'EXEC sp_helptext ''dbo.' +
   OBJECT_NAME(id) + '''' +
   CHAR(10) + 'GO'
FROM syscomments
WHERE text LIKE '%MyTable%'
GO
-- Resulting output from query above
EXEC sp_helptext 'dbo.MyProc'
GO
EXEC sp_helptext 'dbo.MyView'
GO
-- Clean up schema
DROP PROCEDURE dbo.MyProc
GO
DROP VIEW dbo.MyView
GO
DROP TABLE dbo.MyTable
GO

------------------------------------