首先遇到的问题是这样的,我们有两个表,比如A 和 A_ARCH, A_ARCH 的结构是和A一模一样的,但是是在两个schema里的现在用这条语句
select COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH as DATA_LENGTH from
INFORMATION_SCHEMA.COLUMNS
where upper(TABLE_NAME)='A' and upper(TABLE_SCHEMA)='S'
except
select COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH as DATA_LENGTH from
INFORMATION_SCHEMA.COLUMNS
where upper(TABLE_NAME)='A_ARCH' and upper(TABLE_SCHEMA)='S_ARCH' 查出来有字段不一致,但是结构应该是一致的(数据库在客户那边,只有让PS的去验证)
现在考虑好像是因为INFORMATION_SCHEMA.COLUMNS没有refresh,因为我们是先drop table然后在create arch的表
但是google了好久,也没有看到相关的topic 。
想问一下有没有这种可能?
还有怎么查看这个INFORMATION_SCHEMA.COLUMNS视图的DDL语句?
还有一个问题,我看文档上说INFORMATION_SCHEMA.COLUMNS视图的TABLE_SCHEMA这个字段解释如下
Name of schema that contains the table.
Important
Do not use INFORMATION_SCHEMA views to determine the schema of an object. The only reliable way to find the schema of an object is to query the sys.objects catalog view. INFORMATION_SCHEMA views could be incomplete since they are not updated for all new features.请问这个会出现不正确的情况吗?谢谢
select COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH as DATA_LENGTH from
INFORMATION_SCHEMA.COLUMNS
where upper(TABLE_NAME)='A' and upper(TABLE_SCHEMA)='S'
except
select COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH as DATA_LENGTH from
INFORMATION_SCHEMA.COLUMNS
where upper(TABLE_NAME)='A_ARCH' and upper(TABLE_SCHEMA)='S_ARCH' 查出来有字段不一致,但是结构应该是一致的(数据库在客户那边,只有让PS的去验证)
现在考虑好像是因为INFORMATION_SCHEMA.COLUMNS没有refresh,因为我们是先drop table然后在create arch的表
但是google了好久,也没有看到相关的topic 。
想问一下有没有这种可能?
还有怎么查看这个INFORMATION_SCHEMA.COLUMNS视图的DDL语句?
还有一个问题,我看文档上说INFORMATION_SCHEMA.COLUMNS视图的TABLE_SCHEMA这个字段解释如下
Name of schema that contains the table.
Important
Do not use INFORMATION_SCHEMA views to determine the schema of an object. The only reliable way to find the schema of an object is to query the sys.objects catalog view. INFORMATION_SCHEMA views could be incomplete since they are not updated for all new features.请问这个会出现不正确的情况吗?谢谢
呵呵,我就是想问一下
1:表更新后,系统视图INFORMATION_SCHEMA.COLUMNS会不会没有更新
2:INFORMATION_SCHEMA.COLUMNS这个视图的TABLE_SCHEMA这个字段会不会不正确(根据文档上的解释)谢谢
没有任何索引,分区等等,连主键都没有,就是一个表结构和我们生产系统中表一模一样,然后什么约束都没有的表,但是执行上面那条SQL有返回值
但是我再网上看到这个视图是根据sys.object来的啊
http://connect.microsoft.com/SQLServer/feedback/details/686118/doc-information-schema-tables-gives-improper-warning-about-schema#details