Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Sql server column name length

Posted on 2015-01-15
11
Medium Priority
?
233 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
  • 4
  • 3
  • 2
  • +1
10 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 80

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 43

Expert Comment

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

Other options:
select * from sys.columns
select * from syscolumns
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

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 43

Accepted Solution

by:
pcelba earned 2000 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 80

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 43

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 43

Expert Comment

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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

879 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