July 2, 2008 at 4:57 am
Hi,
Is there any means to know by which SP is a particular table getting modified. (in Both SQL 2000 and 2K5).
July 2, 2008 at 7:05 am
Run a trace on the server is one way. Check the transaction log is the other.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 2, 2008 at 10:41 am
Check the SQL Server 2005 Default TRACE File... it might show you something... if you know the time... of Happenings.
Maninder
www.dbanation.com
July 2, 2008 at 12:00 pm
here is a query i use frequently when taking over databases I didn't create:
select b.table_name, a.name, a.type_desc
from information_schema.tables b left join sys.procedures a on OBJECT_DEFINITION(a.OBJECT_ID) like '%'+b.table_name+'%'
where b.table_type = 'BASE TABLE' and b.table_name in ('TABLENAME1','TABLENAME2')
order by b.table_name asc
Put the list of table names you are looking for in the stored procedures.
This just does a search for that keyword. If you called your table 'select' or 'from', this won't be much help 🙂
July 3, 2008 at 12:49 pm
This SP will find the desired text as shown in the procedure and can be used in SQL 2000 and 2005 - note it will find the watchword even if it is in a comment within the SP or Function
CREATE PROCEDURE UDP_FindWordsInaSP
@Watchword varchar(50)
AS
SELECT distinct
'type' = case type
when 'FN' then 'Scalar function'
when 'IF' then 'Inlined table-function'
when 'P' then 'Stored procedure'
when 'TF' then 'Table function'
when 'TR' then 'Trigger'
when 'V' then 'View'
end,
o.[name],
watchword = @Watchword
FROM dbo.sysobjects o (NOLOCK)
JOIN dbo.syscomments c (NOLOCK)
ON o.id = c.id
WHERE charindex(lower(@Watchword),lower(text)) <> 0
and o.type in ('FN', 'IF', 'P', 'TF', 'TR', 'V')
and o.name NOT LIKE 'dt%' and o.name NOT LIKe 'sys%'
and o.name NOT LIKE 'UDP_FindWordsInaSP'
ORDER BY type, o.[name]
-- run as UDP_FindWordsInaSP 'your table name''
July 7, 2008 at 8:46 pm
we can use sp_depends 'table_name' as well to see which objects are using the particular table.
Tasawar hussain
Netsol technologies
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply