Bring out your dead!
Just a quick post to share the script. At my current client, they have a large codebase that's not been cared for. I give them credit for generally avoiding dynamic SQL but the proliferation of autogenerated code has resulted in thousands, not an exaggeration, of objects that were generated and never used. I had run down fixing a dependency chain only to learn the rootmost object was broken as the day is long.
There has to be a better way of figuring this out. SQL Server Data Tools you say? Yeah, it took 2 days to reverse engineer the database and validate the dependencies only to run out of memory. I settled on sys.sp_refreshsqlmodule to at least give me a fighting chance of finding out what's broken in the database. The following query generates a list of all non-schemabound objects and then calls sys.sp_refreshmodules against the objects. if it raises an error, I drop it into a table so I can inspect it later as well as print out an error message. Quick and dirty, there's probably some logic issues in there, things that aren't dead yet, but this at least helps me triage the database.
-- This script attempts to generate a report on whether all the SQL modules are valid -- in a database. This can generate false positives as there is no intelligence -- built into the order to ensure object dependencies are followed. SET NOCOUNT ON; DECLARE @SchemaName sysname , @ObjectName sysname , @QualifiedObjectName sysname , @QueryTemplate nvarchar(1000) = N'EXECUTE sys.sp_refreshsqlmodule @name;' , @ParamList nvarchar(20) = N'@Name sysname'; DECLARE ObjectCursor CURSOR READ_ONLY FOR SELECT ALL SCHEMA_NAME(S.schema_id) AS SchemaName , S.name AS ObjectName FROM sys.objects AS S INNER JOIN ( -- schema bound objects can't get recompiled SELECT SM.object_id FROM sys.sql_modules AS SM WHERE SM.is_schema_bound = 0 ) AS SM ON SM.object_id = S.object_id WHERE S.is_ms_shipped = 0 -- adjust this list as needed AND S.type in ('FN', 'IF', 'IT', 'P', 'TF', 'TR', 'V') ORDER BY S.schema_id ; DECLARE @ManualCleanup TABLE ( SchemaName sysname , ObjectName sysname , SQLStatement nvarchar(1000) NOT NULL ); OPEN ObjectCursor FETCH NEXT FROM ObjectCursor INTO @SchemaName , @ObjectName; WHILE (@@fetch_status > -1) BEGIN SET @QualifiedObjectName = QUOTENAME(@schemaName) + '.' + QUOTENAME(@ObjectName); BEGIN TRY EXECUTE dbo.sp_executesql @queryTemplate, @ParamList, @Name = @QualifiedObjectName; END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; INSERT INTO @ManualCleanup ( SchemaName , ObjectName , SQLStatement ) SELECT @SchemaName , @ObjectName , 'EXECUTE sys.sp_refreshsqlmodule @name = N''' + @QualifiedObjectName + ''';'; END CATCH FETCH NEXT FROM ObjectCursor INTO @SchemaName , @ObjectName; END CLOSE ObjectCursor DEALLOCATE ObjectCursor SELECT MC.* FROM @ManualCleanup AS MC;