May 14, 2004 at 6:37 pm
Hi team,
I got stuck with, a whole other persons workload for probably the next 3 weeks. He left without any scripts, docs, etc.. for any of the projects. Apparently his developers didn't keep many records..
I haven't had to support developers in this way in a long time, and am a bit rusty.. I am so busy, that I am not sure when I will have the time to sit down and think about it..
so does anyone have any sql code they would like to share?
I need to supply a column name, and see what tables contain that column, and also what SP's reference that column. Latly I need to find how many sp's reference another sp.
I am going to have to sweep 50+ db's since they don't know where the columns, and sp's may be used..
Any help is appreciated!
Thanks
John
May 15, 2004 at 8:59 am
----------------------------------------------------------------------
--exact search (faster)
----------------------------------------------------------------------
set nocount on
declare @col sysname
set @col = 'iConfidential'
--find out which tables/views have column
select so.type as ObjectType,
so.name as ObjectName
from dbo.sysobjects so
join dbo.syscolumns sc
on sc.id = so.id
where so.type in ('U', 'V')
and sc.name = @col
order by 1,2
--find out which procs/triggers/fns reference column
--this is loose & not 100% but close & simple
--it will miss references that cross from one text row to next
select so.type as ObjectType,
so.name as ObjectName,
count(sct.text) as NbrReferences
from dbo.sysobjects so
join dbo.syscomments sct
on sct.id = so.id
and sct.text like '%' + @col + '%'
where so.type in ('P', 'TR', 'FN')
group by so.type, so.name
order by 1,2
go
----------------------------------------------------------------------
--extend exact search to all DBs
----------------------------------------------------------------------
set nocount on
declare @col sysname, @sql varchar(8000)
set @col = 'iConfidential'
set @sql =
'select ''?'' as DBName,
so.type as ObjectType,
so.name as ObjectName,
sc.name as ColumnName
from ?.dbo.sysobjects so
join ?.dbo.syscolumns sc
on sc.id = so.id
where so.type in (''U'', ''V'')
and sc.name = ''' + @col + '''
order by 1,2,3
select ''?'' as DBName,
so.type as ObjectType,
so.name as ObjectName,
count(sct.text) as NbrReferences
from ?.dbo.sysobjects so
join ?.dbo.syscomments sct
on sct.id = so.id
and sct.text like ''%' + @col + '%''
where so.type in (''P'', ''TR'', ''FN'')
group by so.type, so.name
order by 1,2
'
exec sp_MSForEachDB @sql, '?'
go
----------------------------------------------------------------------
--wildcard search (slower)
----------------------------------------------------------------------
set nocount on
declare @col sysname
set @col = 'iConfidential'
--find out which tables/views have column
select so.type as ObjectType,
so.name as ObjectName,
sc.name as ColumnName
from dbo.sysobjects so
join dbo.syscolumns sc
on sc.id = so.id
where so.type in ('U', 'V')
and sc.name like @col
order by 1,2,3
--find out which procs/triggers/fns reference column
--this is loose & not 100% but close & simple
--it will miss references that cross from one text row to next
select so.type as ObjectType,
so.name as ObjectName,
d.name as ColumnName,
count(sct.text) as NbrReferences
from dbo.sysobjects so
cross join (select distinct sc.name
from dbo.sysobjects so
join dbo.syscolumns sc
on sc.id = so.id
where so.type in ('U', 'V')
and sc.name like @col) d
join dbo.syscomments sct
on sct.id = so.id
and sct.text like '%' + rtrim(d.name) + '%'
where so.type in ('P', 'TR', 'FN')
group by so.type, so.name, d.name
order by 1,2,3
go
--this can be extended to all DBs in similar fashion
May 21, 2004 at 4:46 pm
Mike,
Thanks so much!! This was a lifesaver.. I do have one other question.. the developers have apparently used the name enddate is numerous tables.. is there a way to search for only SP that reference this particular table, and column?
Thanks again!!
Have a great weekend!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply