首页 文章

令人困惑的行为 - 使用向导创建的SQL Server信息架构和列

提问于
浏览 次
1

编辑：我尝试了这个代码，这是用户提到的一部分，它正确返回字段 .

SELECT * FROM INFORMATION_SCHEMA.TABLES 
        JOIN INFORMATION_SCHEMA.COLUMNS on 
        INFORMATION_SCHEMA.TABLES.TABLE_NAME = 
        INFORMATION_SCHEMA.COLUMNS.TABLE_NAME
WHERE INFORMATION_SCHEMA.TABLES.TABLE_NAME = 'myTable'

前一个查询中有一些内容适用于某些数据库但不适用于其他数据库

EDIT2：

我已经尝试设置Profiler跟踪来检查通过向导创建的列，如用户建议的那样 . 显然，它使用相同的模式 .

ALTER TABLE dbo.table1 ADD
field2 nchar(10) NULL

我正在使用SQL Server 2014，并使用向导创建数据库，我的意思是，不是通过代码，而是通过使用UI .

然后，我尝试获取有关其表的列的元数据信息..并且不返回任何结果 . 我查询INFORMATION_SCHEMA .

我已经尝试对我用代码创建的数据库使用相同的查询，它工作正常，它返回列及其值等 .

我试过通过互联网查找它，但似乎无法想出任何适合这类情况的东西 . 我觉得它有点奇怪，它会对代码和UI创建的列产生影响 . 如果有人知道为什么这样的事情会发生，或者看起来很熟悉，我会很感激它的一些亮点:)

这是我用来检索列元数据的代码：

USE 'database'
SELECT infSch.TABLE_CATALOG,
       infSch.TABLE_NAME,
       sysCols.name,
       infSch.ORDINAL_POSITION,
       sysCols.is_nullable,
       infSch.DATA_TYPE,
       infSch.CHARACTER_MAXIMUM_LENGTH,
       sysCols.is_identity,
       IIF(infSchCons.CONSTRAINT_TYPE = 'PRIMARY KEY', 1, 0),
       IIF(infSchCons.CONSTRAINT_TYPE = 'FOREIGN KEY', 1, 0),
       IIF(infSchCons.CONSTRAINT_TYPE = 'UNIQUE', 1, 0),
       IIF(infSchCons.CONSTRAINT_TYPE = 'CHECK', 1, 0)
FROM sys.columns as sysCols 
RIGHT JOIN INFORMATION_SCHEMA.COLUMNS as infSch on sysCols.name = 
     infSch.COLUMN_NAME
RIGHT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS as infSchCons on 
     infSch.TABLE_NAME = infSchCons.TABLE_NAME

结果：

  • 如果表和列是由代码创建的 - >它返回给定数据库的所有列及其元数据

  • 如果使用UI和窗口创建表和列 - >它不返回任何内容，只返回一组空结果

sql-server database tsql information-schema database-metadata

1 回答

  • 1

    右连接改变了查询的性质，并且可以通过反转表的顺序来重写它们 . 避免正确的连接....试试这个：

    SELECT infSch.TABLE_CATALOG,
       infSch.TABLE_NAME,
       sysCols.name,
       infSch.ORDINAL_POSITION,
       sysCols.is_nullable,
       infSch.DATA_TYPE,
       infSch.CHARACTER_MAXIMUM_LENGTH,
       sysCols.is_identity,
       IIF(infSchCons.CONSTRAINT_TYPE = 'PRIMARY KEY', 1, 0),
       IIF(infSchCons.CONSTRAINT_TYPE = 'FOREIGN KEY', 1, 0),
       IIF(infSchCons.CONSTRAINT_TYPE = 'UNIQUE', 1, 0),
       IIF(infSchCons.CONSTRAINT_TYPE = 'CHECK', 1, 0)
FROM sys.columns as sysCols 
LEFT JOIN INFORMATION_SCHEMA.COLUMNS as infSch on sysCols.name = 
     infSch.COLUMN_NAME
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS as infSchCons on 
     infSch.TABLE_NAME = infSchCons.TABLE_NAME

    该查询不会将结果限制为仅存在约束的结果 .

    尝试比较这些：

    select count(*) from (

    SELECT
        infSchCons.*
    FROM sys.columns as sysCols 
    right JOIN INFORMATION_SCHEMA.COLUMNS as infSch on sysCols.name = infSch.COLUMN_NAME
    right JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS as infSchCons on infSch.TABLE_NAME = infSchCons.TABLE_NAME

    ) x
;

select count(*) from (

    SELECT
        infSchCons.*
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS infSchCons
    left JOIN INFORMATION_SCHEMA.COLUMNS AS infSch ON infSchCons.TABLE_NAME = infSch.TABLE_NAME
    left JOIN sys.columns AS sysCols ON infSch.COLUMN_NAME = sysCols.name

    ) x
;

    最后一个查询是对原始from子句的重写，这里更容易（在我看来）看到你必须对要返回的任何行有约束 .

    回复于

相关问题