Reading comments from all SQL server tables columns

I needed a script for gather all comments into one table. I have created this one:

Script creates new table AllComments which contains two columns (column, comment).

Sample output:

image

And script producing this

IF OBJECT_ID(N'AllComments', N'U') IS NOT NULL
DROP TABLE AllComments;
create table AllComments
([column] varchar(100), [comment] varchar(200))
declare Tbl_Cursor cursor for
select o.name, s.name from sys.objects o
join sys.schemas s on s.schema_id = o.schema_id
where type='U';
declare @tbl_name as varchar(50);
declare @tbl_schema as varchar(50);
open tbl_cursor;
fetch next from tbl_cursor
  into @tbl_name, @tbl_schema;
while @@FETCH_STATUS=0
  begin
	declare Desc_Cursor cursor for
	select c.name, cast(value as varchar)
	from sys.all_columns c
	left join fn_listextendedproperty(
	'MS_Description','schema', @tbl_schema, 'table',
	 @tbl_name, 'column', default) p
	 on
	(c.name collate Latin1_General_CI_AI = p.objname
		or p.objname is null)
	where c.object_id = OBJECT_ID(@tbl_schema + '.' +@tbl_name)
		declare @desc_col as varchar(50);
		declare @desc_value as varchar(50);
		open Desc_Cursor;
		fetch next from desc_cursor
		  into @desc_col, @desc_value;
		while @@FETCH_STATUS=0
		  begin
		  INSERT INTO [AllComments]
           ([column],[comment])
             VALUES
           (@tbl_schema + '.' +@tbl_name + '.'+ @desc_col,@desc_value);
	  	  fetch next from desc_cursor
		    into @desc_col, @desc_value;
		  end;
	close desc_cursor;
	deallocate desc_cursor;
    print @tbl_schema + '.' + @tbl_name;
    fetch next from tbl_cursor
      into @tbl_name, @tbl_schema;
  end;
close tbl_cursor;
deallocate tbl_cursor;

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 )

Google+ photo

You are commenting using your Google+ 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 )

w

Connecting to %s