Solved

Sql server column name length

Posted on 2015-01-15
11
213 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
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 77

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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

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 77

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
T-SQL 10 35
TSQL convert date to string 4 37
Import text from rows into columns? 12 44
MS SQL and inserting dates into tables using stored procedures 6 17
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

830 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