Finding other database objects referenced in an SP

,

Script can be used to find out the objects which are available in other database and are referenced in your stored procedure. These objects will not be listed with sp_depends system stored procedure.

DECLARE @RCount INT
DECLARE @i 	INT
DECLARE @idx 	INT
DECLARE @spstr	NVARCHAR(4000)
DECLARE @splen  INT

CREATE TABLE #tmp_sp (rowid INT IDENTITY(1,1), sp_txt NVARCHAR(4000))
CREATE TABLE #prod(tbname VARCHAR(100))

INSERT INTO #tmp_sp
(sp_txt)
SELECT text 
FROM SYSCOMMENTS
WHERE id IN
(
	SELECT id 
	FROM sysobjects 
	WHERE name IN
	(
--Please Replace the stored procedure names here..
'stp_sample1' ,
'stp_sample2' 
	)
)

SET @RCount = @@ROWCOUNT
SET @i = 1

WHILE @i <= @RCount
BEGIN

SELECT @spstr = sp_txt 
FROM #tmp_sp
WHERE rowid = @i

SET @splen = LEN(@spstr)
SET @idx = 1

	WHILE @idx < @splen AND @idx <> -1
	BEGIN
--Please replace 'dbname.user' with required dbname and user eg 'master.dbo.'
		SET @idx = CHARINDEX('dbname.user.', @spstr, @idx)
		IF @idx = 0 
		BEGIN
			SET @idx = -1
		END
		ELSE
		BEGIN
			INSERT INTO #prod
			SELECT SUBSTRING(@spstr, @idx, 60)
			SET @idx = @idx + 1
		END
	END
SET @i = @i + 1
END

UPDATE #prod
SET tbname = REPLACE(tbname, CHAR(13), '|')

UPDATE #prod
SET tbname = REPLACE(tbname, CHAR(9), '|')

UPDATE #prod
SET tbname = REPLACE(tbname, CHAR(10), '|')

UPDATE #prod
SET tbname = REPLACE(tbname, ' ', '|')

UPDATE #prod
SET tbname = REPLACE(tbname, ',', '|')

UPDATE #prod
SET tbname = REPLACE(tbname, '(', '|')

SELECT DISTINCT LOWER(LEFT(LTRIM(tbname),PATINDEX('%|%',LTRIM(tbname))-1)) tbname FROM #prod
DROP TABLE #tmp_sp
DROP TABLE #prod

Rate

Share

Share

Rate