viewdetail

select definition
from sys.objects     o
join sys.sql_modules m on m.object_id = o.object_id
where o.type      = 'V'

select *
FROM   sys.parameters
WHERE  object_id = Object_id('metadata.usp_Common_CreateDestinationTable')
select p.[type]
      ,p.[name]
      ,c.[definition]
  from sys.objects p
  join sys.sql_modules c
    on p.object_id = c.object_id
 where p.[type] = 'P'
   and c.[definition] like '%usp_Common_CreateDestinationTable%'