/****************************************************** * * Name: usp_get_text4fn.sql * * Design Phase: * Author: John Miner * Date: 01-23-2013 * Purpose: Grab the TSQL for all functions * in a given database. * ******************************************************/ /* Which database to use. */ USE msdb GO /* Delete the existing stored procedure. */ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_get_text4fn]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[usp_get_text4fn] GO /* Create the new stored procedure. */ CREATE PROCEDURE [dbo].[usp_get_text4fn] @VAR_DATABASE_NM VARCHAR(256) = '*', @VAR_VERBOSE_IND TINYINT = 1 AS BEGIN -- -- 0 - Check input parameter -- -- No Input IF (@VAR_DATABASE_NM = '*') BEGIN PRINT '[usp_get_text4fn] - please select a valid database name to get TSQL from.' PRINT ' ' RETURN END -- Bad Input IF NOT EXISTS (SELECT 1 FROM sys.databases AS d (NOLOCK) WHERE d.name = @VAR_DATABASE_NM AND d.state_desc = 'ONLINE') BEGIN PRINT '[usp_get_text4fn] - please select a valid database name to get TSQL from. Database (' + @VAR_DATABASE_NM + ') is invalid.' PRINT ' ' RETURN END -- -- 1 - Prepare temp table for user -- -- NO COUNTING SET NOCOUNT ON; -- DECLARE LOCAL VARIABLES FOR STEP 1 DECLARE @VAR_TABLE_NM VARCHAR(256); DECLARE @VAR_TSQL VARCHAR(2048); -- GET USER NAME FOR TEMP TABLE SET @VAR_TABLE_NM = LOWER('[TEMPDB].[DBO].[' + ISNULL(REPLACE(SUSER_SNAME(SUSER_SID()), '\', '_'), 'DEFAULT2') + '_text4fn]'); -- TRUNCATE OR CREATE TABLE? TABLE IS TEMPORARY TO SERVER. IF EXISTS (SELECT * FROM TEMPDB.sys.objects AS T1 (NOLOCK) WHERE T1.object_id = OBJECT_ID(@VAR_TABLE_NM) AND type in (N'U')) BEGIN SET @VAR_TSQL = 'TRUNCATE TABLE ' + @VAR_TABLE_NM; EXECUTE (@VAR_TSQL); END ELSE BEGIN SET @VAR_TSQL = 'CREATE TABLE ' + @VAR_TABLE_NM + ' (' + ' [auto_id] int identity(1, 1) not null ' + ' , [line_no] int null ' + ' , [database_nm] sysname null ' + ' , [schema_nm] sysname null ' + ' , [object_nm] sysname null ' + ' , [sql_txt] text null ' + ' ); '; EXECUTE (@VAR_TSQL); END -- -- 2 - Load temp table with TSQL -- -- DECLARE LOCAL VARIABLES FOR STEP 2 DECLARE @VAR_FULL_NM AS SYSNAME; DECLARE @VAR_OBJECT_NM AS SYSNAME; DECLARE @VAR_SCHEMA_NM AS SYSNAME; -- TABLE TEMPORARY TO PROCESS (SPID) CREATE TABLE #TEXT4VW ( FULL_NM sysname, SCHEMA_NM sysname, OBJECT_NM sysname ); -- DYNAMIC SQL SINCE DB IS NOT SAME ALL THE TIME SELECT @VAR_TSQL = ' INSERT INTO #TEXT4VW ' + ' 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] in (''FN'', ''IF'', ''TF'') AND is_ms_shipped = 0' EXEC (@VAR_TSQL); -- USE CURSOR TO PROCESS THE RESULTS DECLARE CUR_OBJS CURSOR FOR SELECT FULL_NM, SCHEMA_NM, OBJECT_NM FROM #TEXT4VW ORDER BY FULL_NM -- OPEN THE CURSOR OPEN CUR_OBJS; -- EXAMINE EACH OBJECT AND GET DETAILED INFO WHILE (1=1) BEGIN -- GRAB A ROW OF INFORMATION FETCH NEXT FROM CUR_OBJS INTO @VAR_FULL_NM, @VAR_SCHEMA_NM, @VAR_OBJECT_NM; -- CHECK FOR PROCESSING ERRORS IF (@@FETCH_STATUS < 0) BREAK; -- MAKE UP THE SQL STMT & EXECUTE SELECT @VAR_TSQL = 'use [' + @VAR_DATABASE_NM + ']; insert into ' + @VAR_TABLE_NM + '(sql_txt) exec sp_helptext @objname = N''' + @VAR_FULL_NM + ''''; EXEC (@VAR_TSQL); -- UPDATE TO THE CORRECT DATABASE, SCHEMA & OBJECT NAME AS WELL AS LINE NUMBER SELECT @VAR_TSQL = 'update ' + @VAR_TABLE_NM + ' set database_nm = ''' + @VAR_DATABASE_NM + ''', schema_nm = ''' + @VAR_SCHEMA_NM + ''', object_nm = ''' + @VAR_OBJECT_NM + ''', line_no = auto_id + 1 - (select min(auto_id) from ' + @VAR_TABLE_NM + ' where schema_nm is null) ' + ' where schema_nm is null;'; EXEC (@VAR_TSQL) END -- CLOSE THE CURSOR CLOSE CUR_OBJS; -- RELEASE THE CURSOR DEALLOCATE CUR_OBJS; -- CHECK TEMP TABLE FOR RESULTS, MESSAGE ON IF VERBOSE IF (@VAR_VERBOSE_IND = 1) BEGIN PRINT '[usp_get_text4fn] - please select from table ' + @VAR_TABLE_NM + ' for your output.' PRINT ' ' END END GO -- -- Sample Usage -- -- Save info to temporary table --EXEC MSDB.[dbo].[usp_get_text4fn] 'AdventureWorks2008R2' -- Look for a object --SELECT * FROM [tempdb].[dbo].[sso_a1017012_text4fn] --WHERE object_nm = 'ufnGetContactInformation' -- Look for a pattern --SELECT * FROM [tempdb].[dbo].[sso_a1017012_text4fn] --WHERE sql_txt LIKE '%Inventory%'