-- Developer: Victor Shahar - All rights reserved! :-)
declare @dbname sysname
set @dbname = 'Campus'
declare @cmd varchar(max)
set @cmd = 'use ' + @dbname + ';
select distinct d.id, OBJECT_NAME(d.id) as ProcedureName, d.depid, OBJECT_NAME(d.depid) as TableName
, c.name as FieldName, t.name as FieldType, c.scale, c.prec
into #tempColumns
from sys.sysdepends d
inner join sys.sysobjects o1
on d.id = o1.id
inner join sys.sysobjects o2
on d.depid = o2.id
inner join sys.syscolumns c
on d.depid = c.id
inner join sys.systypes t
on c.xtype = t.xtype
where OBJECT_NAME(d.id) not like ''sp_%''
and o1.xtype = ''P''
and o2.xtype = ''U''
select object_name(O.id) As ProcedureName, replace(C.name,''@'','''') as InputParameter
,t.name as FieldType, c.scale, c.prec
into #tempParameters
from sys.sysobjects O
inner join sys.syscolumns C
on C.id = O.id
inner join sys.systypes t
on c.xtype = t.xtype
where O.xtype = ''P''
order by 1
select tc.ProcedureName, tc.TableName,
tc.FieldName, tc.FieldType, tc.scale, tc.prec,
(''@'' + tp.InputParameter) as InputParameter,
tp.FieldType, tp.scale, tp.prec
from #tempColumns tc
inner join #tempParameters tp
on tc.ProcedureName = tp.ProcedureName
and tc.FieldName = tp.InputParameter
where
tc.FieldType != tp.FieldType
order by 1,2,3'
exec (@cmd)
No comments:
Post a Comment