|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Monday, August 11, 2008 7:06 AM
Points: 3,
Visits: 18
|
|
Hi,
Is there any means to know by which SP is a particular table getting modified. (in Both SQL 2000 and 2K5).
|
|
|
|
|
SSCertifiable
Group: General Forum Members
Last Login: Wednesday, July 01, 2009 12:21 PM
Points: 6,281,
Visits: 3,254
|
|
Run a trace on the server is one way. Check the transaction log is the other.
- GSquared
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
|
|
|
|
|
Mr or Mrs. 500
Group: General Forum Members
Last Login: 2 days ago @ 6:38 AM
Points: 569,
Visits: 540
|
|
| Check the SQL Server 2005 Default TRACE File... it might show you something... if you know the time... of Happenings.
|
|
|
|
|
SSC Veteran
Group: General Forum Members
Last Login: Thursday, July 02, 2009 9:04 AM
Points: 228,
Visits: 334
|
|
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 :)
|
|
|
|
|
Ten Centuries
Group: General Forum Members
Last Login: Yesterday @ 9:27 AM
Points: 1,227,
Visits: 3,459
|
|
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''
If everything seems to be going well, you have obviously overlooked something.
Before posting a question please read
Before posting a performance problem please read
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Saturday, May 09, 2009 8:00 PM
Points: 2,
Visits: 7
|
|
we can use sp_depends 'table_name' as well to see which objects are using the particular table.
Tasawar hussain
Netsol technologies
|
|
|
|
