教您查詢SQL字段屬性的sql語(yǔ)句寫法
作者：佚名
下文對(duì)查詢SQL字段屬性的sql語(yǔ)句寫法進(jìn)行了詳細(xì)的闡述說(shuō)明，該SQL腳本供您參考學(xué)習(xí)，希望對(duì)您能夠有所幫助。
想要查看SQL字段屬性，應(yīng)該如何實(shí)現(xiàn)呢？下面為您介紹的就是查詢SQL字段屬性的sql語(yǔ)句寫法，希望對(duì)您學(xué)習(xí)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
- 使用：把這段代碼拷貝到查詢分析器內(nèi)選中相關(guān)查詢的數(shù)據(jù)庫(kù)名即可。
- //符合我自己使用的查詢數(shù)據(jù)庫(kù)字典方法：SQL server 200
- SELECT sysobjects.name AS 表名,--獲取數(shù)據(jù)庫(kù)表名
- 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 數(shù)據(jù)類型,
- syscolumns.length AS 長(zhǎng)度,
- CASE syscolumns.isnullable WHEN '1' THEN '是' ELSE '否'END AS 允許為空,
- CASE WHEN syscomments.text IS NULL THEN '' ELSE syscomments.text END AS 默認(rèn)值
- 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
【編輯推薦】
責(zé)任編輯：段燃
來(lái)源：
互聯(lián)網(wǎng)
