CODEWITHSUNDEEP
c#sqlsql-server-2008
netX
netX

Reputation: 144

Retrieve size of field having varchar datatype in SQL Server using C#

I'm creating a project in ASP.NET using VS 2010 and SQL Server 2008.

I want to retrieve size of columns having varchar datatype.

e.g. 

Varchar(20)

I want to get 20 instead of maximum possible size for varchar using c# code .

Upvotes: 2

Views: 4942

Answers (6)

Ash K
Ash K

Reputation: 3631

The below method gets the column size of all the char columns (char, varchar etc.) of a table directly from the database.

You just need to provide the tableName and the connectionString.

// I took HUGE help from this Microsoft docs website: - AshishK
// https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnection.getschema?view=netframework-4.7.2#System_Data_SqlClient_SqlConnection_GetSchema_System_String_System_String___
public static Dictionary<string, int> GetColumnSizesOfTableFromDatabase(string tableName, string connectionString)
{
    var columnSizes = new Dictionary<string, int>();
            
    using (var connection = new SqlConnection(connectionString))
    {
        // Connect to the database then retrieve the schema information.  
        connection.Open();

        // You can specify the Catalog, Schema, Table Name, Column Name to get the specified column(s).
        // You can use four restrictions for Column, so you should create a 4 members array.
        String[] columnRestrictions = new String[4];

        // For the array, 0-member represents Catalog; 1-member represents Schema;
        // 2-member represents Table Name; 3-member represents Column Name.
        // Now we specify the Table_Name and Column_Name of the columns what we want to get schema information.
        columnRestrictions[2] = tableName;

        DataTable allColumnsSchemaTable = connection.GetSchema("Columns", columnRestrictions);

        foreach (DataRow row in allColumnsSchemaTable.Rows)
        {
            var columnName = row.Field<string>("COLUMN_NAME");
            var dataType = row.Field<string>("DATA_TYPE");
            var characterMaxLength = row.Field<int?>("CHARACTER_MAXIMUM_LENGTH");

            // I'm only capturing columns whose Datatype is "varchar" or "char", i.e. their CHARACTER_MAXIMUM_LENGTH won't be null.
            if(characterMaxLength != null)
            {
                columnSizes.Add(columnName, characterMaxLength.Value);
            }
        }

        connection.Close();
    }

    return columnSizes;
}

Upvotes: 0

marc_s
marc_s

Reputation: 754418

Try this:

public int GetColumnMaxLength(string tableName, string columnName)
{
    string query = @"SELECT max_length
                     FROM sys.columns c 
                     INNER JOIN sys.tables t ON t.object_id = c.object_id
                     WHERE t.Name = @TableName
                     AND c.Name = @ColumnName";

    int result = -1;

    using (SqlConnection conn = new SqlConnection("your-connection-string-here"))
    using (SqlCommand cmd = new SqlCommand(query, conn))
    {
        cmd.Parameters.Add("@TableName", SqlDbType.VarChar, 100).Value = tableName;
        cmd.Parameters.Add("@ColumnName", SqlDbType.VarChar, 100).Value = columnName;

        conn.Open();
        result = (int)cmd.ExecuteScalar();
        conn.Close();
    }

    return result;
}

Upvotes: 1

pravprab
pravprab

Reputation: 2293

information_schema.columns table gives the information try

SELECT table_catalog, 
       table_name, 
       column_name, 
       data_type, 
       character_maximum_length 
FROM   information_schema.columns 
WHERE  data_type = 'varchar'

Upvotes: 3

vino20
vino20

Reputation: 429

SELECT CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'YOUR_TABLE_NAME'

Upvotes: 0

Damir Arh
Damir Arh

Reputation: 17855

Here's another way to do it:

using(var conn = new SqlConnection("ConnectionString"))
{
    conn.Open();
    var cmd = new SqlCommand("SELECT * FROM Table WHERE 1 = 0", conn);
    var reader = cmd.ExecuteReader();
    var schema = reader.GetSchemaTable();
    var size = schema.AsEnumerable()
                     .Single(s => s.Field<string>("ColumnName") == "Column")
                     .Field<int>("ColumnSize");
    conn.Close();
}

Just replace ConnectionString, Table and Column with your own values.

Upvotes: 3

fyjham
fyjham

Reputation: 7034

Depending on your data framework you're using there may be a simpler supported way to do this, but simply going to SQL directly for the answer you can get these values from the sys tables.

An example of a query would be:

select t.name as tableName, c.name as columnName, c.max_length
from sys.columns c
join sys.tables t on c.object_id=t.object_id

Note that length is a number of bytes. For a varchar, this will correspond to characters. For other data types, this has different meanings (One example would be an nvarchar which stores each character in 2 bytes and hence tells you twice the number).

Upvotes: 0

Related Questions