-- Listing 1 USE master GO CREATE PROCEDURE dbo.sp_FixTables @DBUltra bit = 0, @DBIntra varchar(8000) = NULL, @DBExtra varchar(8000) = NULL, @PCIntra varchar(100) = NULL, @PCExtra varchar(100) = NULL, @PCAdmin varchar(7000) = NULL AS SET NOCOUNT ON DECLARE @Return int DECLARE @Retain int DECLARE @Status int SET @Status = 0 DECLARE @TPre varchar(10) DECLARE @TDo3 tinyint DECLARE @TDo4 tinyint SET @TPre = '' SET @TDo3 = LEN(@TPre) SET @TDo4 = LEN(@TPre) + 1 DECLARE @Task varchar(8000) DECLARE @Name varchar(100) DECLARE @Same varchar(100) DECLARE @Pick varchar(100) DECLARE @Work varchar(10) DECLARE @Wish varchar(10) IF @PCAdmin IS NULL SET @PCAdmin = 'UPDATE STATISTICS * WITH FULLSCAN EXECUTE sp_recompile ^*^' SET @PCAdmin = REPLACE(@PCAdmin,CHAR(94),CHAR(39)) -- SET @PCAdmin = REPLACE(@PCAdmin,CHAR(96),CHAR(39)) DECLARE Tables CURSOR FAST_FORWARD FOR SELECT O.name, U.name, CONVERT(varchar(10),O.id), SUBSTRING(O.name,@TDo4,100) FROM sysobjects AS O JOIN sysusers AS U ON O.uid = U.uid WHERE ISNULL(OBJECTPROPERTY(O.id,'IsMSShipped'),1) = 0 AND RTRIM(O.type) = 'U' AND LEFT(O.name,@TDo3) = @TPre AND O.name NOT LIKE 'adt%' AND O.name NOT LIKE '%dtproper%' AND O.name NOT LIKE 'dt[_]%' AND (@DBIntra IS NULL OR CHARINDEX('|'+SUBSTRING(O.name,@TDo4,100)+'|','|'+(@DBIntra)+'|') > 0) AND (@DBExtra IS NULL OR CHARINDEX('|'+SUBSTRING(O.name,@TDo4,100)+'|','|'+(@DBExtra)+'|') = 0) AND (@PCIntra IS NULL OR SUBSTRING(O.name,@TDo4,100) LIKE @PCIntra) AND (@PCExtra IS NULL OR SUBSTRING(O.name,@TDo4,100) NOT LIKE @PCExtra) ORDER BY O.name SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain OPEN Tables SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain FETCH NEXT FROM Tables INTO @Name, @Same, @Work, @Pick SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain IF @DBUltra = 0 BEGIN WHILE @@FETCH_STATUS = 0 AND @Status = 0 BEGIN SET @Task = @PCAdmin SET @Task = REPLACE(@Task,CHAR(42),@Name) SET @Task = REPLACE(@Task,CHAR(36),@Same) SET @Task = REPLACE(@Task,CHAR(35),@Work) SET @Task = REPLACE(@Task,CHAR(38),@Pick) PRINT @Task FETCH NEXT FROM Tables INTO @Name, @Same, @Work, @Pick SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain END END ELSE BEGIN WHILE @@FETCH_STATUS = 0 AND @Status = 0 BEGIN SET @Task = @PCAdmin SET @Task = REPLACE(@Task,CHAR(42),@Name) SET @Task = REPLACE(@Task,CHAR(36),@Same) SET @Task = REPLACE(@Task,CHAR(35),@Work) SET @Task = REPLACE(@Task,CHAR(38),@Pick) IF @Status = 0 EXECUTE (@Task) SET @Return = @@ERROR IF @Status = 0 SET @Status = @Return FETCH NEXT FROM Tables INTO @Name, @Same, @Work, @Pick SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain END END CLOSE Tables DEALLOCATE Tables SET NOCOUNT OFF RETURN (@Status) GO -- Listing 2 USE master GO CREATE PROCEDURE dbo.sp_FixObjects @DBUltra bit = 0, @DBIntra varchar(8000) = NULL, @DBExtra varchar(8000) = NULL, @PCIntra varchar(100) = NULL, @PCExtra varchar(100) = NULL, @PCAdmin varchar(7000) = NULL AS SET NOCOUNT ON DECLARE @Return int DECLARE @Retain int DECLARE @Status int SET @Status = 0 DECLARE @Task varchar(8000) DECLARE @Name varchar(100) DECLARE @Same varchar(100) DECLARE @Pick varchar(100) DECLARE @Work varchar(10) DECLARE @Wish varchar(10) IF @PCAdmin IS NULL SET @PCAdmin = 'IF ^$^ <> ^dbo^ EXECUTE sp_changeobjectowner ^$.*^,^dbo^' SET @PCAdmin = REPLACE(@PCAdmin,CHAR(94),CHAR(39)) -- SET @PCAdmin = REPLACE(@PCAdmin,CHAR(96),CHAR(39)) DECLARE DBItems CURSOR FAST_FORWARD FOR SELECT O.name, U.name, CONVERT(varchar(10),O.id), CASE O.type WHEN 'U ' THEN 'TABLE' WHEN 'V ' THEN 'VIEW' WHEN 'P ' THEN 'PROCEDURE' WHEN 'FN' THEN 'FUNCTION' WHEN 'IF' THEN 'FUNCTION' WHEN 'TF' THEN 'FUNCTION' ELSE 'FUNCTION' END FROM sysobjects AS O JOIN sysusers AS U ON O.uid = U.uid WHERE ISNULL(OBJECTPROPERTY(O.id,'IsMSShipped'),1) = 0 AND O.type IN ('U ','V ','P ','FN','IF','TF') AND O.name NOT LIKE 'adt%' AND O.name NOT LIKE '%dtproper%' AND O.name NOT LIKE 'dt[_]%' AND (@DBIntra IS NULL OR CHARINDEX('|'+O.name+'|','|'+(@DBIntra)+'|') > 0) AND (@DBExtra IS NULL OR CHARINDEX('|'+O.name+'|','|'+(@DBExtra)+'|') = 0) AND (@PCIntra IS NULL OR O.name LIKE @PCIntra) AND (@PCExtra IS NULL OR O.name NOT LIKE @PCExtra) ORDER BY O.name SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain OPEN DBItems SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain FETCH NEXT FROM DBItems INTO @Name, @Same, @Work, @Pick SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain IF @DBUltra = 0 BEGIN WHILE @@FETCH_STATUS = 0 AND @Status = 0 BEGIN SET @Task = @PCAdmin SET @Task = REPLACE(@Task,CHAR(42),@Name) SET @Task = REPLACE(@Task,CHAR(36),@Same) SET @Task = REPLACE(@Task,CHAR(35),@Work) SET @Task = REPLACE(@Task,CHAR(38),@Pick) PRINT @Task FETCH NEXT FROM DBItems INTO @Name, @Same, @Work, @Pick SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain END END ELSE BEGIN WHILE @@FETCH_STATUS = 0 AND @Status = 0 BEGIN SET @Task = @PCAdmin SET @Task = REPLACE(@Task,CHAR(42),@Name) SET @Task = REPLACE(@Task,CHAR(36),@Same) SET @Task = REPLACE(@Task,CHAR(35),@Work) SET @Task = REPLACE(@Task,CHAR(38),@Pick) IF @Status = 0 EXECUTE (@Task) SET @Return = @@ERROR IF @Status = 0 SET @Status = @Return FETCH NEXT FROM DBItems INTO @Name, @Same, @Work, @Pick SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain END END CLOSE DBItems DEALLOCATE DBItems SET NOCOUNT OFF RETURN (@Status) GO