I have a database with three main Schemas;
- Unique Schema name based on the customers ID number (1, 2, 3, 4, ect...)
Each customer has objects in all three schemas, with the Global and Permissions schema object names, containing the customers ID number followed by an _
.[Test] [Global].[1_Test] [Permissions].[1_Test]
So far, I have written the bellow script that does this, but only keeps one customers data, not multiple;
DECLARE @TenantId NVARCHAR(10) DECLARE @Commands NVARCHAR(MAX) DECLARE @Statements TABLE ( Commands NVARCHAR(500) ) SET @TenantId = '686' INSERT INTO @Statements EXECUTE ( 'SELECT ''DROP TABLE'' + '' '' + ''['' + s.[name] + '']'' + ''.'' + ''['' + t.[name] + '']'' AS ''Commands'' FROM sys.schemas s JOIN sys.tables t on s.schema_id = t.schema_id WHERE s.name <> '''+ @TenantId +''' AND s.name <> ''dbo'' AND t.name NOT LIKE '''+ @TenantId +'_%'' AND t.name NOT IN (''DatabaseVersion'', ''PermissionTypes'') ORDER BY s.name, t.name' ) SELECT * FROM @Statements --SET @Commands =( --SELECT STRING_AGG (CAST([Commands] AS NVARCHAR(MAX)), ' ') FROM @Statements ) --SELECT (@Commands)
How can I convert this so that it takes multiple IDs into account, except for just one? I feel that I need to use a Cursor here, but not sure how that would be structured.
Thanks in advance.