WITH co
AS ( SELECT CONVERT(VARCHAR, SCHEMA_NAME(d.uid)) AS sch,
CONVERT(VARCHAR, d.name) tb, a.colorder colid,
CONVERT(VARCHAR, a.name) col,
CONVERT(VARCHAR, g.[value]) AS coldesc,
CONVERT(VARCHAR, g.name) AS prop
FROM syscolumns a
LEFT JOIN systypes b ON a.xtype = b.xusertype
INNER JOIN sysobjects d ON a.id = d.id
AND d.xtype = 'U'
AND d.name <> 'dtproperties'
INNER JOIN sys.extended_properties g ON a.id = g.major_id
AND a.colid = g.minor_id
WHERE d.[name] <> 'table_desc' --你要查看的表名,注释掉,查看当前数据库所有表的字段信息
)
SELECT '
begin try
EXEC sys.sp_addextendedproperty @name = N''' + prop + ''', @value = N'''
+ coldesc + ''',
@level0type = N''SCHEMA'', @level0name = N''' + sch + ''',
@level1type = N''TABLE'', @level1name = N''' + tb
+ ''', @level2type = N''COLUMN'',
@level2name = N''' + col + '''
end try
begin catch
print ERROR_MESSAGE()
end catch
' AS ad
FROM co
AS ( SELECT CONVERT(VARCHAR, SCHEMA_NAME(d.uid)) AS sch,
CONVERT(VARCHAR, d.name) tb, a.colorder colid,
CONVERT(VARCHAR, a.name) col,
CONVERT(VARCHAR, g.[value]) AS coldesc,
CONVERT(VARCHAR, g.name) AS prop
FROM syscolumns a
LEFT JOIN systypes b ON a.xtype = b.xusertype
INNER JOIN sysobjects d ON a.id = d.id
AND d.xtype = 'U'
AND d.name <> 'dtproperties'
INNER JOIN sys.extended_properties g ON a.id = g.major_id
AND a.colid = g.minor_id
WHERE d.[name] <> 'table_desc' --你要查看的表名,注释掉,查看当前数据库所有表的字段信息
)
SELECT '
begin try
EXEC sys.sp_addextendedproperty @name = N''' + prop + ''', @value = N'''
+ coldesc + ''',
@level0type = N''SCHEMA'', @level0name = N''' + sch + ''',
@level1type = N''TABLE'', @level1name = N''' + tb
+ ''', @level2type = N''COLUMN'',
@level2name = N''' + col + '''
end try
begin catch
print ERROR_MESSAGE()
end catch
' AS ad
FROM co