WITH tbl AS (
SELECT tb =CONVERT(VARCHAR, case when a.colorder = 1 then d.name
else '' END),
tbdesc = CONVERT(VARCHAR,case when a.colorder = 1 then isnull(f.value, '')
else '' END)
,CONVERT(VARCHAR,f.name) AS prop,
CONVERT(VARCHAR,SCHEMA_NAME(d.uid)) AS sch
FROM syscolumns a
inner join sysobjects d
on a.id = d.id
and d.xtype = 'U'
and d.name <> 'sys.extended_properties'
join sys.extended_properties f
on a.id = f.major_id
and f.minor_id = 0
Where (case when a.colorder = 1 then d.name else '' end) <>''
)
SELECT
'
begin try
EXEC sys.sp_addextendedproperty @name = N'''+prop+''', @value = N'''+tbdesc+''',
@level0type = N''SCHEMA'', @level0name = N'''+sch+''',
@level1type = N''TABLE'', @level1name = N'''+tb+'''
end try
begin catch
print ERROR_MESSAGE()
end catch
'
AS ad
FROM tbl
SELECT tb =CONVERT(VARCHAR, case when a.colorder = 1 then d.name
else '' END),
tbdesc = CONVERT(VARCHAR,case when a.colorder = 1 then isnull(f.value, '')
else '' END)
,CONVERT(VARCHAR,f.name) AS prop,
CONVERT(VARCHAR,SCHEMA_NAME(d.uid)) AS sch
FROM syscolumns a
inner join sysobjects d
on a.id = d.id
and d.xtype = 'U'
and d.name <> 'sys.extended_properties'
join sys.extended_properties f
on a.id = f.major_id
and f.minor_id = 0
Where (case when a.colorder = 1 then d.name else '' end) <>''
)
SELECT
'
begin try
EXEC sys.sp_addextendedproperty @name = N'''+prop+''', @value = N'''+tbdesc+''',
@level0type = N''SCHEMA'', @level0name = N'''+sch+''',
@level1type = N''TABLE'', @level1name = N'''+tb+'''
end try
begin catch
print ERROR_MESSAGE()
end catch
'
AS ad
FROM tbl