issue checking the sys table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

issue checking the sys table

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-09-07 : 19:26:22

If I have a procedure which inserts and updates to certain tables is there any way i can find out where actually the updation is taken place using some sys tables.
Normally we will know by looking at the stored procedure but is there any other sys tables to which i can find a mapping

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-07 : 19:28:33

You would need to audit this activity using triggers.

Tara

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-09-07 : 19:29:36

You can use the syscomments table - that shows you the text of the stored procedure, but it doesn't provide a nice list of all items referenced.
You could possibly look at the sysdepends table, but it may not show all table dependencies for stored procedures (particularly if you're using dynamic SQL)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-07 : 19:34:38

Oh are we not talking about data?

Tara

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-09-07 : 19:39:17

How do I map to know which procedure is updated to which table does the text in the syscomments table help.I don't want to use trigger.trigger will definitely help but in this case they don't want to use trigger.which id helps to find the dependencies

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-09-07 : 19:39:54

No.. not the data tara

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-09-07 : 19:43:38

Not sure if I'm on the same wavelength as you two, but if you do a search in the text of syscomments to find UPDATE .... or INSERT INTO .... statements, you should be able to tell what tables are being touched. Not reliable, I admit. But it's a starting point.

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-09-07 : 19:56:51

Most of the text in the syscomments is create procedure how do u determine whether its an insert or an update

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-09-07 : 20:27:40

The sp text will follow 'Create Procedure' in that column.

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-09-07 : 21:28:53

But the text doesn't say that which table its updating or inserting to.Is there any way we can get it from sysdepends table

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-09-07 : 21:45:52

One way to check:
Write a stored procedure that updates a couple of tables
Save the procedure
Right click it in EM and select 'Show Dependencies'
This will tell you if sysdepends can help you.

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-09-07 : 22:00:57

Is that U go into the stored procedure right click the stored procedure and then an option showing 'display dependencies'.Is that what u r referring to..That just shows which objects are depended on it..it doesn't show whether its an update or an insert

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-09-07 : 22:33:21

Another is if I go to the sql analyzer and expand the stored procedure then i can see the dependencies in which it show the table and stored procedure inside it..but again..how will i determine whether an update or insert is happening again is there anywhere this structure is stored in a systable which shows which tables are being used by the stored procedure

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-09-07 : 23:02:01

what query should i use to see the dependencies

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-09-07 : 23:09:24

if I use this stored procedure sp_depends 'procedure name'.it will give me details...updated and select but no inserted..

Kristen
Test

22859 Posts

Posted - 2004-09-09 : 23:30:21

Test it?

CREATE TABLE dbo.foo
(
bar int
)
GO
CREATE PROCEDURE dbo.MySProc1
AS
INSERT INTO foo ( bar ) VALUES (1)
GO
EXEC sp_depends 'dbo.MySProc1'
GO
create procedure dbo.MySProc2
as
UPDATE dbo.foo SET bar = 2 WHERE bar = 1
GO
EXEC sp_depends 'dbo.MySProc2'
GO
DROP PROCEDURE dbo.MySProc1
DROP PROCEDURE dbo.MySProc2
DROP TABLE dbo.foo
GO

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources