教您查詢SQL字段屬性的sql語句寫法
作者：佚名
下文對查詢SQL字段屬性的sql語句寫法進行了詳細的闡述說明，該SQL腳本供您參考學習，希望對您能夠有所幫助。
想要查看SQL字段屬性，應該如何實現呢？下面為您介紹的就是查詢SQL字段屬性的sql語句寫法，希望對您學習SQL字段屬性方面有所啟迪。
- SELECT sysobjects.name AS tableName, syscolumns.name AS field, properties.[value] AS fieldRemark, systypes.name AS type,
- syscolumns.length, ISNULL(COLUMNPROPERTY(syscolumns.id, syscolumns.name, 'Scale'), 0) AS [decimal], syscolumns.isnullable AS isnulls,
- CASE WHEN syscomments.text IS NULL THEN '' ELSE syscomments.text END AS [Default],
- CASE WHEN COLUMNPROPERTY(syscolumns.id, syscolumns.name, 'IsIdentity') = 1 THEN 'Y' ELSE 'N' END AS [id], 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 'Y' ELSE 'N' END AS PK FROM syscolumns INNER JOIN sysobjects ON sysobjects.id = syscolumns.id INNER JOIN
- systypes ON syscolumns.xtype = systypes.xtype LEFT OUTER JOIN sysproperties properties ON syscolumns.id = properties.id AND
- syscolumns.colid = properties.smallid LEFT OUTER JOIN sysproperties ON sysobjects.id = sysproperties.id AND
- sysproperties.smallid = 0 LEFT OUTER JOIN syscomments ON syscolumns.cdefault = syscomments.id
- WHERE (sysobjects.xtype = 'U') and systypes.name<>'sysname' order by sysobjects.name
- 使用：把這段代碼拷貝到查詢分析器內選中相關查詢的數據庫名即可。
- //符合我自己使用的查詢數據庫字典方法：SQL server 200
- SELECT sysobjects.name 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 'K' ELSE '' END AS PK,--是否是主鍵
- syscolumns.name AS 字段名,
- properties.[value] AS 字段描述,
- systypes.name AS 數據類型,
- syscolumns.length AS 長度,
- CASE syscolumns.isnullable WHEN '1' THEN '是' ELSE '否'END AS 允許為空,
- CASE WHEN syscomments.text IS NULL THEN '' ELSE syscomments.text END AS 默認值
- FROM syscolumns INNER JOIN
- sysobjects ON sysobjects.id = syscolumns.id INNER JOIN
- systypes ON syscolumns.xtype = systypes.xtype LEFT OUTER JOIN
- sysproperties properties ON syscolumns.id = properties.id AND
- syscolumns.colid = properties.smallid LEFT OUTER JOIN
- sysproperties ON sysobjects.id = sysproperties.id AND sysproperties.smallid = 0 LEFT OUTER JOIN
- syscomments ON syscolumns.cdefault = syscomments.id
- WHERE (sysobjects.xtype = 'U') and systypes.name<>'sysname'
- order by sysobjects.name desc
【編輯推薦】
責任編輯：段燃
來源：
互聯網
