How to become an author
Search
Write a publication
Pull to refresh

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

SQL*
Invite pending
Привет Хабр!
Некоторые из нас пишут и/или поддерживают БД на основе СУБД 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 буду рад прочитать о них в комментариях.
Tags:
Hubs:
You can’t comment this publication because its author is not yet a full member of the community. You will be able to contact the author only after he or she has been invited by someone in the community. Until then, author’s username will be hidden by an alias.

Your account

Sections

Information

Services

Support
© 2006–2025, Habr