January 9, 2012 at 2:33 am
Hi everyone,
In my development database I have some 1000+ stores procedures. Out of which 100-200 procedures do not work as the tables or columns in-lined in these procedures have undergone changes.
Can anyone help me in finding out the list at once.
Thanks in advance.
January 9, 2012 at 3:11 am
There is nice script in below link.
January 9, 2012 at 3:11 am
You should be able to run sp_depends for all procedures and then query the information schema from these results to find tables and columns that no longer exist.
Rob
January 9, 2012 at 4:48 am
Its not working as desired.
January 9, 2012 at 6:07 am
This will recompile the code in your database and alert you to coding errors. Note that it will not detect errors in dynamic sql, and neither will it detect errors due to a dependent object being deleted, because SQL Server will happily compile such code in the expectation that the object will be there when it is called.
Requires SQL Server version 2005 or better. Run it on a restored copy of your database rather than your production copy!
------------------------------------------------------------------------------------------------------------------------
DESCRIPTION:Recompiles all the code objects in a database.
------------------------------------------------------------------------------------------------------------------------
REVISION HISTORY:
EDITORDATEREVISIONS
blindman08/2007Script created.
blindman11/19/2008Modified to handle different schemas.
------------------------------------------------------------------------------------------------------------------------
*/
begin
set nocount on
create table #SQLStrings (StringID bigint identity(1, 1), SQLString varchar(max))
create table #CompileErrors (ErrorMessage varchar(4000))
declare@SQLString varchar(max)
declare@ObjectName varchar(500)
declare@ObjectType varchar(10)
declare CodeObjectList cursor for
select[schemas].[name] + '.' + [objects].[name], [objects].[type]
fromsys.objects objects
inner join sys.schemas schemas on objects.schema_id = schemas.schema_id
wheretype in ('P', 'TR', 'V', 'TF', 'FN', 'IF')
and objects.name <> 'RecompileSQLCode'
order by parent_object_id
open CodeObjectList
fetch next from CodeObjectList into @ObjectName, @ObjectType
while @@fetch_status = 0
begin
truncate table #SQLStrings
insert into #SQLStrings exec sp_helptext @ObjectName
set @SQLString = ''
select @SQLString = @SQLString + ltrim(SQLString) from #SQLStrings order by StringID
set@SQLString = left(@SQLString, charindex('CREATE ', @SQLString)-1) + 'ALTER ' + right(@SQLString, len(@SQLString)-charindex('CREATE ', @SQLString) -6)
begin try
exec (@SQLString)
end try
begin catch
insert into #CompileErrors (ErrorMessage) values (@ObjectName + ' (' + rtrim(@ObjectType) + '): ' + error_message())
end catch
fetch next from CodeObjectList into @ObjectName, @ObjectType
end
close CodeObjectList
deallocate CodeObjectList
select * from #CompileErrors
drop table #SQLStrings
drop table #CompileErrors
end
January 9, 2012 at 8:29 am
There is also a nice 3rd party tool from RedGate (SQL Search, I believe) that can help you with this.
Thanks...Chris
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply