SQL Server has quite a few neat tricks up its sleave when it comes to metadata around the database and objects. The information_schema views are incredibly useful in finding relationships between objects, constraints and determing underlying objects used by views, however one large gap still remains: determining, at least at a table object level, dependencies between stored procedure code and the objects called by that code. I started looking into this while a friend and I were working on an ETL metadata project -- we had a sandbox server used by several team members for rapid report development, and one issue that kept cropping up was object dependency between the stored procedure code and the underlying tables and views used. Granted, this would not have been as big an issue if we all documented as we went, but ....
So here's a simple script that can help with that documentation; it is entirely SQL based, not guaranteed to get you 100%, but it has worked for everything I have run it against thus far. The basic output is a result set that lets you know which table or view object is being called by your stored procedures in your database.
Try and let me know how it works for you:
---------------------------------------------------------------
-- Get all of our table objects
---------------------------------------------------------------
if(object_id('tempdb..#TableObjects') is not null)
begin
drop table #TableObjects
end
create table #TableObjects
(
DbObject nvarchar(500) collate Latin1_General_CI_AS_KS_WS
, ObjectType nvarchar(50) collate Latin1_General_CI_AS_KS_WS
)
insert into #TableObjects
select DbObject = o.name
, ObjectType = case
when o.type = 'U' then 'Table'
when o.type = 'V' then 'View'
when o.type = 'P' then 'Stored Procedure'
else 'N/A' end
from sys.objects o
inner join sys.schemas s
on s.schema_id = o.schema_id
where o.type in ('U','V','P')
---------------------------------------------------------------
-- Get all of our procedure objects
---------------------------------------------------------------
if(object_id('tempdb..#ProcedureObjects') is not null)
begin
drop table #ProcedureObjects
end
create table #ProcedureObjects
(
ProcedureObject nvarchar(500) collate Latin1_General_CI_AS_KS_WS
, textpulled bit
)
insert into #ProcedureObjects
select ProcedureObject = s.name + '.' + o.name, textpulled = 0
from sys.objects o
inner join sys.schemas s
on s.schema_id = o.schema_id
where o.type = 'P'
---------------------------------------------------------------
-- Build out the procedure text
---------------------------------------------------------------
if(object_id('tempdb..#ProcedureObjectsAndText') is not null)
begin
drop table #ProcedureObjectsAndText
end
create table #ProcedureObjectsAndText
(
ProcedureName nvarchar(1000) collate Latin1_General_CI_AS_KS_WS
, ProcText nvarchar(4000) collate Latin1_General_CI_AS_KS_WS
)
declare @ProcedureObject nvarchar(1000)
while exists(select 9 from #ProcedureObjects where textpulled = 0)
begin
select top 1 @ProcedureObject = ProcedureObject
from #ProcedureObjects
where textpulled = 0
if(object_id('tempdb..#tmpT') is not null)
begin
drop table #tmpT
end
create table #tmpT (s nvarchar(4000))
insert into #tmpT
exec sp_helptext @ProcedureObject
insert into #ProcedureObjectsAndText
select ProcedureName = @ProcedureObject, s
from #tmpT
update #ProcedureObjects
set textpulled = 1
where @ProcedureObject = ProcedureObject
end
---------------------------------------------------------------
-- Map table object references to procedures
---------------------------------------------------------------
declare @table nvarchar(500), @procText nvarchar(3000), @index int
if(object_id(N'tempdb..#ProcsToObjects') is not null)
begin
drop table #ProcsToObjects
end
select p.ProcedureName, t.DbObject, t.ObjectType, HasTable = charindex(t.DbObject,p.ProcText)
into #ProcsToObjects
from #ProcedureObjectsAndText p
cross join #TableObjects t
where charindex(t.DbObject,p.ProcText) > 0
select * from #ProcsToObjects
Tags:
Categories: Business Intelligence