Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

GetSchema("StructuredTypeMembers") always returns TYPE_SCHEMA as [sys] #1498

Closed
tf-micwil opened this issue Feb 1, 2022 · 3 comments
Closed

Comments

@tf-micwil
Copy link
Contributor

When using GetSchema("StructuredTypeMembers", ...) to get the schema for User-Defined-Table-Types, the type schema returned is alway "sys".

To reproduce:

use MyTemp
go
create schema MySchema;
go
create type [dbo].[PostalCodeList] as table ([postalCode] varchar(10));
go
create type [MySchema].[PostalCodeList] as table ([postalCode] varchar(10), [country] char(2));
go
using System.Data;
using Microsoft.Data.SqlClient;
using var connection = new SqlConnection("Data Source=(local);Initial Catalog=MyTemp;Integrated Security=True;Encrypt=false");
connection.Open();
var schema = connection.GetSchema("StructuredTypeMembers", new string[] { null, null, "PostalCodeList" });
foreach (DataRow row in schema.Rows)
{
    Console.WriteLine(
        string.Format("{0}: [{1}].[{2}].[{3}]",
        row["Ordinal_Position"],
        row["TYPE_SCHEMA"],
        row["TYPE_NAME"],
        row["MEMBER_NAME"]));
}

Output:
1: [sys].[PostalCodeList].[postalCode]
1: [sys].[PostalCodeList].[postalCode]
2: [sys].[PostalCodeList].[country]

Expected results:
1: [dbo].[PostalCodeList].[postalCode]
1: [MySchema].[PostalCodeList].[postalCode]
2: [MySchema].[PostalCodeList].[country]

Looking at the SQL statement executed behind the scenes in SQL Profiler, it is returning the schema name from sys.objects.schema_id which always sys. The correct schema name should be obtained from sys.table_types.schema_id.

@JRahnama
Copy link
Contributor

JRahnama commented Feb 2, 2022

@tf-micwil we will investigate the issue and will get back to you shortly.

@ErikEJ
Copy link
Contributor

ErikEJ commented Jul 23, 2023

@JRahnama why is this still open?

@JRahnama
Copy link
Contributor

Apparently GitHub does not close it based on the fixing PR/ I have seen it in several places.

Closing as this was fixed in PR #1500

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

3 participants