There are four main database objects that contain stored (compiled) SQL: VIEWS, TRIGGERS, FUNCTIONS and STORED PROCEDURES.
Yesterday, I created a pattern searching tool for SQL code in STORED PROCEDURES. Today, I am going to clone and modify the code so that it will allow pattern searching on TRIGGERS.
First, I want to examine the dynamic SELECT statement that retrieves the user defined stored procedure names from a given database. This is the key element of the whole program.
I took the liberty to modify the original code so that it will work standalone. The current code returns the schema name, the object name, and a combination of both called full name. The full name is used by the system stored procedure called sp_helptext that returns the stored SQL code (text) for any object.
Both the sys.objects and sys.schemas system views are local to a given database. Therefore, the TSQL has to be dynamic since we do not know the database name ahead of time.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
-- DECLARE LOCAL VARIABLES DECLARE @VAR_DATABASE_NM VARCHAR(256) = 'AdventureWorks2008R2'; DECLARE @VAR_TSQL VARCHAR(2048) = ''; -- DYNAMIC SQL SINCE DB IS NOT SAME ALL THE TIME SELECT @VAR_TSQL = ' SELECT S.NAME + ''.'' + O.name AS FULL_NM, ' + ' S.NAME AS SCHEMA_NM, ' + ' O.NAME AS OBJECT_NM ' + ' FROM ' + @VAR_DATABASE_NM + '.sys.objects AS o (NOLOCK) INNER JOIN ' + @VAR_DATABASE_NM + '.sys.schemas AS s (NOLOCK) ON o.schema_id = s.schema_id ' + ' WHERE [TYPE] = ''P'' AND is_ms_shipped = 0' EXEC (@VAR_TSQL); |
Second, the WHERE clause has two conditions. The [is_ms_shipped] column indicates whether or not the object is user defined. The [type] column indicates the object type. This needs to be changed in our new procedure named [usp_get_text4tr].
The table below shows valid values for the [type] column. I will substitute ‘P’ with ‘TR’ for this new code. Both the global and local temporary table names are changed to reflect the TRIGGER object type.
- AF = Aggregate function (CLR)
- C = CHECK constraint
- D = DEFAULT (constraint or stand-alone)
- F = FOREIGN KEY constraint
- FN = SQL scalar function
- FS = Assembly (CLR) scalar-function
- FT = Assembly (CLR) table-valued function
- IF = SQL inline table-valued function
- IT = Internal table
- P = SQL Stored Procedure
- PC = Assembly (CLR) stored-procedure
- PG = Plan guide
- PK = PRIMARY KEY constraint
- R = Rule (old-style, stand-alone)
- RF = Replication-filter-procedure
- S = System base table
- SN = Synonym
- SQ = Service queue
- TA = Assembly (CLR) DML trigger
- TF = SQL table-valued-function
- TR = SQL DML trigger
- TT = Table type
- U = Table (user-defined)
- UQ = UNIQUE constraint
- V = View
- X = Extended stored procedure
Enclosed is the full stored procedure for your usage.
A sample call to the tool to list and save all stored procedure code in the AdventureWorks database to a user defined table.
|
1 |
EXEC MSDB.[dbo].[usp_get_text4tr] 'AdventureWorks2008R2' |
We can filter by object name if it is known. I am looking for the delete vendor trigger.
|
1 2 3 |
-- Look for a object SELECT * FROM [tempdb].[dbo].[sso_a1017012_text4tr] WHERE object_nm = 'dVendor' |
We can filter by actual code if we know a key value. I am looking for the product id.
|
1 2 3 |
-- Look for a pattern SELECT * FROM [tempdb].[dbo].[sso_a1017012_text4tr] WHERE sql_txt LIKE '%ProductId%' |
The image below show the results of this search.
Again, the user defined table created by this procedure stays in the system until tempdb is recreated upon server startup.
Next time, I will show how this query needs to be adjusted to display SQL code for FUNCTIONS.
I always was interested in this subject and stock still am, thank you for putting up.
Thx for taking the time to describe the terminlogy to the inexperienced persons!