Knowledge Share 2014-05

* 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)[1]', 
                                                       'varchar(100)') 
                            AND is_tracked_by_cdc = 1 ) 
            ROLLBACK 
    END
Advertisements
This entry was posted in knowledge, Script, SQL, SQL Query and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s