Solved

Sql server column name length

Posted on 2015-01-15
11
216 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 78

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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 78

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

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

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

Suggested Solutions

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

738 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