Solved

Sql server column name length

Posted on 2015-01-15
11
219 Views
Last Modified: 2015-01-26
I am exporting data from sql server to Teradata.  I am running into a error on the Teradata side that the column names are too long.  Is there a function in sql server that would allow me check the length of the column name.  Not the data type.
0
Comment
Question by:centralmike
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40551849
Select len(ColumnName) From Table1

or

SELECT DATALENGTH(ColumnName), ColumnName
FROM Table1
ORDER BY ColumnName;
0
 
LVL 79

Expert Comment

by:arnold
ID: 40551867
If you've created the database/table on the one to which you are exporting, export data only from your SQL. Though in this case, make sure the order of the data, matches the order of the schema where you will be importing it.

Instead of going directly, do indirect. export the data on one, import the data on the other, adjusting the column name as you need it to be.

Dealing with column name length is only one of possible many  other issues, constraints, references etc.
0
 
LVL 42

Expert Comment

by:pcelba
ID: 40551870
SELECT len(column_name), * FROM INFORMATION_SCHEMA.COLUMNS

Other options:
select * from sys.columns
select * from syscolumns
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:centralmike
ID: 40551880
Neither function works.  I tried both.  
select
len(policy_restoration_of_benefits_dt)

from
dbo.v_ads_policy_crnt
 group by
policy_restoration_of_benefits_dt
I expect the function to return the length of 33
0
 
LVL 42

Accepted Solution

by:
pcelba earned 500 total points
ID: 40551901
You may try:

SELECT MAX(len(column_name)) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = 'v_ads_policy_crnt'
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40551903
Sorry centralmike, I misread your post. The other solution posted by the experts looks good.
0
 
LVL 79

Expert Comment

by:arnold
ID: 40551929
Puzzling, lets say you find three columns that have a length of 128 characters, the issue will remain on how to get the data from here to there.

In this type of a situation I would do first get the table structure exported/setup making sure any references/constraints/triggers/etc. are accounted for in the new location.  Then export the data in the correct sequence to maintain data integrity.
i.e. populate table with data on which other tables/triggers, etc. rely.  

The other is export the data, edit the data to account for the ....
import the data. (just depends on how much data there is to deal with the large.,.....)
0
 
LVL 42

Expert Comment

by:pcelba
ID: 40553399
Hmm...
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40556987
To All,

I have requested this post to be regraded at: http://www.experts-exchange.com/Community_Support/General/Q_28599161.html
0
 
LVL 42

Expert Comment

by:pcelba
ID: 40570863
Thanks for the points!
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question