Link to home
Start Free TrialLog in
Avatar of Member_2_6478753
Member_2_6478753Flag for United States of America

asked on

tsql how select tablename from triggertext

i have a lot of row each row contains the trigger of each table
i want to get the name of table in each trigger code related to each table

for example the column contains the trigger code for each table is name

for example in trigger text we have like this format !


USE [CGI_SMS_SERVICES]
GO

/****** Object:  Trigger [dbo].[cgi_sms_d82114_datetime]    Script Date: 11/09/2012 22:45:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO

/****** Object:  Trigger dbo.cgi_sms_d82114_datetime    Script Date: 2/15/2006 10:08:55 AM ******/
ALTER TRIGGER [dbo].[cgi_sms_d82114_datetime] ON [dbo].[CGI_SMS_d82114]
FOR INSERT
AS
DECLARE @rec_id BIGINT
DECLARE @thedatetime DATETIME
DECLARE @thedate CHAR(8)
DECLARE @thetime CHAR(6)
DECLARE @reqport BIGINT
DECLARE @recdport BIGINT
DECLARE @message NVARCHAR(500)
DECLARE @message1 NVARCHAR(200)
DECLARE @message2 NVARCHAR(200)
DECLARE @smscode VARCHAR(20)
DECLARE @msisdn VARCHAR(50)
DECLARE @coding INT
DECLARE @count INT

SELECT @thedatetime = getdate()

SELECT @rec_id = (
		SELECT id
		FROM inserted
		)

SELECT @thedate = str(datepart(yyyy, @thedatetime), 4) + str(datepart(mm, @thedatetime), 2) + str(datepart(dd, @thedatetime), 2)

SELECT @thetime = str(datepart(hh, @thedatetime), 2) + str(datepart(mi, @thedatetime), 2) + str(datepart(ss, @thedatetime), 2)

SELECT @thedate = replace(@thedate, ' ', '0')

SELECT @thetime = replace(@thetime, ' ', '0')

SELECT @reqport = (
		SELECT reqport
		FROM inserted
		)

SELECT @message = (
		SELECT ltrim(rtrim(message))
		FROM inserted
		)

SELECT @msisdn = (
		SELECT ltrim(rtrim(msisdn))
		FROM inserted
		)

SELECT @recdport = (
		SELECT recdport
		FROM inserted
		)

SELECT @coding = (
		SELECT coding
		FROM inserted
		)

UPDATE cgi_sms_d82114
SET datein = @thedate,
	timein = @thetime
WHERE id = @rec_id

INSERT INTO cgi_sms_Quiz..NJMAR_REGISTRATION (
	reqport,
	msisdn,
	message,
	coding,
	datein,
	timein
	)
VALUES (
	@reqport,
	@msisdn,
	@message,
	@coding,
	@thedate,
	@thetime
	)

Open in new window



the result what i need is
cgi_sms_Quiz..NJMAR_REGISTRATION  cgi_sms_d82114  ??
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

If the names of the tables are consistent you may be able to do this.  For example, if all the  TRIGGERs contain tables that are consistently named cgi_sms_Quiz..???  then it is doable.
Avatar of Member_2_6478753

ASKER

EXEC rayasport.dbo.uspReceivesms
Insert into nokia.dbo.[in] (msisdn, message, datetimein,reqport, lang) values (@msisdn, @message, @thedatetime, @reqport,
exec spsms_received_forHiTV @reqport, @recdport, @msisdn, @message, @coding, 'KSA','Zain'
EXEC radiomix.dbo.spReceivesms 
Insert into [3ilag3alhawa2].dbo.temp (msisdn, message, datetimein, reqport) values (@msisdn, @message, @thedatetime, @reqport)
EXEC  BahrainApp.dbo.[ReceiveSms] @reqport , @msisdn, @message;
Insert into womenservice.dbo.temp (msisdn, message, datetimein, reqport) values (@msisdn, @message, @thedatetime, @reqport)

Open in new window


result are different because each trigger have something to do ...  
i want to select the entry code exec ...................... or insert to ..................................
for each trigger text ?
If you don't have a consistent pattern than the best you can do is return all the TRIGGER code and you will have to search through for the tables used to INSERT INTO.
i have already extract the trigger code i want to make a regex to filter insertion ?
i am afraid I have no idea.
You're trying to discover, within the code of the trigger, the name of the table that owns the trigger?

select p.[name] AS TableName
from sysobjects p
join sysobjects c
on p.id = c.parent_obj
where c.id = @@PROCID

Open in new window


hth

Mike
You're trying to discover, within the code of the trigger, the name of the table that owns the trigger?
That is the easy part and can be best done using the sys.triggers VIEW.  The difficult part is to find the table or tables that are used to INSERT INTO in the TRIGGER itself.
This will gt you the stored procedure that holds the code that sprang the trigger. Further than this I've never needed to go. If you just enter

INSERT INTO t (c1) VALUES(@i)

Open in new window


after doing the code below, you'll get the actual line.

CREATE TABLE t (c1 INT)
GO
CREATE TRIGGER dbo_t ON t FOR INSERT
AS
DBCC INPUTBUFFER(@@SPID)
GO
CREATE PROCEDURE dbo.getT (@i INT)
AS
SET NOCOUNT ON
INSERT INTO t (c1) VALUES(@i)
GO

EXEC dbo.getT 1

Open in new window


hth

Mike
Mike,

Let me suggest you re-read the original question.  The details are explained there.
ac,

it appears that he wants to find out the code that caused the trigger code. What I just posted will give you the line of code if it's not a stored proc; like you, I don't know of any way of getting closer.

Mike
it appears that he wants to find out the code that caused the trigger code.
That is true, but they also want the table in the TRIGGER code that has the INSERT INTO and that is the complicated part.  Here is the abbreviated code:
ALTER TRIGGER [dbo].[cgi_sms_d82114_datetime] ON [dbo].[CGI_SMS_d82114]
FOR INSERT
AS
...
INSERT INTO cgi_sms_Quiz..NJMAR_REGISTRATION (

This is what they expect:
cgi_sms_Quiz..NJMAR_REGISTRATION  cgi_sms_d82114  ??
Maybe I'm being dense, but the author of the trigger already knows what table he or she is inserting into. Or does he mean that he has a table full of fields with the text of the triggers, and he wants the trigger name and the table that the trigger inserts into?

In that case, with the code in a varchar, one could search for the string "insert into " and then walk the string on until finding a break character (space, tab, <LF><CR>, etc) , unless of course one encountered a "[", in which case one would be looking for the closing "]" before continuing on looking for a break character.  

Then you'd have the trigger name and table it inserts into to store and, if needed, pull out at runtime to combine with info about what called it.

Or am I totally off-base?
ASKER CERTIFIED SOLUTION
Avatar of Member_2_6478753
Member_2_6478753
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
i have make a php script to extract all table name from each trigger text:)