'取得現有 MS SQL 的資料表清單 (含擴充屬性的描述)
SELECT o.name AS table_name, ep.value AS 說明
FROM sys.objects O
LEFT JOIN
sys.extended_properties AS ep
ON o.object_id = ep.major_id
WHERE 1 = 1
AND ISNULL (minor_id, 0) = 0
AND O.name NOT LIKE 'sys%'
AND O.type = 'U'
ORDER BY O.name
'取得現有 MS SQL 的資料表欄位清單
SELECT b.colorder,
a.name AS table_name,
b.name AS FNAME,
(SELECT convert (VARCHAR, value)
FROM ::
fn_listextendedproperty (NULL,
'user',
'dbo',
'table',
a.name,
'column',
b.name
))
AS DESCRIPT,
b.xtype,
(SELECT TOP 1
c.name
FROM systypes c
WHERE c.xtype = b.xtype)
AS ctype,
b.length,
b.isnullable,
''
FROM sysobjects a, syscolumns b
WHERE a.id = b.id AND A.type = 'U'
ORDER BY a.name, b.colorder
沒有留言:
張貼留言