T-Sql
數據庫類型“幾何”的一列導致 ssms 隱藏了兩個額外的列
如果我創建一個像
create table test2 (foo geometry)
foo 將按預期顯示在表格設計器中
但是當我查詢
sys.columns
列資訊時…select t.name as TABLE_NAME, c.name as COLUMN_NAME, c.precision as PRECISION, c.scale as SCALE, c.max_length as MAX_LENGTH, c.is_nullable as IS_NULLABLE, ty.name as DATA_TYPE FROM sys.columns c JOIN sys.tables t ON c.object_id = t.object_id JOIN sys.types ty on c.system_type_id = ty.system_type_id and ty.NAME != 'sysname' ORDER BY c.column_id
…我還得到了一列類型
hiearchyid
和geography
(使用相同的列名)。為什麼?
我可以更改我的
sys.columns
查詢以過濾掉那些偽列嗎?(我正在使用該查詢來重新創建
create table
查詢)。
請改用此查詢:
select t.name as TABLE_NAME, c.name as COLUMN_NAME, c.precision as PRECISION, c.scale as SCALE, c.max_length as MAX_LENGTH, c.is_nullable as IS_NULLABLE, ty.name as DATA_TYPE FROM sys.columns c JOIN sys.tables t ON c.object_id = t.object_id JOIN sys.types ty on c.user_type_id = ty.user_type_id and ty.NAME != 'sysname' ORDER BY c.column_id
連接基於user_type_id,因此結果與 GUI 相同。