Pretraga procedura, viewova i funkcija bez komentara
Objavljeno: 29-12-2014 | Autor: Nenad Živković | Kategorija: Skriptovi
1
Preuzmi: SQLKefalo_PretragaBezKomentara.sql
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 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 |
-- **************************************************************** -- www.sql-kefalo.net -- **************************************************************** -- Autor: Nenad Zivkovic -- Datum: 28.12.2014 -- Opis: Pretraga procedura, viewova, funkcija i trigera izuzimajuci tekst koji se nalazi pod komentarima -- Za pokretanje procedure potrebno je imati udfKefalo_Splitter_While funkciju u istoj bazi -- Fja je dostupna sa linka: http://www.sql-kefalo.net/2014/11/funkcija-za-splitovanje-stringova-while/ -- Clanak: http://www.sql-kefalo.net/2014/12/Pretraga-procedura-viewova-i-funkcija-bez-komentara -- **************************************************************** CREATE PROCEDURE uspKefalo_PretragaBezKomentara ( @PojamZaPretragu NVARCHAR(4000) , @Baza sysname = NULL ) AS BEGIN --DECLARE @PojamZaPretragu NVARCHAR(4000); --SET @PojamZaPretragu = '2014'; --DECLARE @Baza sysname = 'SQL_KEFALO'; DECLARE @dbid INT; IF (@Baza IS NULL) BEGIN SET @Baza = ''; SET @dbid = DB_ID(); END ELSE BEGIN SET @dbid = DB_ID(@Baza); SET @Baza = '[' + @Baza + ']'; SET @Baza = @Baza + '.'; END IF (OBJECT_ID('tempdb..#tmp1') IS NOT NULL) DROP TABLE #tmp1; IF (OBJECT_ID('tempdb..#tmp5') IS NOT NULL) DROP TABLE #tmp5; CREATE TABLE #tmp1 (object_id INT, type CHAR(2), type_desc NVARCHAR(60), RedniBroj INT, Element NVARCHAR(MAX)); DECLARE @sql NVARCHAR(MAX); SET @sql = 'INSERT INTO #tmp1 SELECT sm.object_id, o.type, o.type_desc, spl.RedniBroj, spl.Element' SET @sql = @sql + ' FROM ' + @Baza + 'sys.sql_modules sm'; SET @sql = @sql + ' INNER JOIN ' + @Baza + 'sys.objects o ON sm.object_id = o.object_id' SET @sql = @sql + ' CROSS APPLY dbo.udfKefalo_Splitter_While(sm.definition, CHAR(13)+CHAR(10)) spl' SET @sql = @sql + ' WHERE sm.definition LIKE ''%' + @PojamZaPretragu + '%'''; EXEC sys.sp_executesql @sql; WITH cte2 AS ( SELECT #tmp1.object_id , #tmp1.RedniBroj , REPLACE (REPLACE (Element, '/*', CHAR(13)+CHAR(10)+'/*'+CHAR(13)+CHAR(10)), '*/', CHAR(13)+CHAR(10) + '*/' + CHAR(13)+CHAR(10)) AS Element FROM #tmp1 ) , cte3 AS ( SELECT cte2.object_id , cte2.RedniBroj , CASE WHEN CHARINDEX('--', Element) > 0 THEN LEFT(Element, CHARINDEX('--', Element)-1) ELSE Element END AS Element FROM cte2 ) , cte4 AS ( SELECT c3.object_id, c3.RedniBroj AS RedniBroj0, dsk.* FROM cte3 c3 CROSS APPLY dbo.udfKefalo_Splitter_While(c3.Element, CHAR(13)+CHAR(10)) dsk ) SELECT *, ROW_NUMBER() OVER (PARTITION BY object_id ORDER BY RedniBroj0, RedniBroj) RN INTO #tmp5 FROM cte4; WITH cte6 AS ( SELECT object_id , RedniBroj0 , RedniBroj , RN , CASE WHEN Element LIKE '%/*%' THEN LEFT(Element, CHARINDEX('/*', Element)-1) ELSE Element END AS Element , CASE WHEN Element LIKE '%/*%' THEN 'Start' ELSE '' END AS Cmnt FROM #tmp5 WHERE RN = 1 UNION ALL SELECT c5.object_id , c5.RedniBroj0 , c5.RedniBroj , c5.RN , CASE WHEN c5.Element LIKE '%/*%' THEN LEFT(c5.Element, CHARINDEX('/*', c5.Element)-1) WHEN c5.Element LIKE '%*/%' THEN SUBSTRING(c5.Element, CHARINDEX('*/', c5.Element)+2, 4000) ELSE c5.Element END AS Element , CASE WHEN c5.Element LIKE '%/*%' THEN 'Start' WHEN c5.Element LIKE '%*/%' THEN 'End' WHEN c6.Cmnt IN ('Start','X') THEN 'X' ELSE '' END FROM cte6 c6 INNER JOIN #tmp5 c5 ON c6.object_id = c5.object_id AND c6.RN+1 = c5.RN ) SELECT DB_NAME(@dbid) Baza , OBJECT_NAME(c6.OBJECT_ID, @dbid) AS ObjectName , c6.object_id , t1.type AS Tip , t1.type_desc AS TipOpis , c6.RedniBroj0 AS BrojReda , t1.Element AS Red FROM cte6 c6 LEFT JOIN #tmp1 t1 ON t1.object_id = c6.object_id AND t1.RedniBroj = c6.RedniBroj0 WHERE Cmnt <> 'X' AND LTRIM(c6.Element) <> '' AND c6.Element LIKE '%' + @PojamZaPretragu +'%' ORDER BY ObjectName, c6.RedniBroj OPTION (MAXRECURSION 0); END |
[…] Da bismo prevazišli problem, mi smo vam pripremili jedan upit – proceduru koja radi pretragu, ali pre toga uklanja sve komentare i pretražuje samo kod. Možete je preuzeti sa sledećeg linka: Procedura uspKefalo_PretragaBezKomentara […]