How to get the sql-text from a view/trigger
-
Hello! Good morning evbdy
Is there any chance that I can retrieve the sql code of a trigger or view?
I imagine that something like that would be perfect
Declare @sqltext varchar(1000)
sp_getsqltext 'MyTrigger' IN, @sqltext OUT
print @sqltext
(forget syntax errors
thank you!
-
...or may be querying the data dictionary?
please help
-
hi
If your looking for View/proc or Table Definition/text then
For Table it is sp_help
for text of View/Proc it is sp_helptext
regards
padmakumar
-
thanks for your reply
sp_helptext is ok for showing trigger code lines in query analyzer,
BUT I need to store all this text code in one variable.
Can this be archieved in SQL2000?
-
hi again!
this is working for me, now:
Create Table #TEMP (Line varchar(8000))
Insert Into #TEMP EXEC ('sp_helptext TIDU_AUDIT_TestTable2')
Declare @FULL_CODE varchar(8000)
Declare @CODE_LINE varchar(8000)
Declare CURSOR_CODE_LINES CURSOR LOCAL FOR
Select * from #TEMP
OPEN CURSOR_CODE_LINES
FETCH NEXT From CURSOR_CODE_LINES Into @CODE_LINE
WHILE @@FETCH_STATUS = 0
Begin
Set @FULL_CODE = @FULL_CODE + char(13) + @CODE_LINE
Print @CODE_LINE
FETCH NEXT From CURSOR_CODE_LINES Into @CODE_LINE
End
Drop Table #TEMP
thanksss for the clues
-
Miguel - you could also do something like this w/out a cursor ...
Northwind database... declare @strViewText varchar(8000) select @strViewText = text from syscomments where id = object_id('dbo.Invoices') print @strViewText
**ASCII stupid question, get a stupid ANSI !!!** -
Great! I preffer this last way!
As long as my trigger text is longer than 4000, table syscomments.name returns 2 rows.
My mission is to create a new trigger for a given table, from the dbo.TIDU_Audit_template trigger wich belongs to dbo.TheTableThatHasTheTemplateTrigger.
So far I did this to handle 7900 chars long triggers:
CREATE PROCEDURE [dbo].[sp_create_audit_trigger_for] @TABLE sysname
AS
Declare @CODE_PART1 varchar(4000)
Declare @CODE_PART2 varchar(4000)
--////////// Get template trigger code //////////
select @CODE_PART1 = max(text) from syscomments
where id = object_id('dbo.TIDU_Audit_template')
select @CODE_PART2 = min(text) from syscomments
where id = object_id('dbo.TIDU_Audit_template')
--////////// Customize Trigger Code //////////
Set @CODE_PART2 = RIGHT(@CODE_PART1, 100) + @CODE_PART2 --this avoids loosing characters when replacing 'template' for a longer table name
Set @CODE_PART1 = LEFT(@CODE_PART1, 4000-100)
Set @CODE_PART1 = REPLACE(@CODE_PART1, 'ON dbo.TheTableThatHasTheTemplateTrigger', 'ON dbo.' + @TABLE)
Set @CODE_PART1 = REPLACE(@CODE_PART1, 'Template', @TABLE)
Set @CODE_PART2 = REPLACE(@CODE_PART2, 'Template', @TABLE)
--////////// CREATE trigger //////////
execute(@CODE_PART1 + @CODE_PART2)
Print @CODE_PART1 + @CODE_PART2
GO
Note: This solution comes from the impossibility to know the table that fired the trigger in the scope of the same trigger (RE: Any way to get table name''''s trigger?)
Thanks again to everybody for helping!
-
Miguel - it's not at all clear (at least to me) what exactly you hope to accomplish..
It seems that you want to dynamically create a trigger on a table that is passed as parameter to the stored procedure and each time you're creating the exact same trigger using a template - I do not understand the purpose of this at all...are all these tables identical in their schema - why not set the triggers on each of the tables individually ?!?!
Also, the use of aggregate functions on text data...you'd be much better off using a cursor to loop through the rows and then concatenating the results...but if you already "know" the text...meaning you are creating triggers based on a template where nothing changes but the table name - why not use the text directly as string variables in your procedure ?!
Lastly, if you want a resultset of trigger names and the corresponding tables then you could use a query like this..
SELECT A.name as TableName, B.name AS TriggerName FROM sysobjects A INNER JOIN sysobjects B ON A.id = B.parent_obj AND B.xtype='TR' ORDER BY A.name
**ASCII stupid question, get a stupid ANSI !!!** -
It seems that you want to dynamically create a trigger on a table that is passed as parameter to the stored procedure and each time you're creating the exact same trigger using a template - I do not understand the purpose of this at all...are all these tables identical in their schema - why not set the triggers on each of the tables individually ?!?!
Because every table is different, and I want to create the trigger progamatically.
I the USER decides to audit a given table, he/she/it only have to insert a record in [AuditTables].
Also, the use of aggregate functions on text data...you'd be much better off using a cursor to loop through the rows and then concatenating the results...but if you already "know" the text...meaning you are creating triggers based on a template where nothing changes but the table name - why not use the text directly as string variables in your procedure ?!
I ca'nt use strings to store the trigger because its code is so long it doesn't fit, and don't want to create a global text variable. The one code I posted (without testing) using a cursor didn't work for that reason (I think).
Lastly, if you want a resultset of trigger names and the corresponding tables then you could use a query like this..
SELECT A.name as TableName, B.name AS TriggerName
FROM sysobjects A
INNER JOIN sysobjects B ON
A.id = B.parent_obj
AND B.xtype='TR'
ORDER BY A.name
I cant' use this code to know the table that fired the trigger, c'ause in the scope of a trigger I don't even know the trigger name.
THANKS for your thoughts.... I swear I'll post my audit paradigm when finished
-
Migel,
I have already adviced you against dynamically creating triggers on the flight on that same thread you posted above.
If you still insist I just wish you good luck
* Noel -
yes... and probably you have the reason.
I really took you in consideration, but as long I'm finishing... first I'd like to see what happens on my own.
tkx
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply