CODEWITHSUNDEEP
c#sql-serverschema
Chris Knight
Chris Knight

Reputation: 1476

Retrieve schema of User Defined Table Type from C#

I am wondering if there is a way in C# to retrieve the schema of a User-Defined Table Type from a SQL database instance. I see that the DbConnection class provides a GetSchema() method for getting table schema information, but I have not been able to figure out a way to get schema information for a User-Defined Table Type. What I am interested in retrieving is the name of the type, and information about the columns like data type, nullable, name, etc. Does anyone have experience doing this? Thanks!

NOTE: This question is NOT asking how to pass a User-Defined Table Type as a parameter to a stored procedure from C#

Upvotes: 2

Views: 2672

Answers (4)

RAJESH JAIN
RAJESH JAIN

Reputation: 106

        using (var connection = CreateConnection())
        {
            connection.Open();
            UserDefinedSchemaTable = connection.GetSchema("StructuredTypeMembers", new string[] { null, null, null,null });
        }

Upvotes: 0

VIVEK P S
VIVEK P S

Reputation: 315

Not a perfect solution. But this will work. Assuming your used defined table type name is "UDT_Sample"

DECLARE @UDT UDT_Sample
SELECT * INTO tempUDT FROM @UDT
EXECUTE sp_columns 'tempUDT'
DROP TABLE tempUDT

Upvotes: 1

Ben Thul
Ben Thul

Reputation: 32697

Use SMO. I'm just a DBA, so Powershell is my tool of choice, but the concepts should be immediately transferable:

import-module sqlps -disablenamechecking;
$s = new-object microsoft.sqlserver.management.smo.server '.';
$db = $s.databases['myDatabase'];

$db.UserDefinedTableTypes | select schema, name;

Once you have a UserDefinedTableType SMO object, you can get use the Columns method on a given type, which will return a collection of Column objects. Each of those will have all the relevant information for the columns (name, type, nullability, etc)

Upvotes: 0

Saravana Kumar
Saravana Kumar

Reputation: 3729

Use the procedure

sp_columns 'TableName'

to get the schema information in SqlServer.

Upvotes: 0

Related Questions