How to execute complete database script without checks on objects existence

Question

I'm working on a customer data archiving. I created a complete database script to prepare the new database for archiving data with the same DB schema of the production one. At the moment I'm preparing a Test environment to verify all the steps we will execute in in production later, but the script is, obviously, raising a lot of errors because of missing external database's views, functions and stored procedure being referred to.

Is it possible to switch off objects existence during the script execution, so I can create the complete database schema to make my tests?

---

I decided to proceed with tests about restoring the whole production database and then clear all tables data. To clear the data I will try suggestions I found in replies to this question in Stack Overflow, to be precise these two:

• https://stackoverflow.com/a/12719464/21116580
• https://stackoverflow.com/a/22527902/21116580

I will come back as soon as I'll complete my tests with the results.

Answer 1

1. If the archiving db should have the same schema, that should include views. If the views are referencing other dbs that are not available, one solution could be to extract the view script and modify the FROM source.
2. If you are archiving data, why do you need views, functions and stored procedures?

Is it possible to get a copy of your script, to make things easier?

Here is a sample script to modify the view:

DECLARE
 @ViewName SYSNAME = 'SomeView'
 , @viewscript NVARCHAR(MAX)
DECLARE @sp_helptextresult TABLE
(
 CodeLine NVARCHAR(max)
 , RowId BIGINT IDENTITY(1, 1)
)
INSERT INTO @sp_helptextresult
(
 CodeLine
)
EXEC sp_helptext @ViewName
Update @sp_helptextresult
Set codeline = 'CREATE OR ALTER VIEW ' + @ViewName WHERE codeline LIKE '%CREATE VIEW%'
Update @sp_helptextresult
Set codeline = ' FROM SomeOtherSource src ' WHERE codeline LIKE '%FROM Somedb..OriginalSource src%'
SELECT
 @viewscript = STRING_AGG(CodeLine, CHAR(10))
FROM
 @sp_helptextresult
Print @viewscript
 
/*
--EXEC SP_ExecuteSql
-- @viewscript
*/

• El See: Hi MUSQL, thanks for your hints. Replying to your question about "If you are archiving data, why do you need views, functions and stored procedures?", this is because we must give access to the client on archived data for printings and statistics, so we will use the same application in "readonly" mode connected to the archive db for this scope.
• Andrey Samykin: El See, I believe the better approach is to restore a copy of your PROD database and delete unneeded data.
• El See: Thank you Andrey Samykin, I was asking to myself if this would be a good approach and your post is giving me a confirmation this may be the right choice :-)