当前位置：文江博客话题详情

查找 SQL 2000 数据库中所有可为 Null 的列

发布于 2024-07-26 22:15:15 字数 26 浏览 3 评论 0原文

如何找出整个数据库中允许插入的空值列？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

不甘平庸 2024-08-02 22:15:16

我手头没有 sql，但查询是这样的

  SELECT * FROM information_schema.columns WHERE is_nullable = 'YES'

一般情况下，搜索此标准视图，以获取有关数据库架构和结构的所有元数据信息；还有很多其他的（information_schema.tables、information_schema.constraints 等）

I don't have sql at hand, but the query goes something like this

  SELECT * FROM information_schema.columns WHERE is_nullable = 'YES'

In general, search for this stardard view, for all the metadata info about your schema and structure of the database; there are many others (information_schema.tables, information_schema.constraints, etc)

回复收藏 0 原文

傻比既视感 2024-08-02 22:15:16

那些只想查看基表（而不是视图）中的列的人应该使用 INFORMATION_SCHEMA.TABLES 加入。我还想排除系统表 sysdiagrams 。

查询

SELECT
     c.TABLE_NAME,
     COLUMN_NAME,
     DATA_TYPE
FROM
     INFORMATION_SCHEMA.COLUMNS AS c
     JOIN INFORMATION_SCHEMA.TABLES AS t ON t.TABLE_NAME = c.TABLE_NAME
WHERE
     is_nullable = 'YES' AND
     TABLE_TYPE = 'BASE TABLE' AND
     c.TABLE_NAME != 'sysdiagrams'
ORDER BY
     c.TABLE_NAME,
     COLUMN_NAME

如果跨模式或表目录有重复的表名称，则还应该在联接中包含这些字段，如此处的答案所示：

区分 INFORMATION_SCHEMA.COLUMNS 中的表和视图。

Those who only want to see columns from base tables (not views) should join with INFORMATION_SCHEMA.TABLES. I also like to exclude the system table sysdiagrams.

Query

SELECT
     c.TABLE_NAME,
     COLUMN_NAME,
     DATA_TYPE
FROM
     INFORMATION_SCHEMA.COLUMNS AS c
     JOIN INFORMATION_SCHEMA.TABLES AS t ON t.TABLE_NAME = c.TABLE_NAME
WHERE
     is_nullable = 'YES' AND
     TABLE_TYPE = 'BASE TABLE' AND
     c.TABLE_NAME != 'sysdiagrams'
ORDER BY
     c.TABLE_NAME,
     COLUMN_NAME

If you have duplicate table names across schemas or table catalogs, you should involve those fields in the join as well, as shown in the answers here:

Differentiating tables and views in INFORMATION_SCHEMA.COLUMNS.

回复收藏 0 原文

~没有更多了~