I worked on a project once where I needed to develop against a local copy of the database. This is not uncommon, especially if I am the only developer on the project. However, this project had three developers all working against our own local copy of the database. We were all off-site and separate from each other, and had to sync up our databases when we met each week. We used a database comparison tool that pointed out to us which of the stored procedures were different. One of us had updated the stored procedure while performing our work, while the other developer may not have touched his copy of the same stored procedure.
So we performed the comparison between my copy of the database and his copy of the database, decided on which stored procedure we wanted to keep, and told the database comparison tool to sync the master database with the selected stored procedure. Here is where we had the problem.
The problem was that the stored procedure we selected wouldn't work because the underlying database structure the stored procedure used had been changed, so the sync failed. We had to first figure out how to re-write the stored procedure to used the updated database structure, made sure it could be saved (compiled), and then go back and try to re-sync the databases objects again.
The underlying problem is that stored procedures can be created and saved (compiled) correctly. But then, days later, you can change the underlying table structure, and SQL gives you no warning (when you make the table structure change) which, or even that, stored procedures that depend on that table structure will no longer work (compile).
We had to come up with a way to easily re-compile all of our stored procedures (over 200) without manually opening up each one and clicking save.
Enter this stored procedure. After searching around on the internet on varios SQL related sites for what seemed like hours, I wasn't able to find anything like this. Maybe there's one out there somewhere, I just didn't have the time to keep looking.
So, I created this stored procedure to re-compile USER stored procedures, views, and functions in the database and tell me which ones didn't compile. Then, I could open only broken ones, fix them and save (compile) them. Then, I'd run this stored procedure again to check that I hadn't broken any others by fixing the reported broken ones. I repeated this procedure until all of the stored procedures, views, and functions in MY copy of the database compiled correctly.
I then had the other developers run the same procedure against their copy of the database. It was amazing to discover how many broken (uncompileable) objects we had between us. No wonder we were having so many problems syncing our databases. Once we ran this procedure on each of our databases, our synchronization only had to sync the database table changes, then select the stored procedure to sync that used the same database structure.
All in all, this procedure we begain enforcing among ourselves prior to any attempt to sync our disconnected databases. It worked like a charm. Hope you can find a use for it too.
/*
=============================================
Author: Gary Janecek
Create date: 5/4/2010
Description: Recompiles ALL User Objects in the database.
The first part of the code inserted the names
of all stored procedures to a table variable,
along with their schema names.
The next part of the code inserted the names
of all views to a table variable,
along with their schema names.
The next part of the code inserted the names
of all user defined functions to a table variable,
along with their schema names.
A table
variable is used just to avoid a CURSOR. The
WHILE loop then reads each object name
and passes it to the system stored procedure:
sp_refreshsqlmodule.
sp_refreshsqlmodule re-compiles the object
and will throw an error if the validation fails.
The CATCH block catches the error if the validation
fails, and displays the error message in the
output window.
NOTE: Due to an unknown condition (so far) this script
will fail on all objects AFTER A FAIL
OCCURS. Fix the first object that failed
and re-run this again to find the next problem.
Repeat until all objects are fixed.
Sample Call: EXEC aDBMaint_ReCompileAllDatabaseObjects
=============================================
*/
CREATE PROCEDURE [dbo].[aDBMaint_ReCompileAllDatabaseObjects]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
-- table variable to database object names.
-- NOTE the IDENTITY FIELD so we don't need a CURSOR
DECLARE @myListOfObjects TABLE (RowID INT IDENTITY(1,1)
, ObjectName sysname
, ObjectType varchar(max))
-- retrieve the list of stored porcedures
INSERT INTO @myListOfObjects(ObjectName, ObjectType)
SELECT
'[' + s.[name] + '].[' + sp.name + ']' AS ObjectName, 'StoredProcedure' AS ObjectType
FROM sys.procedures sp
INNER JOIN sys.schemas s ON s.schema_id = sp.schema_id
WHERE is_ms_shipped = 0
ORDER BY ObjectName
-- retrieve the list of views
INSERT INTO @myListOfObjects(ObjectName, ObjectType)
SELECT
'[' + s.[name] + '].[' + vw.name + ']' AS ObjectName, 'View' AS ObjectType
FROM sys.views vw
INNER JOIN sys.schemas s ON s.schema_id = vw.schema_id
WHERE is_ms_shipped = 0
ORDER BY ObjectName
-- retrieve the list of functions
INSERT INTO @myListOfObjects(ObjectName, ObjectType)
SELECT
'[' + s.[name] + '].[' + func.name + ']' AS FunctionName, 'Function' AS ObjectType
FROM sys.objects func
INNER JOIN sys.schemas s ON s.schema_id = func.schema_id
WHERE is_ms_shipped = 0
AND type_desc LIKE '%FUNCTION%'
ORDER BY FunctionName
-- counter variables
DECLARE @RowNumber INT
DECLARE @TotalRows INT
SELECT @RowNumber = 1
SELECT @TotalRows = COUNT(*) FROM @myListOfObjects
DECLARE @ThisObjectName sysname
-- Start the loop
WHILE @RowNumber < @TotalRows BEGIN
SELECT @ThisObjectName = ObjectName FROM @myListOfObjects WHERE RowID = @RowNumber
SELECT @ThisObjectType = ObjectType FROM @myListOfObjects WHERE RowID = @RowNumber
PRINT N'Refreshing... ' + @ThisObjectType + SPACE(16-LEN(@ThisObjectType)) + ': ' + @ThisObjectName
BEGIN TRY
-- Refresh the Object
EXEC sp_refreshsqlmodule @ThisObjectName
END TRY
BEGIN CATCH
PRINT 'Validation failed for : ' + @ThisObjectName + ', Error:' + ERROR_MESSAGE()
END CATCH
SET @RowNumber = @RowNumber + 1
END
END
No comments:
Post a Comment