triggers for procedures - 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

General SQL Server Forums

New to SQL Server Programming

triggers for procedures

Author

Topic

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2006-11-29 : 07:03:44

is there a trigger that will be activated whena procedure is carried out?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-29 : 07:10:24

Do you mean to say you want to attach a trigger to procedure and get it fired when procedure starts its execution? I don't think there is any such way of doing it. In sql 2005, you have DDL triggers which gets fired when the object is created/altered/deleted but that won't help you, either.

Harsh Athalye
India.
"Nothing is Impossible"

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2006-11-29 : 07:16:52

hmm what we're trying to do is clean up a huge database, it has alot of tables which we think may be redundant or not in use... what would be the best approach

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-29 : 07:27:25

This is one crude way to find dead tables...although there may be indirect dependencies which are not covered by this script:

declare @tbl_name varchar(100),
	@tbl_type varchar(100)

declare c1 cursor for select table_name, table_type from information_schema.tables where objectproperty(object_id(table_name),'IsMSShipped') = 0

if not(object_id('#tbl2') is not null)
	drop table #tbl2

create table #tbl2
(
	tbl_name varchar(100),
	tbl_type varchar(100),
	total_recs int
)

open c1

fetch next from c1 into @tbl_name, @tbl_type

while @@fetch_status = 0
Begin

	IF not EXISTS (SELECT * FROM information_schema.routines where routine_definition like '' + '%' + @tbl_name + '%' + '') and
		not exists(Select * from information_schema.views where view_definition like ''+ '%' + @tbl_name + '%' + '')

	begin
		insert into #tbl2 select @tbl_name, @tbl_type, 0

		update t 
		set total_recs = rows 
		from sysindexes as si join #tbl2 as t
		on si.id = object_id(t.tbl_name) 
		where si.indid < 2
	end

	fetch next from c1 into @tbl_name, @tbl_type
end	

close c1
deallocate c1

select * from #tbl2

Harsh Athalye
India.
"Nothing is Impossible"

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2006-11-29 : 07:37:23

thanx heaps

what u mean by "may be indirect dependencies which are not covered by this script"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-29 : 07:40:52

For example the table is used by the front-end code (not through SPs). Another example could be that of cross-db reference where tables in current database in used by SPs or Views in some other database.

Harsh Athalye
India.
"Nothing is Impossible"

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2006-11-29 : 07:47:34

please excuse my simplicity but could u explain that more please

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-29 : 07:57:52

I think that was pretty simple.

If you have written code in front-end e.g. VB like this:

rs.open "Select * from t1, cn

then this kind of dependency on the table t1, won't be caught by my script.

Simple example of Cross-database reference will be like,

suppose you have following line in one of the SP in the other database which references some table in current database:

(in database - db2)


Create Procedure dbo.SomeProc
as
Select * from db1.dbo.sometable
GO

this kind of table dependency won't be caught by my script either.

I hope it is clear now.

Harsh Athalye
India.
"Nothing is Impossible"

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2006-11-29 : 08:13:07

ahhh right makes sense, well the databases im trying to find are all created using sql server 2005 with queries such as CREATE TABLE test (... varchar(20))...

etc

X002548
Not Just a Number

15586 Posts

Posted - 2006-11-29 : 09:52:28

quote:
Originally posted by rnbguy

hmm what we're trying to do is clean up a huge database, it has alot of tables which we think may be redundant or not in use... what would be the best approach

Well that's different than the original question, but triggers are still the right answer.

First, create a log table that will hold the tablename and the date of access, and maybe the user, and maybe the type of dml activity

Then generate a simple trigger for insert, update, delete that will insert to the log for every table in the database

Here read this

http://weblogs.sqlteam.com/brettk/archive/2006/11/29/35816.aspx

There is no trigger event for selects however...

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2006-11-29 : 21:57:24

thank you both for ur help once again i apologising for being too simple i hope one day i can be as good as u guys and teach simple people like me, i think i will use this example

http://weblogs.sqlteam.com/brettk/archive/2006/11/29/35816.aspx

however it would be good to know if the table is accesses for viewing purposes also not just editing

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2006-11-29 : 23:12:18

also how much bandwidth does this process hog up bcuz this server has thousands of transactions per minute

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-11-30 : 00:20:24

here's a fun way: you could rename every table you think is dead, and then see what breaks.

that's how HAL would do it. but then he'd cut your life support when you go to fix the break.

SqlSpec: a fast and comprehensive data dictionary generator for
SQL Server 2000/2005, Analysis Server 2005, Access 97/2000/XP/2003
http://www.elsasoft.org

Kristen
Test

22859 Posts

Posted - 2006-11-30 : 01:39:47

I was wondering how long before someone recommended a "Scream" test!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources