RE: how to maintain test environment

SSC Guru

Points: 323506

December 5, 2013 at 11:49 am

here's an adapted example of some code i use to check for invalid objects; this will recompile your procs/functions/views to allow you to point to a different database.

at the end, anything that didn't compile correctly(invalid referneces, drop tables/columns, etc) will appear .

DECLARE @BadObjects TABLE (ALLINVALIDOBJECTS nvarchar(4000))

DECLARE @objname NVARCHAR(4000),

@cmd NVARCHAR(max),

@OldDatabase1 varchar(128),

@NewDatabase1 varchar(128),

@OldDatabase2 varchar(128),

@NewDatabase2 varchar(128)

SET @OldDatabase1 = 'PRODUCTION'

SET @NewDatabase1 = 'TESTPROD'

SET @OldDatabase2 = 'CommonSecurity'

SET @NewDatabase2 = 'TESTSecurity'

--#################################################################################################

--Views

--#################################################################################################

DECLARE c1 CURSOR FOR

SELECT

QUOTENAME(s.name) + '.' + QUOTENAME(obs.name) ,

mods.definition

FROM sys.objects obs

INNER JOIN sys.sql_modules mods ON obs.object_id = mods.object_id

INNER JOIN sys.schemas s ON obs.schema_id = s.schema_id

WHERE obs.is_ms_shipped = 0

AND obs.type_desc IN('VIEW')

OPEN c1

FETCH NEXT FROM c1 INTO @objname,@cmd

WHILE @@fetch_status <> -1

BEGIN

BEGIN TRANSACTION

BEGIN TRY

SET @cmd = REPLACE(@cmd,convert(varchar(max),N'CREATE VIEW'),convert(varchar(max),N'ALTER VIEW'))

--modify three part naming conventions to point to the test database

SET @cmd = REPLACE(@cmd,convert(varchar(max),@OldDatabase1 +'.'),convert(varchar(max),@NewDatabase1 +'.'))

SET @cmd = REPLACE(@cmd,convert(varchar(max),@OldDatabase2 +'.'),convert(varchar(max),@NewDatabase2 +'.'))

SET @cmd = REPLACE(@cmd,convert(varchar(max),N'[' + @OldDatabase1 + '].'),convert(varchar(max),N'[' + @NewDatabase1 + '.'))

SET @cmd = REPLACE(@cmd,convert(varchar(max),N'[' + @OldDatabase2 + '].'),convert(varchar(max),N'[' + @NewDatabase2 + '].'))

print @cmd

exec (@cmd)

COMMIT TRAN

END TRY

BEGIN CATCH

ROLLBACK TRANSACTION

INSERT @BadObjects VALUES (@objname)

END CATCH

FETCH NEXT FROM c1 into @objname,@cmd

END --WHILE

CLOSE c1

DEALLOCATE c1

--#################################################################################################

--Procs

--#################################################################################################

DECLARE c1 CURSOR FOR

SELECT

QUOTENAME(s.name) + '.' + QUOTENAME(obs.name) ,

mods.definition

FROM sys.objects obs

INNER JOIN sys.sql_modules mods ON obs.object_id = mods.object_id

INNER JOIN sys.schemas s ON obs.schema_id = s.schema_id

WHERE obs.is_ms_shipped = 0

AND obs.type_desc IN('SQL_STORED_PROCEDURE')

OPEN c1

FETCH NEXT FROM c1 INTO @objname,@cmd

WHILE @@fetch_status <> -1

BEGIN

BEGIN TRANSACTION

BEGIN TRY

SET @cmd = REPLACE(@cmd,convert(varchar(max),N'CREATE PROCEDURE'),convert(varchar(max),N'ALTER PROCEDURE'))

--modify three part naming conventions to point to the test database

SET @cmd = REPLACE(@cmd,convert(varchar(max),@OldDatabase1 +'.'),convert(varchar(max),@NewDatabase1 +'.'))

SET @cmd = REPLACE(@cmd,convert(varchar(max),@OldDatabase2 +'.'),convert(varchar(max),@NewDatabase2 +'.'))

SET @cmd = REPLACE(@cmd,convert(varchar(max),N'[' + @OldDatabase1 + '].'),convert(varchar(max),N'[' + @NewDatabase1 + '.'))

SET @cmd = REPLACE(@cmd,convert(varchar(max),N'[' + @OldDatabase2 + '].'),convert(varchar(max),N'[' + @NewDatabase2 + '].'))

print @cmd

exec (@cmd)

COMMIT TRAN

END TRY

BEGIN CATCH

ROLLBACK TRANSACTION

INSERT @BadObjects VALUES (@objname)

END CATCH

FETCH NEXT FROM c1 into @objname,@cmd

END --WHILE

CLOSE c1

DEALLOCATE c1

--#################################################################################################

--Functions

--#################################################################################################

DECLARE c1 CURSOR FOR

SELECT

QUOTENAME(s.name) + '.' + QUOTENAME(obs.name) ,

mods.definition

FROM sys.objects obs

INNER JOIN sys.sql_modules mods ON obs.object_id = mods.object_id

INNER JOIN sys.schemas s ON obs.schema_id = s.schema_id

WHERE obs.is_ms_shipped = 0

AND obs.type_desc IN('AGGREGATE_FUNCTION','SQL_INLINE_TABLE_VALUED_FUNCTION','SQL_TABLE_VALUED_FUNCTION','SQL_SCALAR_FUNCTION')

OPEN c1

FETCH NEXT FROM c1 INTO @objname,@cmd

WHILE @@fetch_status <> -1

BEGIN

BEGIN TRANSACTION

BEGIN TRY

SET @cmd = REPLACE(@cmd,convert(varchar(max),N'CREATE FUNCTION'),convert(varchar(max),N'ALTER FUNCTION'))

--modify three part naming conventions to point to the test database

SET @cmd = REPLACE(@cmd,convert(varchar(max),@OldDatabase1 +'.'),convert(varchar(max),@NewDatabase1 +'.'))

SET @cmd = REPLACE(@cmd,convert(varchar(max),@OldDatabase2 +'.'),convert(varchar(max),@NewDatabase2 +'.'))

SET @cmd = REPLACE(@cmd,convert(varchar(max),N'[' + @OldDatabase1 + '].'),convert(varchar(max),N'[' + @NewDatabase1 + '.'))

SET @cmd = REPLACE(@cmd,convert(varchar(max),N'[' + @OldDatabase2 + '].'),convert(varchar(max),N'[' + @NewDatabase2 + '].'))

print @cmd

exec (@cmd)

COMMIT TRAN

END TRY

BEGIN CATCH

ROLLBACK TRANSACTION

INSERT @BadObjects VALUES (@objname)

END CATCH

FETCH NEXT FROM c1 into @objname,@cmd

END --WHILE

CLOSE c1

DEALLOCATE c1

SELECT * FROM @BadObjects

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!