SQL-Server-Tools/csp_DropColumnDescriptions.txt

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE dbo.csp_DropColumnDescriptions
	@TableName SYSNAME -- Can be in the format like 'Invoices' or 'dbo.invoices' or '[invoices]' or '[RH8].[invoices]'
AS
/******************************************************************************
**		File: 
**		Name: csp_DropColumnDescriptions
**		Desc: Drops all the column descriptions for the specified table.
*******************************************************************************/
BEGIN
	DECLARE @Items TABLE
	(
		ItemID INT IDENTITY(1,1),
		Item NVARCHAR(200),
		ItemType VARCHAR(50),
		PRIMARY KEY(itemID)
	);
	DECLARE @Export TABLE
	(
		RowID INT IDENTITY(1,1),
		ColumnName SYSNAME,	
		PRIMARY KEY(RowID)
	);	
	DECLARE @Length INT;
	DECLARE @Loop INT;
	DECLARE @Start INT;
	DECLARE @ItemCount INT;
	DECLARE @Ch VARCHAR(1);
	DECLARE @InSquareBrackets INT;
	DECLARE @SQL NVARCHAR(4000);
	DECLARE @Schema NVARCHAR(200);
	DECLARE @Table NVARCHAR(200);
	DECLARE @Column NVARCHAR(200);
	DECLARE @TableID INT;
	DECLARE @Description NVARCHAR(4000);

	SET NOCOUNT ON;

	-- Parse @Field by . and put it im @Items.
	SET @Length = LEN(@TableName);
	SET @Start = 1;
	SET @Loop = 1;
	SET @InSquareBrackets = 0;
	WHILE @Loop <= @Length
	BEGIN
		SET @Ch = SUBSTRING(@TableName,@Loop,1);
		IF @Ch = '['
		BEGIN
			SET @InSquareBrackets = @InSquareBrackets + 1;
			IF @InSquareBrackets > 1
			BEGIN
				THROW 50000,'Square brackets incorrect in @Field',1;
			END;
		END;
		IF @Ch = ']'
		BEGIN
			SET @InSquareBrackets = @InSquareBrackets - 1;
			IF @InSquareBrackets < 0
			BEGIN
				THROW 50000,'Square brackets incorrect in @Field',1;
			END;
		END;
		IF @Ch = '.' AND @InSquareBrackets = 0
		BEGIN
			INSERT INTO @Items (Item,ItemType) 
			VALUES(SUBSTRING(@TableName,@Start,@Loop-@Start),'');
			SET @Start = @Loop + 1;
		END
		SET @Loop = @Loop + 1;
	END;
	INSERT INTO @Items (Item,ItemType) 
	VALUES (SUBSTRING(@TableName,@Start,@Length-@Start+1),'');

	-- Remove [ and ] from @Items.Item
	UPDATE @Items
	SET Item = SUBSTRING(Item,2,LEN(Item)-2)
	WHERE LEN(Item) >=2 AND SUBSTRING(Item,1,1) = '[' AND SUBSTRING(Item,LEN(Item),1) = ']';

	-- Add descriptions to Item.
	SELECT @ItemCount = COUNT(*) FROM @Items;

	IF @ItemCount < 1 OR @ItemCount > 2
	BEGIN
		SET @SQL = N'Incorrect number of items in @TableName';
		THROW 50000,@SQL,1;
	END;
	IF @ItemCount = 2
	BEGIN
		UPDATE @Items SET ItemType = CASE ItemID WHEN 1 THEN 'Schema' WHEN 2 THEN 'Table' END;		
	END
	IF @ItemCount = 1
	BEGIN
		UPDATE @Items SET ItemType = CASE ItemID WHEN 1 THEN 'Table' END;
	END	

	-- Find the TableID.
	SELECT @Schema = Item FROM @Items WHERE ItemType = 'Schema';
	SELECT @Table = Item FROM @Items WHERE ItemType = 'Table';	
	-- Find out how many tables have this name.
	SELECT @TableID = COUNT(*) FROM sys.tables WHERE [type] = 'U' AND name = @Table;
	IF ISNULL(@TableID,0) = 0
	BEGIN
		SET @SQL = CONCAT(N'Table ',@Table,N' wasn`t found');
		THROW 50000,@SQL,1;
	END
	IF ISNULL(@TableID,0) = 1
	BEGIN
		-- Don't use schema.
		SET @TableID = 0;
		SELECT @TableID = object_id,@Schema = SCHEMA_NAME(schema_id) FROM sys.tables WHERE [type] = 'U' AND name = @Table;
	END ELSE
	BEGIN
		-- Use schema.
		SELECT @TableID = object_id FROM sys.tables WHERE [type] = 'U' AND name = @Table AND SCHEMA_NAME(schema_id) = @Schema;
	END
	IF ISNULL(@TableID,0) = 0
	BEGIN
		SET @SQL = CONCAT(N'Table ',@Table,N' wasn`t found');
		THROW 50000,@SQL,1;
	END;

	INSERT INTO @Export (ColumnName)
	SELECT SC.name AS ColumnName
	FROM sys.extended_properties AS SEP 
	INNER JOIN sys.columns as SC
	ON SEP.minor_id = SC.column_id
	WHERE 
		SEP.class_desc = 'OBJECT_OR_COLUMN' 
		AND SEP.NAME = 'Ms_Description' 
		AND SEP.major_id = @TableID 
		AND SC.object_id = @TableID;

	SET @Length = @@ROWCOUNT;

	SET @Loop = 1;
	WHILE @Loop <= @Length
	BEGIN
		SELECT @Column = ColumnName
		FROM @Export
		WHERE RowID = @Loop;

		EXEC sys.sp_dropextendedproperty @name=N'MS_Description'
						, @level0type=N'SCHEMA',@level0name=@Schema
						, @level1type=N'TABLE',@level1name=@Table
						, @level2type=N'COLUMN',@level2name=@Column;
		
		SET @Loop = @Loop + 1;
	END;	
END