SQL Server:提取表元数据(描述,字段及其数据类型) [英] SQL Server: Extract Table Meta-Data (description, fields and their data types)
问题描述
我正在尝试找到一种方法来提取有关SQL Server(2008)中我的表的信息.
我需要的数据包括表的说明(从属性"窗口的描述"属性中填充),该表的字段列表以及它们各自的数据类型.
I am trying to find a way to extract information about my tables in SQL Server (2008).
The data I need needs to include the description of the table (filled from the Description property in the Properties Window), a list of fields of that table and their respective data types.
有什么方法可以提取此类元数据?我想我必须使用一些sys
sp,但是我不确定是哪个.
Is there any way I can extract such meta-data? I presume I have to use some sys
sp but I'n not sure which one.
推荐答案
要获取描述数据,很遗憾,您必须使用sysobjects/syscolumns来获取ID:
To get the description data, you unfortunately have to use sysobjects/syscolumns to get the ids:
SELECT u.name + '.' + t.name AS [table],
td.value AS [table_desc],
c.name AS [column],
cd.value AS [column_desc]
FROM sysobjects t
INNER JOIN sysusers u
ON u.uid = t.uid
LEFT OUTER JOIN sys.extended_properties td
ON td.major_id = t.id
AND td.minor_id = 0
AND td.name = 'MS_Description'
INNER JOIN syscolumns c
ON c.id = t.id
LEFT OUTER JOIN sys.extended_properties cd
ON cd.major_id = c.id
AND cd.minor_id = c.colid
AND cd.name = 'MS_Description'
WHERE t.type = 'u'
ORDER BY t.name, c.colorder
您可以使用info-schema来做到这一点,但是您必须串联等才能调用OBJECT_ID()-那有什么意义呢?
You can do it with info-schema, but you'd have to concatenate etc to call OBJECT_ID() - so what would be the point?
这篇关于SQL Server:提取表元数据(描述,字段及其数据类型)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!