[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Sql server column name length

Posted on 2015-01-15
11
Medium Priority
?
241 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 81

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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 

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 81

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

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

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

MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Exchange database can often fail to mount thereby halting the work of all users connected to it. Finding out why database isn’t mounting is crucial and getting the server back online. Stellar Phoenix Mailbox Exchange Recovery is a champion product t…
Viewers will learn how the fundamental information of how to create a table.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

590 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