CODEWITHSUNDEEP
sql-servert-sql
user3115933
user3115933

Reputation: 4443

What is the T-SQL syntax to get the length of each column of a specific Table?

My database is hosted on Microsoft SQL Server 2012 and I need to write a T-SQL query to pull the length of each column of a specific table.

Assuming my database is called mydatabase and the table is called table1 with 3 columns (namely col1, col2 and col3), how do I write my sql query to get that information?

Ideally, I want the output to be something like this:

ColumnName    Length
  col1          50
  col2          30
  col3          25

Additional info: I will need to run this query on several other tables where I don't know the number or names of the columns therein. So the query should output the names of the columns with their respective column length.

Upvotes: 3

Views: 2313

Answers (3)

Thom A
Thom A

Reputation: 95574

I assume by length you mean, for example, that if it is a varchar(50) it has a length of 50. If it's a decimal(18,2) then you want to know Scale 18, Precision 2. This should help:

SELECT c.[name] AS ColumnName, st.[name] AS DataType,
       CASE WHEN st.[name] IN ('varchar','nvarchar') THEN c.max_length END AS Max_Length,
       CASE WHEN st.[name] NOT IN ('varchar','nvarchar') THEN c.scale END AS Scale,
       CASE WHEN st.[name] NOT IN ('varchar','nvarchar') THEN c.[precision] END AS [Precision]
FROM sys.tables t
     JOIN sys.columns c ON t.object_id = c.object_id
     JOIN sys.types st ON c.system_type_id = st.system_type_id
WHERE t.[name] = 'YourTableName';

Upvotes: 4

Tom
Tom

Reputation: 15051

You can use COL_LENGTH to get this (more information can be found here)

You could write something like the following:

Select COL_LENGTH ( 'table1' , 'Col1' )
Select COL_LENGTH ( 'table1' , 'Col2' ) 
Select COL_LENGTH ( 'table1' , 'Col3' )

EDIT:

With the extra information provided I think the below is what you are looking for:

SELECT        
       t.name AS 'Table_Name'
      ,c.name  AS 'Column_Name'
      ,I.CHARACTER_MAXIMUM_LENGTH 
      ,I.DATA_TYPE
FROM  sys.columns c
JOIN  sys.tables  t   ON c.object_id = t.object_id
JOIN  INFORMATION_SCHEMA.COLUMNS I on I.COLUMN_NAME = c.name

You will probably have to add a where clause in as this is currently looking for everything on a database.

By joining the sys.columns and sys.tables with information_schema.columns You can find the length of columns/tables with out needing to know the name.

Upvotes: 2

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8033

If you are looking for a Query that will return the Maximum permitted length of a column, Then you can View it from the INFORMATION_SCHEMA.COLUMN view

SELECT
    ORDINAL_POSITION,
    COLLATION_NAME,
    CHARACTER_MAXIMUM_LENGTH
    FROM INFORMATION_SCHEMA.COLUMNS
       WHERE TABLE_NAME = 'YourTableName'

Or if you are looking for the Maximum Lenght of the Data Stored is Each Column Use MAX() and LEN() function

SELECT MAX(LEN(Col1)) FROM YourTable

Upvotes: 3

Related Questions