Немного комментариев для таблиц в MSSQL

Привет Хабр!
Некоторые из нас пишут и/или поддерживают БД на основе СУБД MSSQL 20xx. Я такими развлечениями занимаюсь давно, и как-то потихоньку я для себя решил писать комментарии к таблицам и столбцам, как к своим так и к чужим. Это оказалось удобно, благодаря тому, что всегда можно увидеть зачем “это” было сделано, и как “это” кушать. Единственным неудобным моментом является написание этих самых комментариев, ибо в MS SQL для этого нужно создавать строчки типа:
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=@col1Decr , @level0type=N'SCHEMA', @level0name=@schema, @level1type=N'TABLE', @level1name=@tabName, @level2type=N'COLUMN', @level2name=@col1Name

Как-то много кода для простой операции, а иных, более лёгких способов я не нашел. Немного подумав, я решил создать простую процедуру для добавления комментариев.

текст процедуры
create procedure [sysAdd].[AddColumnDescription]
	@schema sysname,
	@tabName sysname,
	@tabDecr nvarchar(2000),

	@col1Name sysname = null,
	@col1Decr nvarchar(2000) = null,
	@col2Name sysname = null,
	@col2Decr nvarchar(2000) = null,
	@col3Name sysname = null,
	@col3Decr nvarchar(2000) = null,
	@col4Name sysname = null,
	@col4Decr nvarchar(2000) = null,
	@col5Name sysname = null,
	@col5Decr nvarchar(2000) = null,
	@col6Name sysname = null,
	@col6Decr nvarchar(2000) = null,
	@col7Name sysname = null,
	@col7Decr nvarchar(2000) = null,
	@col8Name sysname = null,
	@col8Decr nvarchar(2000) = null,
	@col9Name sysname = null,
	@col9Decr nvarchar(2000) = null,
	@col10Name sysname = null,
	@col10Decr nvarchar(2000) = null,
	@col11Name sysname = null,
	@col11Decr nvarchar(2000) = null,
	@col12Name sysname = null,
	@col12Decr nvarchar(2000) = null,
	@col13Name sysname = null,
	@col13Decr nvarchar(2000) = null,
	@col14Name sysname = null,
	@col14Decr nvarchar(2000) = null,
	@col15Name sysname = null,
	@col15Decr nvarchar(2000) = null,
	@col16Name sysname = null,
	@col16Decr nvarchar(2000) = null,
	@col17Name sysname = null,
	@col17Decr nvarchar(2000) = null,
	@col18Name sysname = null,
	@col18Decr nvarchar(2000) = null,
	@col19Name sysname = null,
	@col19Decr nvarchar(2000) = null,
	@col20Name sysname = null,
	@col20Decr nvarchar(2000) = null
as
begin
	if(@tabDecr is not null)
		if(exists(select 1 
			from sys.schemas sch inner join sys.tables tab on tab.schema_id = sch.schema_id
				inner join sys.extended_properties ep on ep.major_id = tab.object_id and ep.minor_id = 0
			where tab.name = @tabName and sch.name = @schema))
				EXEC sys.sp_updateextendedproperty @name=N'MS_Description', @value=@tabDecr, @level0type=N'SCHEMA',@level0name=@schema
					, @level1type=N'TABLE',@level1name=@tabName
			else
				EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=@tabDecr, @level0type=N'SCHEMA',@level0name=@schema
					, @level1type=N'TABLE',@level1name=@tabName

	-- Увы, однотипный копипаст
		if(@col1Name is not null)
		if(exists(select 1 
			from sys.schemas sch inner join sys.tables tab on tab.schema_id = sch.schema_id
				inner join sys.columns col on col.object_id = tab.object_id inner join sys.extended_properties ep on ep.major_id = tab.object_id and ep.minor_id = col.column_id
			where tab.name = @tabName and sch.name = @schema and col.name = @col1Name))
				EXEC sys.sp_updateextendedproperty @name=N'MS_Description', @value=@col1Decr , @level0type=N'SCHEMA',@level0name=@schema
					, @level1type=N'TABLE',@level1name=@tabName, @level2type=N'COLUMN',@level2name=@col1Name
			else
				EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=@col1Decr , @level0type=N'SCHEMA',@level0name=@schema
					, @level1type=N'TABLE',@level1name=@tabName, @level2type=N'COLUMN',@level2name=@col1Name
	if(@col2Name is not null)
		if(exists(select 1 
			from sys.schemas sch inner join sys.tables tab on tab.schema_id = sch.schema_id
				inner join sys.columns col on col.object_id = tab.object_id inner join sys.extended_properties ep on ep.major_id = tab.object_id and ep.minor_id = col.column_id
			where tab.name = @tabName and sch.name = @schema and col.name = @col2Name))
				EXEC sys.sp_updateextendedproperty @name=N'MS_Description', @value=@col2Decr , @level0type=N'SCHEMA',@level0name=@schema
					, @level1type=N'TABLE',@level1name=@tabName, @level2type=N'COLUMN',@level2name=@col2Name
			else
				EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=@col2Decr , @level0type=N'SCHEMA',@level0name=@schema
					, @level1type=N'TABLE',@level1name=@tabName, @level2type=N'COLUMN',@level2name=@col2Name
	if(@col3Name is not null)
		if(exists(select 1 
			from sys.schemas sch inner join sys.tables tab on tab.schema_id = sch.schema_id
				inner join sys.columns col on col.object_id = tab.object_id inner join sys.extended_properties ep on ep.major_id = tab.object_id and ep.minor_id = col.column_id
			where tab.name = @tabName and sch.name = @schema and col.name = @col3Name))
				EXEC sys.sp_updateextendedproperty @name=N'MS_Description', @value=@col3Decr , @level0type=N'SCHEMA',@level0name=@schema
					, @level1type=N'TABLE',@level1name=@tabName, @level2type=N'COLUMN',@level2name=@col3Name
			else
				EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=@col3Decr , @level0type=N'SCHEMA',@level0name=@schema
					, @level1type=N'TABLE',@level1name=@tabName, @level2type=N'COLUMN',@level2name=@col3Name
	if(@col4Name is not null)
		if(exists(select 1 
			from sys.schemas sch inner join sys.tables tab on tab.schema_id = sch.schema_id
				inner join sys.columns col on col.object_id = tab.object_id inner join sys.extended_properties ep on ep.major_id = tab.object_id and ep.minor_id = col.column_id
			where tab.name = @tabName and sch.name = @schema and col.name = @col4Name))
				EXEC sys.sp_updateextendedproperty @name=N'MS_Description', @value=@col4Decr , @level0type=N'SCHEMA',@level0name=@schema
					, @level1type=N'TABLE',@level1name=@tabName, @level2type=N'COLUMN',@level2name=@col4Name
			else
				EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=@col4Decr , @level0type=N'SCHEMA',@level0name=@schema
					, @level1type=N'TABLE',@level1name=@tabName, @level2type=N'COLUMN',@level2name=@col4Name
	if(@col5Name is not null)
		if(exists(select 1 
			from sys.schemas sch inner join sys.tables tab on tab.schema_id = sch.schema_id
				inner join sys.columns col on col.object_id = tab.object_id inner join sys.extended_properties ep on ep.major_id = tab.object_id and ep.minor_id = col.column_id
			where tab.name = @tabName and sch.name = @schema and col.name = @col5Name))
				EXEC sys.sp_updateextendedproperty @name=N'MS_Description', @value=@col5Decr , @level0type=N'SCHEMA',@level0name=@schema
					, @level1type=N'TABLE',@level1name=@tabName, @level2type=N'COLUMN',@level2name=@col5Name
			else
				EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=@col5Decr , @level0type=N'SCHEMA',@level0name=@schema
					, @level1type=N'TABLE',@level1name=@tabName, @level2type=N'COLUMN',@level2name=@col5Name
	if(@col6Name is not null)
		if(exists(select 1 
			from sys.schemas sch inner join sys.tables tab on tab.schema_id = sch.schema_id
				inner join sys.columns col on col.object_id = tab.object_id inner join sys.extended_properties ep on ep.major_id = tab.object_id and ep.minor_id = col.column_id
			where tab.name = @tabName and sch.name = @schema and col.name = @col6Name))
				EXEC sys.sp_updateextendedproperty @name=N'MS_Description', @value=@col6Decr , @level0type=N'SCHEMA',@level0name=@schema
					, @level1type=N'TABLE',@level1name=@tabName, @level2type=N'COLUMN',@level2name=@col6Name
			else
				EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=@col6Decr , @level0type=N'SCHEMA',@level0name=@schema
					, @level1type=N'TABLE',@level1name=@tabName, @level2type=N'COLUMN',@level2name=@col6Name
	if(@col7Name is not null)
		if(exists(select 1 
			from sys.schemas sch inner join sys.tables tab on tab.schema_id = sch.schema_id
				inner join sys.columns col on col.object_id = tab.object_id inner join sys.extended_properties ep on ep.major_id = tab.object_id and ep.minor_id = col.column_id
			where tab.name = @tabName and sch.name = @schema and col.name = @col7Name))
				EXEC sys.sp_updateextendedproperty @name=N'MS_Description', @value=@col7Decr , @level0type=N'SCHEMA',@level0name=@schema
					, @level1type=N'TABLE',@level1name=@tabName, @level2type=N'COLUMN',@level2name=@col7Name
			else
				EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=@col7Decr , @level0type=N'SCHEMA',@level0name=@schema
					, @level1type=N'TABLE',@level1name=@tabName, @level2type=N'COLUMN',@level2name=@col7Name
	if(@col8Name is not null)
		if(exists(select 1 
			from sys.schemas sch inner join sys.tables tab on tab.schema_id = sch.schema_id
				inner join sys.columns col on col.object_id = tab.object_id inner join sys.extended_properties ep on ep.major_id = tab.object_id and ep.minor_id = col.column_id
			where tab.name = @tabName and sch.name = @schema and col.name = @col8Name))
				EXEC sys.sp_updateextendedproperty @name=N'MS_Description', @value=@col8Decr , @level0type=N'SCHEMA',@level0name=@schema
					, @level1type=N'TABLE',@level1name=@tabName, @level2type=N'COLUMN',@level2name=@col8Name
			else
				EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=@col8Decr , @level0type=N'SCHEMA',@level0name=@schema
					, @level1type=N'TABLE',@level1name=@tabName, @level2type=N'COLUMN',@level2name=@col8Name
	if(@col9Name is not null)
		if(exists(select 1 
			from sys.schemas sch inner join sys.tables tab on tab.schema_id = sch.schema_id
				inner join sys.columns col on col.object_id = tab.object_id inner join sys.extended_properties ep on ep.major_id = tab.object_id and ep.minor_id = col.column_id
			where tab.name = @tabName and sch.name = @schema and col.name = @col9Name))
				EXEC sys.sp_updateextendedproperty @name=N'MS_Description', @value=@col9Decr , @level0type=N'SCHEMA',@level0name=@schema
					, @level1type=N'TABLE',@level1name=@tabName, @level2type=N'COLUMN',@level2name=@col9Name
			else
				EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=@col9Decr , @level0type=N'SCHEMA',@level0name=@schema
					, @level1type=N'TABLE',@level1name=@tabName, @level2type=N'COLUMN',@level2name=@col9Name
	if(@col10Name is not null)
		if(exists(select 1 
			from sys.schemas sch inner join sys.tables tab on tab.schema_id = sch.schema_id
				inner join sys.columns col on col.object_id = tab.object_id inner join sys.extended_properties ep on ep.major_id = tab.object_id and ep.minor_id = col.column_id
			where tab.name = @tabName and sch.name = @schema and col.name = @col10Name))
				EXEC sys.sp_updateextendedproperty @name=N'MS_Description', @value=@col10Decr , @level0type=N'SCHEMA',@level0name=@schema
					, @level1type=N'TABLE',@level1name=@tabName, @level2type=N'COLUMN',@level2name=@col10Name
			else
				EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=@col10Decr , @level0type=N'SCHEMA',@level0name=@schema
					, @level1type=N'TABLE',@level1name=@tabName, @level2type=N'COLUMN',@level2name=@col10Name
	if(@col11Name is not null)
		if(exists(select 1 
			from sys.schemas sch inner join sys.tables tab on tab.schema_id = sch.schema_id
				inner join sys.columns col on col.object_id = tab.object_id inner join sys.extended_properties ep on ep.major_id = tab.object_id and ep.minor_id = col.column_id
			where tab.name = @tabName and sch.name = @schema and col.name = @col11Name))
				EXEC sys.sp_updateextendedproperty @name=N'MS_Description', @value=@col11Decr , @level0type=N'SCHEMA',@level0name=@schema
					, @level1type=N'TABLE',@level1name=@tabName, @level2type=N'COLUMN',@level2name=@col11Name
			else
				EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=@col11Decr , @level0type=N'SCHEMA',@level0name=@schema
					, @level1type=N'TABLE',@level1name=@tabName, @level2type=N'COLUMN',@level2name=@col11Name
	if(@col12Name is not null)
		if(exists(select 1 
			from sys.schemas sch inner join sys.tables tab on tab.schema_id = sch.schema_id
				inner join sys.columns col on col.object_id = tab.object_id inner join sys.extended_properties ep on ep.major_id = tab.object_id and ep.minor_id = col.column_id
			where tab.name = @tabName and sch.name = @schema and col.name = @col12Name))
				EXEC sys.sp_updateextendedproperty @name=N'MS_Description', @value=@col12Decr , @level0type=N'SCHEMA',@level0name=@schema
					, @level1type=N'TABLE',@level1name=@tabName, @level2type=N'COLUMN',@level2name=@col12Name
			else
				EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=@col12Decr , @level0type=N'SCHEMA',@level0name=@schema
					, @level1type=N'TABLE',@level1name=@tabName, @level2type=N'COLUMN',@level2name=@col12Name
	if(@col13Name is not null)
		if(exists(select 1 
			from sys.schemas sch inner join sys.tables tab on tab.schema_id = sch.schema_id
				inner join sys.columns col on col.object_id = tab.object_id inner join sys.extended_properties ep on ep.major_id = tab.object_id and ep.minor_id = col.column_id
			where tab.name = @tabName and sch.name = @schema and col.name = @col13Name))
				EXEC sys.sp_updateextendedproperty @name=N'MS_Description', @value=@col13Decr , @level0type=N'SCHEMA',@level0name=@schema
					, @level1type=N'TABLE',@level1name=@tabName, @level2type=N'COLUMN',@level2name=@col13Name
			else
				EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=@col13Decr , @level0type=N'SCHEMA',@level0name=@schema
					, @level1type=N'TABLE',@level1name=@tabName, @level2type=N'COLUMN',@level2name=@col13Name
	if(@col14Name is not null)
		if(exists(select 1 
			from sys.schemas sch inner join sys.tables tab on tab.schema_id = sch.schema_id
				inner join sys.columns col on col.object_id = tab.object_id inner join sys.extended_properties ep on ep.major_id = tab.object_id and ep.minor_id = col.column_id
			where tab.name = @tabName and sch.name = @schema and col.name = @col14Name))
				EXEC sys.sp_updateextendedproperty @name=N'MS_Description', @value=@col14Decr , @level0type=N'SCHEMA',@level0name=@schema
					, @level1type=N'TABLE',@level1name=@tabName, @level2type=N'COLUMN',@level2name=@col14Name
			else
				EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=@col14Decr , @level0type=N'SCHEMA',@level0name=@schema
					, @level1type=N'TABLE',@level1name=@tabName, @level2type=N'COLUMN',@level2name=@col14Name
	if(@col15Name is not null)
		if(exists(select 1 
			from sys.schemas sch inner join sys.tables tab on tab.schema_id = sch.schema_id
				inner join sys.columns col on col.object_id = tab.object_id inner join sys.extended_properties ep on ep.major_id = tab.object_id and ep.minor_id = col.column_id
			where tab.name = @tabName and sch.name = @schema and col.name = @col15Name))
				EXEC sys.sp_updateextendedproperty @name=N'MS_Description', @value=@col15Decr , @level0type=N'SCHEMA',@level0name=@schema
					, @level1type=N'TABLE',@level1name=@tabName, @level2type=N'COLUMN',@level2name=@col15Name
			else
				EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=@col15Decr , @level0type=N'SCHEMA',@level0name=@schema
					, @level1type=N'TABLE',@level1name=@tabName, @level2type=N'COLUMN',@level2name=@col15Name
	if(@col16Name is not null)
		if(exists(select 1 
			from sys.schemas sch inner join sys.tables tab on tab.schema_id = sch.schema_id
				inner join sys.columns col on col.object_id = tab.object_id inner join sys.extended_properties ep on ep.major_id = tab.object_id and ep.minor_id = col.column_id
			where tab.name = @tabName and sch.name = @schema and col.name = @col16Name))
				EXEC sys.sp_updateextendedproperty @name=N'MS_Description', @value=@col16Decr , @level0type=N'SCHEMA',@level0name=@schema
					, @level1type=N'TABLE',@level1name=@tabName, @level2type=N'COLUMN',@level2name=@col16Name
			else
				EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=@col16Decr , @level0type=N'SCHEMA',@level0name=@schema
					, @level1type=N'TABLE',@level1name=@tabName, @level2type=N'COLUMN',@level2name=@col16Name
	if(@col17Name is not null)
		if(exists(select 1 
			from sys.schemas sch inner join sys.tables tab on tab.schema_id = sch.schema_id
				inner join sys.columns col on col.object_id = tab.object_id inner join sys.extended_properties ep on ep.major_id = tab.object_id and ep.minor_id = col.column_id
			where tab.name = @tabName and sch.name = @schema and col.name = @col17Name))
				EXEC sys.sp_updateextendedproperty @name=N'MS_Description', @value=@col17Decr , @level0type=N'SCHEMA',@level0name=@schema
					, @level1type=N'TABLE',@level1name=@tabName, @level2type=N'COLUMN',@level2name=@col17Name
			else
				EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=@col17Decr , @level0type=N'SCHEMA',@level0name=@schema
					, @level1type=N'TABLE',@level1name=@tabName, @level2type=N'COLUMN',@level2name=@col17Name
	if(@col18Name is not null)
		if(exists(select 1 
			from sys.schemas sch inner join sys.tables tab on tab.schema_id = sch.schema_id
				inner join sys.columns col on col.object_id = tab.object_id inner join sys.extended_properties ep on ep.major_id = tab.object_id and ep.minor_id = col.column_id
			where tab.name = @tabName and sch.name = @schema and col.name = @col18Name))
				EXEC sys.sp_updateextendedproperty @name=N'MS_Description', @value=@col18Decr , @level0type=N'SCHEMA',@level0name=@schema
					, @level1type=N'TABLE',@level1name=@tabName, @level2type=N'COLUMN',@level2name=@col18Name
			else
				EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=@col18Decr , @level0type=N'SCHEMA',@level0name=@schema
					, @level1type=N'TABLE',@level1name=@tabName, @level2type=N'COLUMN',@level2name=@col18Name
	if(@col19Name is not null)
		if(exists(select 1 
			from sys.schemas sch inner join sys.tables tab on tab.schema_id = sch.schema_id
				inner join sys.columns col on col.object_id = tab.object_id inner join sys.extended_properties ep on ep.major_id = tab.object_id and ep.minor_id = col.column_id
			where tab.name = @tabName and sch.name = @schema and col.name = @col19Name))
				EXEC sys.sp_updateextendedproperty @name=N'MS_Description', @value=@col19Decr , @level0type=N'SCHEMA',@level0name=@schema
					, @level1type=N'TABLE',@level1name=@tabName, @level2type=N'COLUMN',@level2name=@col19Name
			else
				EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=@col19Decr , @level0type=N'SCHEMA',@level0name=@schema
					, @level1type=N'TABLE',@level1name=@tabName, @level2type=N'COLUMN',@level2name=@col19Name
	if(@col20Name is not null)
		if(exists(select 1 
			from sys.schemas sch inner join sys.tables tab on tab.schema_id = sch.schema_id
				inner join sys.columns col on col.object_id = tab.object_id inner join sys.extended_properties ep on ep.major_id = tab.object_id and ep.minor_id = col.column_id
			where tab.name = @tabName and sch.name = @schema and col.name = @col20Name))
				EXEC sys.sp_updateextendedproperty @name=N'MS_Description', @value=@col20Decr , @level0type=N'SCHEMA',@level0name=@schema
					, @level1type=N'TABLE',@level1name=@tabName, @level2type=N'COLUMN',@level2name=@col20Name
			else
				EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=@col20Decr , @level0type=N'SCHEMA',@level0name=@schema
					, @level1type=N'TABLE',@level1name=@tabName, @level2type=N'COLUMN',@level2name=@col20Name
end
GO


Промер использования:
execute [sysAdd].[AddColumnDescription]
			'sec', 'Users', 'Пользователи' 
			, 'Name', 'Имя пользователя (Уникально среди всех активных)'
			, 'Password', 'Пароль Sha256'
			, 'EMail', 'Почта'
			, 'IsEnable', 'вкл/выкл'
execute [sysAdd].[AddColumnDescription]
			'sec', 'Users', null 
			, 'ID', 'идентификатор пользователя'

Разумеется я ленив

и писать даже шаблон для новых таблиц, мне лень, и я решил написать простой скрипт, который выводит мне уже готовый шаблон.
Скрипт для шаблона
with tablesInfo as (
	select sch.name [sch], tab.name [tab], col.name [col], isnull(cast(ep.value as nvarchar), '') [description], tab.object_id [tab_object_id]
		from 
				sys.schemas sch
			inner join
				sys.tables tab
					on 
							tab.schema_id = sch.schema_id
						and sch.name not in ('sys')
			inner join
				sys.columns col
					on col.object_id = tab.object_id
			left outer join
				sys.extended_properties ep 
					on ep.major_id = tab.object_id and ep.minor_id = col.column_id
	)
,tabNames as (
	select distinct ti.sch, ti.tab, isnull(cast(ep.value as nvarchar), '') [description]
		from 
				tablesInfo ti
			left outer join
				sys.extended_properties ep 
					on ep.major_id = ti.[tab_object_id] and ep.minor_id = 0
	)
select tabNames.sch, tabNames.tab,
'execute [sysAdd].[AddColumnDescription]
			'''+tabNames.sch+''', '''+tabNames.tab+''', '''+tabNames.[description]+''' '+(select [sysAdd].[Concatenate]('
			, '''+ti.col+''', '''+ti.[description]+'''')
						from
							tablesInfo ti
						where ti.tab = tabNames.tab and ti.col != 'versionId')+'' [AddComment]
	from
		tabNames tabNames
	order by
		tabNames.sch, tabNames.tab


Ну а иногда с меня спрашивают описание БД. Разумеется, описание, кроме схемы, должно содержать описание таблиц и полей.
В общем выдёргиваем их очередным скриптом.
Скрипт для вытаскивания всех таблиц и их полей с описаниями
;with allDescr as (
	select col.name [objName], isnull(ep.value, '') [MS_Description], col.column_id, col.object_id
				, typ.name				
				, case 
						when col.max_length = -1 
							then 'max' 
						when typ.name = 'nvarchar' 
							then cast(col.max_length/2 as nvarchar)
						else '' end max_length
				, case when col.is_nullable = 0 then 'not null' else 'null' end is_nullable
				, cast(col.max_length as nvarchar) bytes
				
		from
				sys.schemas sch
			inner join
				sys.tables tab
					on 
							tab.schema_id = sch.schema_id
						and sch.name not in ('dbo', 'sys')			
			inner join
				sys.columns col 
					on col.object_id = tab.object_id
			inner join
				sys.types typ
					on
							typ.system_type_id = col.system_type_id
						and typ.user_type_id = col.user_type_id
			left outer join
				sys.extended_properties ep 
					on ep.major_id = tab.object_id and ep.minor_id = col.column_id
	union all
	select '[' + sch.name + '].[' + tab.name + ']', isnull(ep.value, ''), 0, tab.object_id, '', '', '', ''
		from
				sys.schemas sch
			inner join
				sys.tables tab
					on 
							tab.schema_id = sch.schema_id
						and sch.name not in ('dbo', 'sys')						
			left outer join
				sys.extended_properties ep 
					on ep.major_id = tab.object_id and ep.minor_id = 0)
select [objName],[MS_Description], name,  max_length, is_nullable, bytes from allDescr
	order by object_id, column_id




Спасибо за внимание.
PS: Если вы знаете более простые способы решения описанных выше проблем с комментариями в SQL буду рад прочитать о них в комментариях.
Теги:
MSSQL, комментариии

Данная статья не подлежит комментированию, поскольку её автор ещё не является полноправным участником сообщества. Вы сможете связаться с автором только после того, как он получит приглашение от кого-либо из участников сообщества. До этого момента его username будет скрыт псевдонимом.