擷取sqlserver資料字典的完整sql

標籤：

SELECT
sysobjects.name AS 表名稱 , --------------as 的作用:為欄位起一個別名
--sysproperties.[value] AS 表說明 , ----------[ ]方括弧的作用:為了避免和關鍵字衝突
syscolumns.name AS 欄位名稱 ,
--properties.[value] AS 欄位說明 ,
systypes.name AS 欄位類型 ,
syscolumns.length AS 欄位長度 ,
ISNULL(COLUMNPROPERTY(syscolumns.id, syscolumns.name,‘Scale‘), 0) AS 小數位元 ,
--isnull---(待測資料,傳回值).當待測資料為空白時返回傳回值,當大策資料為非null時,反回待測資料
CASE WHEN syscolumns.isnullable=0
THEN ‘‘
ELSE ‘ √ ‘
END AS 是否為空白 ,
CASE WHEN syscomments.text IS NULL
THEN ‘‘ ELSE syscomments.text
END AS 預設值 ,
CASE WHEN COLUMNPROPERTY(syscolumns.id, syscolumns.name, ‘IsIdentity‘)= 1
THEN ‘ √ ‘ ELSE ‘‘
END AS 遞增欄位 ,
CASE WHEN sysindexes.name IS NULL
THEN ‘‘
ELSE sysindexes.name
END AS 索引名稱 ,
CASE WHEN sysindexkeys.keyno IS NULL
THEN ‘‘
ELSE CONVERT(VARCHAR(10),sysindexkeys.keyno )
END AS 索引位置 ,
CASE WHEN sysindexes.indid=1
THEN ‘ 叢集索引 ‘
WHEN sysindexes.indid>1 AND sysindexes.indid<>255
THEN ‘ 非叢集索引 ‘
WHEN sysindexes.indid IS NULL
THEN ‘‘
ELSE
‘ 其他 ‘
END AS 索引類型 ,
CASE WHEN EXISTS
(SELECT 1
FROM sysobjects
WHERE xtype = ‘PK‘ AND name IN
(SELECT name
FROM sysindexes
WHERE indid IN
(SELECT indid
FROM sysindexkeys
WHERE id = syscolumns.id AND colid = syscolumns.colid)))
THEN ‘ √ ‘ ELSE ‘‘
END AS 主鍵 ,
CASE WHEN sysforeignkeys.constid IS NULL
THEN ‘‘
ELSE ‘ √ ‘
END AS 外健
FROM syscolumns -- 資料表欄位
INNER JOIN sysobjects -- 資料對象
ON sysobjects.id = syscolumns.id
INNER JOIN systypes -- 資料類型
ON syscolumns.xtype = systypes.xtype

LEFT OUTER JOIN syscomments -- 注釋資訊
ON syscolumns.cdefault = syscomments.id
LEFT OUTER JOIN sysindexkeys -- 索引中的鍵或列的資訊
ON sysindexkeys.id = syscolumns.id
AND sysindexkeys.colid = syscolumns.colid
LEFT OUTER JOIN sysindexes -- 資料庫 索引表
ON sysindexes.id = sysindexkeys.id
AND sysindexes.indid = sysindexkeys.indid
LEFT OUTER JOIN sysforeignkeys
ON sysforeignkeys.fkeyid = syscolumns.id
AND sysforeignkeys.fkey = syscolumns.colid
WHERE (sysobjects.xtype = ‘U‘)
order by sysobjects.id,syscolumns.colid

擷取sqlserver資料字典的完整sql