Fixed Price Projects          Flexibele IT oplossingen

Software op maat              Web Applications

When you drop a column from the MS SQL management studio, it automatically drops all depending objects of that column.  When you want to use the DROP statement it is possible as long as it has dependent objects.

Therefore we need a procedure to do this:

CREATE PROCEDURE DropColumnCascading @tablename nvarchar(500), @columnname nvarchar(500)

AS

 

SELECT CONSTRAINT_NAME, 'C' AS type

INTO #dependencies

FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME = @tablename AND COLUMN_NAME = @columnname

 

INSERT INTO #dependencies

select d.name, 'C'

from sys.default_constraints d

join sys.columns c ON c.column_id = d.parent_column_id AND c.object_id = d.parent_object_id

join sys.objects o ON o.object_id = d.parent_object_id

WHERE o.name = @tablename AND c.name = @columnname

 

INSERT INTO #dependencies

SELECT i.name, 'I'

FROM sys.indexes i

JOIN sys.index_columns ic ON ic.index_id = i.index_id and ic.object_id=i.object_id

JOIN sys.columns c ON c.column_id = ic.column_id and c.object_id=i.object_id

JOIN sys.objects o ON o.object_id = i.object_id

where o.name = @tableName AND i.type=2 AND c.name = @columnname AND is_unique_constraint = 0

 

DECLARE @dep_name nvarchar(500)

DECLARE @type nchar(1)

 

DECLARE dep_cursor CURSOR

FOR SELECT * FROM #dependencies

 

OPEN dep_cursor

 

FETCH NEXT FROM dep_cursor

INTO @dep_name, @type;

 

DECLARE @sql nvarchar(max)

 

WHILE @@FETCH_STATUS = 0

BEGIN

    SET @sql =

        CASE @type

            WHEN 'C' THEN 'ALTER TABLE [' + @tablename + '] DROP CONSTRAINT [' + @dep_name + ']'

            WHEN 'I' THEN 'DROP INDEX [' + @dep_name + '] ON dbo.[' + @tablename + ']'

        END

    print @sql

    EXEC sp_executesql @sql

    FETCH NEXT FROM dep_cursor

    INTO @dep_name, @type;

END

 

DEALLOCATE dep_cursor

 

DROP TABLE #dependencies

 

SET @sql = 'ALTER TABLE [' + @tablename + '] DROP COLUMN [' + @columnname + ']'

 

print @sql

EXEC sp_executesql @sql

 

GO

 

Categorie: SQL
Actions: E-mail | Permalink |

waardering