Get columns size along with column name and datatype

Question

I am trying to fetch columns, datatypes and size of columns of selected table and database from SQL Server database.

So far I am successful in fetching column names and datatypes but I want to fetch size also.

Example: Consider I have 2 columns in SQL Server database tables like below:

1) Name nvarchar(30)
3) Salary decimal(6,2)

Now I am able to fetch Name and salary with nvarchar and salary datatypes but size is not coming.

Code:

String[] columnRestrictions = new String[4];
columnRestrictions[0] = 'MyDb';
columnRestrictions[1] = 'dbo';
columnRestrictions[2] = 'Employee';
using (SqlConnection con = new SqlConnection("MyConnectionString"))
{
    con.Open();
    var columns = con.GetSchema("Columns", columnRestrictions).AsEnumerable()
        .Select(c => new (c[3].ToString(), c.Field<string>("DATA_TYPE"))).ToList();

Result I am getting is like below:

Name nvarchar
Salary decimal

Expected result:

1) Name nvarchar(30)
3) Salary decimal(6,2)

Source code: Reference

Is it possible to get above result?

Stephen · Accepted Answer · 2016-12-09 15:02:13Z

1

you can use CHARACTER_OCTET_LENGTH for char/varchar and the Numeric_precision + Numeric_Scale for numeric types.

answered Dec 9, 2016 at 15:02

Stephen

1,5421 gold badge9 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ivan Stoev Over a year ago

Correct in general, except for char/nvarchar I would rather say using character_maximum_length column value.

Collectives™ on Stack Overflow

Get columns size along with column name and datatype

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related