SQL Kefalo » Blog Archive » Pretraga procedura, viewova i funkcija bez komentara

Pretraga procedura, viewova i funkcija bez komentara

Objavljeno: 29-12-2014 | Autor: Nenad Živković | Kategorija: Skriptovi

1

Preuzmi: SQLKefalo_PretragaBezKomentara.sql

-- ****************************************************************
-- 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

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

)

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 ,

, 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

Pretraga procedura, viewova i funkcija bez komentara

Objavljeno: 29-12-2014 | Autor: Nenad Živković | Kategorija: Skriptovi

1

Komentari (1)

Napišite komentar

Prijava na mejling listu

Najnoviji članci

SQL Kefalo » Blog Archive » Pretraga procedura, viewova i funkcija bez komentara 29-12-2014
	[…] 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 […] Одговори