Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Table Modification Expand / Collapse
Author
Message
Posted Wednesday, July 2, 2008 4:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 10, 2009 1:02 AM
Points: 3, Visits: 19
Hi,
Is there any means to know by which SP is a particular table getting modified. (in Both SQL 2000 and 2K5).
Post #527158
Posted Wednesday, July 2, 2008 7:05 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 13,872, Visits: 9,600
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
Post #527275
Posted Wednesday, July 2, 2008 10:41 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, August 15, 2014 11:42 AM
Points: 646, Visits: 732
Check the SQL Server 2005 Default TRACE File... it might show you something... if you know the time... of Happenings.

Maninder
www.dbanation.com
Post #527440
Posted Wednesday, July 2, 2008 12:00 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, October 15, 2010 8:23 AM
Points: 371, Visits: 437
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 :)
Post #527491
Posted Thursday, July 3, 2008 12:49 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:00 PM
Points: 5,333, Visits: 25,277
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.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #528333
Posted Monday, July 7, 2008 8:46 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 8:21 PM
Points: 5, Visits: 70
we can use sp_depends 'table_name' as well to see which objects are using the particular table.

Tasawar hussain
Netsol technologies
Post #529734
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse