SQL-Server-Tools/csp_ExportColumnDescriptions.txt

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE dbo.csp_ExportColumnDescriptions
	@TableName SYSNAME -- Can be in the format like 'Invoices' or 'dbo.invoices' or '[invoices]' or '[RH8].[invoices]'
AS
/******************************************************************************
**		File: 
**		Name: csp_ExportColumnDescriptions
**		Desc: Exports the column descriptions for a specified table in a format
**            that's good for importing to another database.
*******************************************************************************/
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,
		[Description] NVARCHAR(4000),
		PRIMARY KEY(RowID)
	);
	DECLARE @Output TABLE
	(
		RowID INT IDENTITY(1,1),
		[SQL] NVARCHAR(4000),
		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);

	-- 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, [Description])
	SELECT SC.name AS ColumnName,CONVERT(NVARCHAR(4000),SEP.value) as [Description]
	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
	ORDER BY SC.name;

	SET @Length = @@ROWCOUNT;

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

		INSERT INTO @Output ([SQL]) VALUES (CONCAT(N'EXEC sys.sp_addextendedproperty @name=N''MS_Description'', @value=N''',REPLACE(@Description,N'''',N''''''),N''''));
		INSERT INTO @Output ([SQL]) VALUES (CONCAT(N'       , @level0type=N''SCHEMA'',@level0name=N''',REPLACE(@Schema,N'''',N''''''),N''''));
		INSERT INTO @Output ([SQL]) VALUES (CONCAT(N'       , @level1type=N''TABLE'',@level1name=N''',REPLACE(@Table,N'''',N''''''),N''''));
		INSERT INTO @Output ([SQL]) VALUES (CONCAT(N'       , @level2type=N''COLUMN'',@level2name=N''',REPLACE(@Column,N'''',N''''''),N''';'));
		INSERT INTO @Output ([SQL]) VALUES ('');

		SET @Loop = @Loop + 1;
	END;

	SELECT [SQL]
	FROM @Output
	ORDER BY RowID;
END