Table Modification

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Table Modification

Table Modification

Rate Topic

Display Mode

Topic Options

Author

Message

sachin.ranka

Posted Wednesday, July 02, 2008 4:57 AM

Forum 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

GSquared

Posted Wednesday, July 02, 2008 7:05 AM

SSCoach

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:55 PM
Points: 15,442, Visits: 9,571

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

MannySingh

Posted Wednesday, July 02, 2008 10:41 AM

SSChasing Mays

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 1:10 PM
Points: 646, Visits: 729

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

JamieX

Posted Wednesday, July 02, 2008 12:00 PM

Old 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

bitbucket-25253

Posted Thursday, July 03, 2008 12:49 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 9:02 AM
Points: 5,103, Visits: 20,216

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

malik.tasawar

Posted Monday, July 07, 2008 8:46 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 9:07 PM
Points: 5, Visits: 39

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 »

Permissions