title: SQL Server/TSQL - Get column literal data type (with length)
body: <p>I need to accomplish a table like the default design's table (right click table and press design):</p>
  
  <p><a href="https://i.stack.imgur.com/74uBb.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/74uBb.png" alt="Data Type With length"></a></p>
  
  <p>With the following query I can get the length assigned for each field in my table:</p>
  
  <pre><code>SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLUMN_DEFAULT
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = @tablename
  ORDER BY ORDINAL_POSITION;
  </code></pre>
  
  <p>However imagine a data type like <code>text</code> where CHARACTER_MAXIMUM_LENGTH is not null (2147483647 instead) but it's value should be only 'text' in the data type column. </p>
  
  <p>Any ideas how can I get this behavior correct? Every other question doesn't want the "literal" data type.</p>
  
  <p>Edit: Some people couldn't understand what I pretend, I will try to explain better. I want the full data type from a column, I even gave the expected output (data type column). Imagine I have a table with some columns like int
  , text, varchar, datetime2. I want the data type like this: <code>int</code>, <code>varchar(120)</code>, <code>text</code>, <code>datetime2(7)</code>. This result is the data type column from the design column.</p>