Listare UDF folosite in proceduri stocate

Situatie

Am facut o procedura ce returneaza toate procedurile ce contin functii definite de catre utilizatori.

Procedura returneaza urmatoarele coloane:

-numele procedurii

-numele functiei pe care procedura respectiva o foloseste

De asemenea, procedura arata si cate functii definite de utilizator foloseste fiecare procedura returnata.

Solutie

create procedure BD_UdfInProcedures_prc

CREATE TABLE #TempFunctions(
ID INT IDENTITY(1,1),
fnName VARCHAR(256)
);

CREATE TABLE #spWithFn (
ID INT IDENTITY(1,1),
spName VARCHAR(256),
fnName VARCHAR(256)
);

INSERT #TempFunctions(fnName)
SELECT name
FROM sys.objects WHERE TYPE IN (‘FN’,’IF’,’TF’)

DECLARE @fnName VARCHAR(256);
DECLARE @SQLCmd VARCHAR(512);

WHILE ((SELECT COUNT(1) FROM #TempFunctions) > 0)
BEGIN

SELECT TOP 1 @fnName = fnName FROM #TempFunctions ORDER BY fnName

SET @SQLCmd =
‘INSERT #spWithFn(spName,fnName)
SELECT Name, ”’ + @fnName + ”’ FROM sys.procedures WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE ”%’ + @fnName + ‘%”’

EXEC(@SQLCmd);

DELETE #TempFunctions
WHERE fnName = @fnName;

END;

SELECT spName,COUNT(1) NumberUDFs
FROM #spWithFn
GROUP BY spName
ORDER BY COUNT(1) DESC;

SELECT * FROM #spWithFn ORDER BY spName;

DROP TABLE #TempFunctions;
DROP TABLE #spWithFn;

Tip solutie

Permanent

Follow Us