* Function to convert a list into a table (Collected this piece of code from my Colleague, Satya Addala)
CREATE FUNCTION [dbo].[udf_ListToTable] (@List VARCHAR(MAX), @Delim CHAR ) RETURNS @ParsedList TABLE ( item VARCHAR(MAX) ) AS BEGIN DECLARE @item VARCHAR(MAX), @Pos INT SET @List = LTRIM(RTRIM(@List))+ @Delim SET @Pos = CHARINDEX(@Delim, @List, 1) WHILE @Pos > 0 BEGIN SET @item = LTRIM(RTRIM(LEFT(@List, @Pos - 1))) IF @item <> '' BEGIN INSERT INTO @ParsedList (item) VALUES (CAST(@item AS VARCHAR(MAX))) END SET @List = RIGHT(@List, LEN(@List) - @Pos) SET @Pos = CHARINDEX(@Delim, @List, 1) END RETURN END
* Computed columns can not be altered but needs to be dropped and re-created
* Caution while creating DDL triggers, specially when you try to rollback drop table statement: Below trigger will not solve the purpose as the table record is already deleted from sys.tables table but this will work as expected if you just rollback with out validating the condition. You may have to create a custom solution in real time scenario.
CREATE TRIGGER PreventDrop ON DATABASE FOR DROP_TABLE AS BEGIN IF EXISTS ( SELECT * FROM sys.tables WHERE name = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)', 'varchar(100)') AND is_tracked_by_cdc = 1 ) ROLLBACK END