June 26, 2019 at 7:31 am
Good Day,
I need advice and solution how to get Get list Stored procedure dependency from root stored procedure
because this is legacy database without FK so i can not identify relation for each table without FK.
any advice or solution are welcome.
thanks for advance.
June 26, 2019 at 9:39 am
you can use the following:
But take into account that there's a deferred name resolution and the objects referenced within a stored procedure do not have to exist at the time the procedure is created.
SQL Server creates the stored procedure and the references to missing objects are validated when the stored procedure is executed.
No error or warning message is issued about the missing objects when the stored procedure is created.
The only exception is when a stored procedure references another stored procedure that doesn’t exist.
In that case, a warning message is issued, but the stored procedure is still created.
June 26, 2019 at 6:33 pm
You can try something dumb like I did, old views and all, like this:
DECLARE @schema varchar(150),@procedure varchar(150),@innerSchema varchar(150), @innerProc varchar(150),@counter int,@maxRows int
SET @schema = 'MMC\something'
SET @procedure = 'SP_name'
SET @innerSchema = @schema
SET @innerProc = 'SP_otherName'
IF object_id('tempdb..#SP_FindDependency') IS NOT NULL BEGIN DROP TABLE #SP_FindDependency END
IF object_id('tempdb..#SP_FindDependency_Results') IS NOT NULL BEGIN DROP TABLE #SP_FindDependency_Results END
CREATE TABLE #SP_FindDependency (
iRow int identity(1,1),
[childSchemaID] int,
[childSchema] varchar(150),
[childNameID] int,
[childName] varchar(150)
)
CREATE TABLE #SP_FindDependency_Results (
iRow int identity(1,1),
[childSchemaID] int,
[childSchema] varchar(150),
[childNameID] int,
[childName] varchar(150),
--[childText] varchar(max),
[parentSchemaID] int,
[parentSchema] varchar(150),
[parentNameID] int,
[parentName] varchar(150)
)
INSERT INTO #SP_FindDependency ([childSchemaID],[childSchema],[childNameID],[childName])
SELECT DISTINCT ss.schema_id,ss.name,
sao.object_ID,sao.Name
FROM
sys.syscomments sc--TODO: if I comment this part out to get the tables (non function/proc/views) I get zilch in the second table, why?
JOIN
sys.all_objects sao
on sc.id=sao.object_id
JOIN sys.schemas ss ON sao.schema_id = ss.schema_id
WHERE
sao.is_ms_shipped <> 1
AND
ss.name IN (--production schemas only
'dbo',
'unittest'
)
;
SELECT * FROM #SP_FindDependency
--where childName = 'serverreference'
--ORDER BY iRow;
SELECT @maxRows = max(iRow) FROM #SP_FindDependency;
SET @counter = 1;
WHILE @counter <= @maxRows
BEGIN
--SELECT *
----@innerSchema = [childSchema],
----@innerProc = [childName]
--FROM #SP_FindDependency
--WHERE --childName = 'serverreference'
--iRow = @counter;
INSERT INTO #SP_FindDependency_Results ([childSchemaID],[childSchema],[childNameID],[childName],[parentSchemaID],[parentSchema],[parentNameID],[parentName])
SELECT DISTINCT
--@innerSchema, @innerProc
childTable.[childSchemaID],
childTable.[childSchema],
childTable.[childNameID],
childTable.[childName]
--,ss.name,sao.Name
,parentTable.[childSchemaID],parentTable.[childSchema],parentTable.[childNameID],parentTable.[childName]
FROM #SP_FindDependency childTable
JOIN sys.syscomments sc ON sc.ID=childTable.childNameID --get the code
CROSS JOIN #SP_FindDependency parentTable
--LEFT JOIN sys.all_objects sao on sourceTable.=sao.object_id
--JOIN sys.schemas ss ON sao.schema_id = ss.schema_id
WHERE childTable.iRow=@counter
AND parentTable.iRow <> @counter
--NOT(ss.name = @innerschema AND sao.name = @innerproc)
--AND
--sao.is_ms_shipped <> 1
AND
--sc.text LIKE '%' + parentTable.[childName] + '%'--@innerProc + '%';
(--have to use the escape character to make it recognize [ and ] as valid
sc.text LIKE '%' + parentTable.[childSchema] + '.' + parentTable.[childName] + '%'
OR
sc.text LIKE '%\[' + parentTable.[childSchema] + '\].' + parentTable.[childName] + '%' ESCAPE '\'
OR
sc.text LIKE '%\[' + parentTable.[childSchema] + '\].\[' + parentTable.[childName] + '\]%' ESCAPE '\'
);
SET @counter = @counter + 1;
END
INSERT INTO unittest.all_mhi_dependency ([schema_id],[object_id],[parent_schema_id],[parent_object_id])
SELECT childSchemaID,childNameID,parentSchemaID,parentNameID
FROM #SP_FindDependency_Results
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply