sp_addextendedproperty 로 집어넣는 "description(설명)" 항목의 값을 불러오는 방법을 알고 싶습니다.
INFORMATION_SCHEMA.COLUMNS 이나 다른 뷰를 찾아봤으나 내공 부족으로 찾지를 못하겠습니다.
조언 부탁드립니다. T.T
CREATE PROCEDURE dbo.usp_TableData @Table varchar(50), @Schema varchar(20), @Cols varchar(500) = '' AS /* Example --for the columns EmployeeID,MaritalStatus,Gender,ModifiedDate --of "Employee" table exec dbo.usp_TableData 'Employee','HumanResources', 'EmployeeID,MaritalStatus,Gender,ModifiedDate'
--for all columns of "Employee" table exec dbo.usp_TableData 'Employee','HumanResources' */ DECLARE @Select nvarchar (800) BEGIN set @Select = '' set @Select = @Select + ' select syscolumns.[Name] as ColName,[value] as ColDescr, ' + ' xtype,xusertype,length from syscolumns, ' + '::fn_listextendedproperty (default, ''schema'', ''' + @Schema + ''', ''table'',''' + @Table + ''',''column'',null) ' + ' where syscolumns.id = ' + ' (select sysobjects.id from sysobjects where type=''U'' ' + ' and [name] = ''' + @Table + ''') ' + ' and fn_listextendedproperty.objname = syscolumns.[name] ' + ' and fn_listextendedproperty.[name] = ''MS_Description'' '
if ltrim(rtrim(@Cols)) = '' begin set @Select = @Select + ' order by syscolumns.colid ' end else begin set @Select = @Select + ' and syscolumns.Name in (''' + replace (@Cols,',',''',''') + ''')' + ' order by syscolumns.colid ' end exec sp_executesql @Select END GO