我正在尝试寻找一种方法来提取SQL Server (2008)中有关我的表的信息。
我需要的数据需要包括表的描述(从属性窗口中的Description属性填充)、该表的字段的列表以及它们各自的数据类型。
有没有办法提取这样的元数据?我想我必须使用一些sys
sp,但我不确定是哪一个。
发布于 2009-05-20 11:35:34
不幸的是,要获取描述数据,您必须使用sysobjects/syscolumn来获取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来做这件事,但是你必须连接etc来调用OBJECT_ID() -那么重点是什么呢?
发布于 2009-05-20 11:26:20
有关表和列的一般信息可在以下表格中找到:
select * from INFORMATION_SCHEMA.TABLES
select * from INFORMATION_SCHEMA.COLUMNS
表描述是一个扩展属性,您可以从sys.extended_properties中查询:
select
TableName = tbl.table_schema + '.' + tbl.table_name,
TableDescription = prop.value,
ColumnName = col.column_name,
ColumnDataType = col.data_type
FROM information_schema.tables tbl
INNER JOIN information_schema.columns col
ON col.table_name = tbl.table_name
AND col.table_schema = tbl.table_schema
LEFT JOIN sys.extended_properties prop
ON prop.major_id = object_id(tbl.table_schema + '.' + tbl.table_name)
AND prop.minor_id = 0
AND prop.name = 'MS_Description'
WHERE tbl.table_type = 'base table'
发布于 2009-05-20 12:31:39
你可以试试sp_help <Name of object>
https://stackoverflow.com/questions/887370
复制相似问题