{"id":1486,"date":"2012-01-27T18:32:44","date_gmt":"2012-01-27T18:32:44","guid":{"rendered":"http:\/\/craftydba.com\/?p=1486"},"modified":"2017-10-12T12:13:52","modified_gmt":"2017-10-12T12:13:52","slug":"searching-stored-sql-%e2%80%93-part-2","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=1486","title":{"rendered":"Searching Stored SQL \u2013 Part 2"},"content":{"rendered":"<p>There are four main database objects that contain stored (compiled) SQL: <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms187956.aspx\">VIEWS<\/a>, <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms189799.aspx\">TRIGGERS<\/a>, <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms186755.aspx\">FUNCTIONS<\/a> and <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms187926.aspx\">STORED PROCEDURES<\/a>.<\/p>\n<p>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.<\/p>\n<p>First, I want to examine the dynamic <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms189499.aspx\">SELECT<\/a> statement that retrieves the user defined stored procedure names from a given database. This is the key element of the whole program.<\/p>\n<p>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 <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms176112.aspx\">sp_helptext<\/a> that returns the stored SQL code (text) for any object.<\/p>\n<p>Both the <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms190324.aspx\">sys.objects<\/a> and <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms176011.aspx\">sys.schemas<\/a> 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.<\/p>\n<pre class=\"lang:TSQL theme:familiar mark:1,2-3\" title=\"tsql - search for s.p.\">\r\n-- DECLARE LOCAL VARIABLES\r\nDECLARE @VAR_DATABASE_NM VARCHAR(256) = 'AdventureWorks2008R2';\r\nDECLARE @VAR_TSQL VARCHAR(2048) = '';\r\n\r\n-- DYNAMIC SQL SINCE DB IS NOT SAME ALL THE TIME\r\nSELECT @VAR_TSQL = \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 ' +\r\n@VAR_DATABASE_NM + '.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<\/pre>\n<\/p>\n<p>Second, the <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms188047.aspx\">WHERE<\/a> 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].<\/p>\n<p>The table below shows valid values for the [type] column. I will substitute &#8216;P&#8217; with &#8216;TR&#8217; for this new code. Both the global and local temporary table names are changed to reflect the TRIGGER object type.<\/p>\n<ul>\n<li>AF = Aggregate function (CLR)<\/li>\n<li>C = CHECK constraint<\/li>\n<li>D = DEFAULT (constraint or stand-alone)<\/li>\n<li>F = FOREIGN KEY constraint<\/li>\n<li>FN = SQL scalar function<\/li>\n<li>FS = Assembly (CLR) scalar-function<\/li>\n<li>FT = Assembly (CLR) table-valued function<\/li>\n<li>IF = SQL inline table-valued function<\/li>\n<li>IT = Internal table<\/li>\n<li>P = SQL Stored Procedure<\/li>\n<li>PC = Assembly (CLR) stored-procedure<\/li>\n<li>PG = Plan guide<\/li>\n<li>PK = PRIMARY KEY constraint<\/li>\n<li>R = Rule (old-style, stand-alone)<\/li>\n<li>RF = Replication-filter-procedure<\/li>\n<li>S = System base table<\/li>\n<li>SN = Synonym<\/li>\n<li>SQ = Service queue<\/li>\n<li>TA = Assembly (CLR) DML trigger<\/li>\n<li>TF = SQL table-valued-function<\/li>\n<li>TR = SQL DML trigger<\/li>\n<li>TT = Table type<\/li>\n<li>U = Table (user-defined)<\/li>\n<li>UQ = UNIQUE constraint<\/li>\n<li>V = View<\/li>\n<li>X = Extended stored procedure<\/li>\n<\/ul>\n<p><a href='https:\/\/craftydba.com\/wp-content\/uploads\/2012\/01\/grab-tsql-4-triggers.txt'>Enclosed<\/a> is the full stored procedure for your usage.<\/p>\n<p>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<pre class=\"lang:TSQL theme:familiar mark:1,2-3\" title=\"user defined stored procedure - usp_get_text4tr\">\r\nEXEC MSDB.[dbo].[usp_get_text4tr] 'AdventureWorks2008R2'\r\n<\/pre>\n<\/p>\n<p>We can filter by object name if it is known. I am looking for the delete vendor trigger.<\/p>\n<pre class=\"lang:TSQL theme:familiar mark:1,2-3\" title=\"search results for named object\">\r\n-- Look for a object\r\nSELECT * FROM [tempdb].[dbo].[sso_a1017012_text4tr] \r\nWHERE object_nm = 'dVendor'\r\n<\/pre>\n<\/p>\n<p>We can filter by actual code if we know a key value. I am looking for the product id.<\/p>\n<pre class=\"lang:TSQL theme:familiar mark:1,2-3\" title=\"search code for pattern\">\r\n-- Look for a pattern\r\nSELECT * FROM [tempdb].[dbo].[sso_a1017012_text4tr] \r\nWHERE sql_txt LIKE '%ProductId%'\r\n<\/pre>\n<\/p>\n<p>The image below show the results of this search.<\/p>\n<p><a href=\"https:\/\/craftydba.com\/wp-content\/uploads\/2012\/01\/usp_get_text4tr.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/craftydba.com\/wp-content\/uploads\/2012\/01\/usp_get_text4tr.jpg\" alt=\"\" title=\"usp_get_text4tr\" width=\"907\" height=\"573\" class=\"aligncenter size-full wp-image-1496\" srcset=\"https:\/\/craftydba.com\/wp-content\/uploads\/2012\/01\/usp_get_text4tr.jpg 907w, https:\/\/craftydba.com\/wp-content\/uploads\/2012\/01\/usp_get_text4tr-300x189.jpg 300w\" sizes=\"auto, (max-width: 907px) 100vw, 907px\" \/><\/a><\/p>\n<p>Again, the user defined table created by this procedure stays in the system until tempdb is recreated upon server startup.<\/p>\n<p>Next time, I will show how this query needs to be adjusted to display SQL code for FUNCTIONS.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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&hellip;<\/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-1486","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\/1486","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=1486"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/1486\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1486"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1486"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1486"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}