{"id":1463,"date":"2012-01-27T03:08:14","date_gmt":"2012-01-27T03:08:14","guid":{"rendered":"http:\/\/craftydba.com\/?p=1463"},"modified":"2017-10-12T12:21:00","modified_gmt":"2017-10-12T12:21:00","slug":"searching-stored-sql-part-1","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=1463","title":{"rendered":"Searching Stored SQL – Part 1"},"content":{"rendered":"

Today, I am going to start building on some the articles I did in the past. We are going to create tools to pattern search stored SQL statements. Four main objects that contain stored SQL are VIEWS<\/a>, TRIGGERS<\/a>, FUNCTIONS<\/a> and STORED PROCEDURES<\/a>.<\/p>\n

Recently at work, I was asked to find all SQL code that had a certain business rule. The task is to return all objects that reference to a certain temperature process named T1. One solution to this problem is to buy a tool like SQL Search from Red Gate. But this tool does not list the actual line number in the code that a pattern was found. Also, advanced search logic like “find all stored procedures that have a variable named T1 and initials of JFM, the SQL developer on the project” can not be performed.<\/p>\n

Given this dilemma, I decided to write a tool, a user defined stored procedure, to do just want I wanted. There is a nice system stored procedure called sp_helptext<\/a> that returns the stored SQL code (text) for any object. Today, I am going to write a tool for use with stored procedures.<\/p>\n

The next step in the process is to write down psuedo code for the algorithm I wanted. <\/p>\n

1 – Ask the user for a database name.
\n2 – Create a global temp table in tempdb to store results.
\n3 – Dump details of the objects into a local temp table.
\n4 – Add information like database name, schema name, object name and line numbers.<\/p>\n

The header section of the CREATE PROCEDURE defines any parameters that are passed as input. I am going to ask the user for a valid database name and a optional verbose indicator.<\/p>\n

\r\n--\r\n-- Create the new stored procedure\r\n--\r\n\r\n\r\nCREATE PROCEDURE [dbo].[usp_get_text4sp]\r\n    @VAR_DATABASE_NM VARCHAR(256) = '*',\r\n    @VAR_VERBOSE_IND TINYINT = 1\r\nAS\r\nBEGIN\r\n<\/pre>\n<\/p>\n
Here are the TRANSACTIONAL SQL statements I am going to use for Step 1.<\/p>\n
IF<\/a> – statement allows for conditional execution of code.
\nBEGIN & END<\/a> – statements mark the start and stop of a code block.
\nPRINT<\/a> – statement returns messages to the output window in SSMS.
\nRETURN<\/a> – statement exits the stored procedure immediately.<\/p>\n
The first task in step 1 of the algorithm is to make sure that the name of the database is valid.  I first test the variable to make sure that it is not equal to the default value of ‘*’.  If it is the default, the user did not pass a value.<\/p>\n
The second task in step 1 of the algorithm is to make sure the name is valid.  This is done by check the system view sys.databases<\/a> for the supplied name.<\/p>\n
\r\n--\r\n-- Check input parameters\r\n--\r\n\r\n-- No Input\r\nIF (@VAR_DATABASE_NM = '*')\r\nBEGIN\r\n    PRINT '[usp_get_text4sp] - please select a valid database name to get TSQL from.'\r\n    PRINT ' '\r\n    RETURN\r\nEND\r\n\r\n-- Bad Input\r\nIF NOT EXISTS (SELECT 1 FROM sys.databases AS d (NOLOCK) WHERE d.name = @VAR_DATABASE_NM and d.state_desc = 'ONLINE')\r\nBEGIN\r\n    PRINT '[usp_get_text4sp] - please select a valid database name to get TSQL from.  Database (' + @VAR_DATABASE_NM + ') is invalid.'\r\n    PRINT ' '\r\n    RETURN\r\nEND\r\n<\/pre>\n<\/p>\n
The Step 2 of the algorithm is where a global temp table is defined in tempdb.  I want the tool to be able to have more than one user at a time.  Therefore, we need to dynamically create a table named after the user and the object (s)he is looking for.  Please note, the table will exist in tempdb until the server is restarted.<\/p>\n
Here are the TRANSACTIONAL SQL statements I am going to use for Step 2.<\/p>\n
SUSER_ID()<\/a> – function returns the current user id.
\nSUSER_NAME()<\/a> – function converts the id into a textual windows domain and login user.
\nREPLACE()<\/a> – function switches all occurences of the search string with a new string.
\nISNULL()<\/a> – function uses value one if it is not null, else it uses value two.
\nDECLARE<\/a> – statement is used to declare local variables.
\nSET<\/a> – statement is used to perform assignment with variables.
\nEXISTS()<\/a> – function determines if a result set is not empty.
\nEXECUTE()<\/a> – statement runs dynamic SQL.<\/p>\n
Step 2 can be broken down into two sub-tasks.  If the table exists, (TRUNCATE TABLE<\/a>) clear the data.  If the table does not exist, create the table from scratch (CREATE TABLE<\/a>).  Both tasks use dynamic SQL.<\/p>\n
\r\n--\r\n-- Prepare temp table for user\r\n--\r\n\r\n-- NO COUNTING\r\nSET NOCOUNT ON;\r\n\r\n-- DECLARE LOCAL VARIABLES FOR STEP 1\r\nDECLARE @VAR_TABLE_NM VARCHAR(256);\r\nDECLARE @VAR_TSQL VARCHAR(2048);\r\n\r\n-- GET USER NAME FOR TEMP TABLE\r\nSET @VAR_TABLE_NM = LOWER('[TEMPDB].[DBO].[' + ISNULL(REPLACE(SUSER_SNAME(SUSER_SID()), '\\', '_'), 'DEFAULT2') + '_text4sp]');\r\n\r\n-- TRUNCATE OR CREATE TABLE?  TABLE IS TEMPORARY TO SERVER.\r\nIF  EXISTS (SELECT * FROM TEMPDB.sys.objects AS T1 (NOLOCK) WHERE T1.object_id = OBJECT_ID(@VAR_TABLE_NM) AND type in (N'U'))\r\nBEGIN\r\n    SET @VAR_TSQL = 'TRUNCATE TABLE ' +  @VAR_TABLE_NM;\r\n    EXECUTE (@VAR_TSQL);\r\nEND \r\n\r\nELSE\r\nBEGIN\r\n    SET @VAR_TSQL = 'CREATE TABLE ' +  @VAR_TABLE_NM + \r\n\t' (' +\r\n\t'   [auto_id] int identity(1, 1) not null ' + \r\n\t' , [line_no] int null ' + \r\n\t' , [database_nm] sysname null ' + \r\n\t' , [schema_nm] sysname null ' + \r\n\t' , [object_nm] sysname null ' + \r\n\t' , [sql_txt] text null ' +\r\n\t' ); ';                                \r\n    EXECUTE (@VAR_TSQL);\r\nEND\r\n<\/pre>\n<\/p>\n
Step 3 of the algorithm is where a local temporary table is create to store the fully qualified name of user defined objects for a given database.<\/p>\n
Step 4 of the algorithm is where a cursor is DECLARED<\/a>, OPENED<\/a>, FETCHED NEXT<\/a>, CLOSED<\/a> and DEALLOCATED<\/a>.  <\/p>\n
Cursors<\/a> are a great object for traversing over a small result set for administrative purposes.
\nA infinite WHILE<\/a> loop is used to process the records until the @@FETCH_STATUS<\/a>  variable is non-zero.  A non-zero condition indicates that there is no more data.  The BREAK<\/a> statement is used to exit the loop at that point.<\/p>\n
Step 4 can be broken down into two sub-tasks which process each record in the temporary table created in step 3.  For each record, add the output of the sp_helptext system stored procedure to the user specific, global temp table in tempdb.  Update the output for the current record to reflect database name, schema name, object name and line numbers.<\/p>\n
\r\n--\r\n-- Load temp table with data\r\n--\r\n\r\n-- DECLARE LOCAL VARIABLES FOR STEP 2\r\nDECLARE @VAR_FULL_NM AS SYSNAME;\r\nDECLARE @VAR_OBJECT_NM AS SYSNAME;\r\nDECLARE @VAR_SCHEMA_NM AS SYSNAME;\r\n\r\n-- TABLE TEMPORARY TO PROCESS (SPID)\r\nCREATE TABLE #TEXT4SP\r\n(\r\nFULL_NM sysname,\r\nSCHEMA_NM sysname,\r\nOBJECT_NM sysname\r\n);\r\n\r\n-- DYNAMIC SQL SINCE DB IS NOT SAME ALL THE TIME\r\nSELECT @VAR_TSQL = \r\n' INSERT INTO #TEXT4SP ' +\r\n' SELECT S.NAME + ''.'' + O.name AS FULL_NM, ' +\r\n'     S.NAME AS SCHEMA_NM, ' +\r\n'     O.NAME AS OBJECT_NM ' +\r\n' FROM ' + @VAR_DATABASE_NM + '.sys.objects AS o (NOLOCK) INNER JOIN ' + @VAR_DATABASE_NM + \r\n      '.sys.schemas AS s (NOLOCK) ON o.schema_id = s.schema_id ' +\r\n' WHERE [TYPE] = ''P'' AND is_ms_shipped = 0'\r\nEXEC (@VAR_TSQL);\r\n\r\n-- USE CURSOR TO PROCESS THE RESULTS\r\nDECLARE CUR_OBJS CURSOR FOR\r\nSELECT \r\n    FULL_NM,\r\n    SCHEMA_NM,\r\n    OBJECT_NM\r\nFROM \r\n    #TEXT4SP\r\nORDER BY \r\n    FULL_NM\r\n\r\n-- OPEN THE CURSOR\r\nOPEN CUR_OBJS;\r\n\r\n-- EXAMINE EACH OBJECT AND GET DETAILED INFO\r\nWHILE (1=1) \r\nBEGIN\r\n\r\n-- GRAB A ROW OF INFORMATION\r\nFETCH NEXT FROM CUR_OBJS\r\n    INTO @VAR_FULL_NM, @VAR_SCHEMA_NM, @VAR_OBJECT_NM;\r\n\r\n-- CHECK FOR PROCESSING ERRORS\r\nIF (@@FETCH_STATUS < 0) \r\n    BREAK; \r\n\r\n-- MAKE UP THE SQL STMT & EXECUTE   \r\nSELECT @VAR_TSQL = 'use [' + @VAR_DATABASE_NM + ']; insert into ' + @VAR_TABLE_NM + \r\n    '(sql_txt) exec sp_helptext @objname  = N''' + @VAR_FULL_NM + '''';\r\nEXEC (@VAR_TSQL);\r\n\r\n-- UPDATE TO THE CORRECT DATABASE, SCHEMA & OBJECT NAME AS WELL AS LINE NUMBER\r\nSELECT @VAR_TSQL = 'update ' + @VAR_TABLE_NM + \r\n    ' set database_nm = ''' +  @VAR_DATABASE_NM + \r\n    ''', schema_nm = ''' +  @VAR_SCHEMA_NM + \r\n    ''', object_nm = ''' + @VAR_OBJECT_NM + \r\n    ''', line_no = auto_id + 1 - (select min(auto_id) from ' + @VAR_TABLE_NM + ' where schema_nm is null) ' + \r\n    ' where schema_nm is null;';\r\nEXEC (@VAR_TSQL)\r\n\r\nEND\r\n\r\n-- CLOSE THE CURSOR\r\nCLOSE CUR_OBJS;\r\n\r\n-- RELEASE THE CURSOR\r\nDEALLOCATE CUR_OBJS;\r\n<\/pre>\n<\/p>\n
The last part of the stored procedure is to tell the user where to find the output table in tempdb.  This is done by using the PRINT<\/a> statement to echo the name of the results table.<\/p>\n
\r\n-- CHECK TEMP TABLE FOR RESULTS, MESSAGE ON IF VERBOSE\r\nIF (@VAR_VERBOSE_IND = 1)\r\nBEGIN\r\n    PRINT '[usp_get_text4sp] - please select from table ' + @VAR_TABLE_NM + ' for your output.'\r\n    PRINT ' '\r\nEND\r\n    \r\nEND\r\nGO\r\n<\/pre>\n<\/p>\n
Enclosed<\/a> is the full stored procedure for your usage.<\/p>\n
A sample call to the tool to list and save all stored procedure code in the AdventureWorks database to a user defined table.<\/p>\n
\r\n-- Save info to temporary table\r\nEXEC MSDB.[dbo].[usp_get_text4sp] 'AdventureWorks2008R2'\r\n<\/pre>\n<\/p>\n
We can filter by object name if it is known.  I am looking for the update employee hire info.<\/p>\n
\r\n-- Look for a object\r\nSELECT * FROM [tempdb].[dbo].[sso_a1017012_text4sp] \r\nWHERE object_nm = 'uspUpdateEmployeeHireInfo'\r\n<\/pre>\n<\/p>\n
We can filter by actual code if we know a key value.  I am looking for the hire date.<\/p>\n
\r\n-- Look for a pattern\r\nSELECT * FROM [tempdb].[dbo].[sso_a1017012_text4sp] \r\nWHERE sql_txt LIKE '%HireDate%'\r\n<\/pre>\n<\/p>\n
The image below show the results of this search. <\/p>\n
\"\"<\/a><\/p>\n
Again, the user defined table created by this procedure stays in the system until tempdb is recreated upon server startup.<\/p>\n
The system views named sys.objects<\/a> and sys.schemas<\/a> are key concepts in this solution.  In upcoming articles, I will explain how this query needs to be adjusted for the four different objects that have stored SQL.<\/p>\n","protected":false},"excerpt":{"rendered":"
Today, I am going to start building on some the articles I did in the past. We are going to create tools to pattern search stored SQL statements. Four main objects that contain stored SQL are VIEWS, TRIGGERS, FUNCTIONS and STORED PROCEDURES. Recently at work, I was asked to find all SQL code that had a certain business rule. The task is to return all objects that reference to a certain temperature process named T1. One solution to this problem is to buy a tool like SQL Search from Red…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[338,322,340,335,318,315,63,317,316,333,117,334,224,341,332,108,337,12,321,330,15,336,323,329,324,314,331,319,28,327,328,320,325,326,251,29,339],"class_list":["post-1463","post","type-post","status-publish","format-standard","hentry","category-db-admin","tag-fetch_status","tag-begin","tag-break","tag-close","tag-create-function","tag-create-procedure","tag-create-table","tag-create-trigger","tag-create-view","tag-cursors","tag-database-administrator","tag-deallocate","tag-declare","tag-dynamic-sql","tag-execute","tag-exists","tag-fetch","tag-free-code","tag-if","tag-isnull","tag-john-f-miner-iii","tag-open","tag-print","tag-replace","tag-return","tag-searching-stored-sql","tag-set","tag-sp_helptext","tag-sql-server","tag-suser_id","tag-suser_name","tag-sys-databases","tag-sys-objects","tag-sys-schemas","tag-truncate-table","tag-tsql","tag-while"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/1463","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1463"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/1463\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1463"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1463"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1463"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}