DECLARE @definitionlike VARCHAR(MAX) = '%app01%'
DECLARE @SQL VARCHAR(MAX) ;
WITH step1
AS (SELECT s.name AS SchemaName,
o.name AS ObjectName,
ot.CreateDefinition AS ObjectType,
m.Definition
FROM sys.objects o (NOLOCK)
INNER JOIN sys.schemas s (NOLOCK) ON s.SCHEMA_ID = o.SCHEMA_ID
INNER JOIN sys.all_sql_modules m (NOLOCK) ON o.OBJECT_ID = m.OBJECT_ID
INNER JOIN Util.dbo.SystemObjectTypes ot ON ot.Type = o.type COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE o.is_ms_shipped = 0
AND m.definition LIKE @definitionlike),
step2
AS (SELECT Util.dbo.StringConcat('----------------------- ' + ObjectType + ' : [' + SchemaName + '].[' + ObjectName + ']', '
') AS SQL
FROM step1
UNION ALL
SELECT Util.dbo.StringConcat('--/* DROP SCRIPT */ IF OBJECT_ID(''[' + SchemaName + '].[' + ObjectName + ']'') IS NOT NULL DROP '
+ ObjectType + ' ' + '[' + SchemaName + '].[' + ObjectName + ']
GO
' + Definition, '
GO
') AS SQL
FROM STEP1)
SELECT @sql = Util.dbo.StringConcat(SQL, '
GO
')
FROM STEP2
EXEC Util.dbo.PrintLargeText
@SQL