There are four main database objects that contain stored SQL: VIEWS, TRIGGERS, FUNCTIONS and STORED PROCEDURES. Yesterday, I created a pattern searching tool for TRIGGERS. Today, I am going to clone and modify the code so that it will work with FUNCTIONS.
Again, 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.
-- 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);
The WHERE clause has an expression that filters in the correct object type. I will substitute ‘P’ with (‘FN’ , ‘IF’, ‘TF’) for this code to search for FUNCTIONS. Please refer to books on line for object type in sys.objects. Both the global and local temporary table names are changed to reflect FUNCTIONS in our new procedure named [usp_get_text4fn].
Enclosed is the full stored procedure for your usage.
A sample call to the tool to list and save all function code in the AdventureWorks database to a user defined table.
-- Save info to temporary table EXEC MSDB.[dbo].[usp_get_text4fn] 'AdventureWorks2008R2'
We can filter by object name if it is known. I am looking for the user defined function called GetContactInformation.
-- Look for a object SELECT * FROM [tempdb].[dbo].[sso_a1017012_text4fn] WHERE object_nm = 'ufnGetContactInformation'
We can filter by actual code if we know a key value. I am looking for any tables that have the word Inventory.
-- Look for a pattern SELECT * FROM [tempdb].[dbo].[sso_a1017012_text4fn] WHERE sql_txt LIKE '%Inventory%'
The image below show the results of this search. The first record matches a table that we were looking for. Unfortunately, the second match is only comments and is not important for our current research.
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 VIEWS.
June 2013 M T W T F S S « May 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
Cloud TagsAFTER ALTER TABLE ALTER TRIGGER CPAN perl modules CREATE DATABASE create function create procedure CREATE TABLE create trigger create view database administrator database developer DATA TYPES DDL DECLARE DELETE DROP DATABASE DROP TRIGGER DROP VIEW execute EXISTS FORMAT free code INSERT ISNULL John F. Miner III PASS perl script REPLACE SELECT sp_help sp_helptext SQL Server SQL Server Management Studio SSMS. string function sys.databases sys.objects sys.schemas TRIGGERS TRUNCATE TABLE TSQL UPDATE USER DEFINED VIEW vb script