Inventory a Stored Procedure for all table references?
-
I have a number of very long reporting stored procedures that I would like to inventory to get a master list of which SPs use what tables. Does anyone know of a slick way to do that? I'm not too wild about doing this by hand.
Ideas?
Thanks,
Chris
-
Hi Folk,
I hope, at first, this select helps you:
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%SELECT%'
OR ROUTINE_DEFINITION LIKE '%UPDATE%'
OR ROUTINE_DEFINITION LIKE '%DELETE%'
It is going to research the INFORMATION_SCHEMA.ROUTINES table
and will bring a list of stored procedures created to SELECT, UPDATE or DELETE rows in a TABLE.
Some tables manipulation should be added if you know you work with this kind of code like:
ALTER TABLE, DROP and CREATE
Hopefully waiting your answer in order to see if it helps a little bit.
Best Regards,
Marcos Rosa / marcosfac@gmail.com
-
/* This Stored Procedure display list of tables and immediately followed by depends Stored Procedure */
create proc DisplayTableDependentProc
as
begin
set nocount on
declare @objname as nvarchar(4000)
declare @objname1 as nvarchar(4000)
declare ss cursor for
select name from sysobjects so
where so.xtype='u'
open ss
fetch next from ss into @objname
while @@fetch_status =0
begin
set @objname1 ='sp_depends' + ' ' + @objname
select @objname as
exec sp_executesql @objname1
fetch next from ss into @objname
end
close ss
deallocate ss
end
Please let me know if you have any question/concern.
-
Hi,
/* This stored proc display list of tables and immediately followed by depends Stored Proc */
create proc DisplayTableDependentProc
as
begin
set nocount on
declare @objname as nvarchar(4000)
declare @objname1 as nvarchar(4000)
declare ss cursor for
select name from sysobjects so
where so.xtype='u'
open ss
fetch next from ss into @objname
while @@fetch_status =0
begin
set @objname1 ='sp_depends' + ' ' + @objname
select @objname as
exec sp_executesql @objname1
fetch next from ss into @objname
end
close ss
deallocate ss
end
Thanks & Regards,
G.Sethuraj
City : Sivakasi
State : TamilNadu
Country :India
-
This is quick and dirty with the While statement, but it works and returns a table organized by type, name, schema name, and table name.
-----------------------------------------------------------------------------------------
DECLARE @tblRoutine TABLE (ID INT, routine_name NVARCHAR(255), routine_type NVARCHAR(50))
INSERT @tblRoutine
SELECTrow_number() OVER(ORDER BY routine_name, routine_type ) AS ID,
'SchemaName.' + routine_name AS routine_name,
routine_type
FROMINFORMATION_SCHEMA.ROUTINES
WHEREROUTINE_DEFINITION LIKE '%SchemaName%' --Filter Schema Name
--ANDroutine_type = 'PROCEDURE' --Filter for specific type (FUNCTION, PROCEDURE, etc.)
DECLARE @Count INT, @CountEnd INT, @Routine_name NVARCHAR(255)
DECLARE @tblDependencies TABLE (ID INT, oType INT, oObjName NVARCHAR(255), oOwner NVARCHAR(255), oSequence INT)
SET @Count = 1
SELECT @CountEnd = MAX(ID) FROM @tblRoutine
SELECT @Routine_name = routine_name FROM @tblRoutine WHERE ID = 1
WHILE @Count <= @CountEnd BEGIN
INSERT @tblDependencies (oType, oObjName, oOwner, oSequence)
EXEC sp_MSdependencies @Routine_name, null, 1053183
----Set table PK
UPDATE d
SET ID = @Count
FROM @tblDependencies d
WHERE ID IS NULL
SET @Count = @Count + 1
SELECT @Routine_name = routine_name FROM @tblRoutine WHERE ID = @Count
END
----Final Results
SELECTr.routine_type,
r.routine_name,
oOwner AS SchemaName,
oObjName AS TableName
FROM@tblDependencies d
INNER JOIN @tblRoutine r ON d.ID = r.ID
ORDER BY r.routine_type, r.routine_name, oOwner, oObjName
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply