• 售前

  • 售后

热门帖子
入门百科

显示 Sql Server 中全部表中的信息

[复制链接]
四龙全缴 显示全部楼层 发表于 2021-10-25 20:23:13 |阅读模式 打印 上一主题 下一主题
体现某个Sql Server某个数据库中全部表或视图的信息
sql server 2000 与 2005 不同 差异在于 赤色字部门
以下语句为获取全部表信息,更换绿色黑体字"U"为"V"为获取全部视图信息。
Sql Server 2000 版本
SELECT Sysobjects.name AS TABLE_NAME, syscolumns.Id, syscolumns.name AS COLUMN_NAME,
systypes.name AS DATA_TYPE, syscolumns.length as CHARACTER_MAXIMUM_LENGTH,
sysproperties.[value] AS COLUMN_DESCRIPTION,  syscomments.text as
COLUMN_DEFAULT,syscolumns.isnullable as IS_NULLABLE FROM syscolumns
INNER JOIN systypes
    ON syscolumns.xtype = systypes.xtype
    LEFT JOIN sysobjects ON syscolumns.id = sysobjects.id
   LEFT OUTER JOIN sysproperties ON
   ( sysproperties.smallid = syscolumns.colid
     AND sysproperties.id = syscolumns.id)
   LEFT OUTER JOIN syscomments ON syscolumns.cdefault = syscomments.id
   WHERE syscolumns.id IN  
    (SELECT id FROM SYSOBJECTS WHERE xtype = 'U') AND (systypes.name <> 'sysname')
    ORDER BY syscolumns.colid
Sql Server 2005版本
SELECT Sysobjects.name AS TABLE_NAME, syscolumns.Id, syscolumns.name AS COLUMN_NAME,
systypes.name AS DATA_TYPE, syscolumns.length as CHARACTER_MAXIMUM_LENGTH,
sys.extended_properties.[value] AS COLUMN_DESCRIPTION,  syscomments.text as
COLUMN_DEFAULT,syscolumns.isnullable as IS_NULLABLE FROM syscolumns
INNER JOIN systypes
    ON syscolumns.xtype = systypes.xtype
    LEFT JOIN sysobjects ON syscolumns.id = sysobjects.id
   LEFT OUTER JOIN sys.extended_properties ON
   ( sys.extended_properties.minor_id = syscolumns.colid
     AND sys.extended_properties.major_id = syscolumns.id)
   LEFT OUTER JOIN syscomments ON syscolumns.cdefault = syscomments.id
   WHERE syscolumns.id IN  
    (SELECT id FROM SYSOBJECTS WHERE xtype = 'U') AND (systypes.name <> 'sysname')
    ORDER BY syscolumns.colid

参考:http://www.devx.com/tips/Tip/31235?type=kbArticle&trk=MSCP

帖子地址: 

回复

使用道具 举报

分享
推广
火星云矿 | 预约S19Pro,享500抵1000!
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

草根技术分享(草根吧)是全球知名中文IT技术交流平台,创建于2021年,包含原创博客、精品问答、职业培训、技术社区、资源下载等产品服务,提供原创、优质、完整内容的专业IT技术开发社区。
  • 官方手机版

  • 微信公众号

  • 商务合作