Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Find Max Field Length Multiple Tables and Multiple Fields


Find Max Field Length Multiple Tables and Multiple Fields

Author
Message
Roger Abram
Roger Abram
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 Visits: 318
I uploaded a bunch of Access tables to SQL and have to convert the Access data into tables used by a SQL database program. The problem is that all the varchar type fields in Access were created with a field length of 255. My SQL database program has field widths much lower than those and that vary as to what the info is that is being loaded (like first_name with a width of 20, suffix with a width of 5, etc) and can't be changed.

In a nutshell, I need to check each Access table/field and determine what the maximum length is currently being used and then identify which records might need manual adjusting before importing into the SQL database. I can run the following for each table/field but that would be cumbersome:

select max(len(employee_name)) from employee_table

Does anyone know of a utility that would loop through all the tables and all the fields that might find and report on this scenario?

Thank you.

Rog
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14959 Visits: 38980
here you go Roger; a project like this has to use the metadata to determine which columns to query, so using a cursor in this case is fine;

the logic is pretty obvious: get all the char-type columns, get their defined size, and find the max length as it exists in the table.
i'm stuffing the data into a global temp table and finally ordering the info back with a final query of the results. watch for MaxActualLength=0, since that's nulls or no rows in the database, which might not be representative of future data.
example results:

TableName ColumnName DefinedLength MaxActualLength
example exampleid 8 0
example exampletext 30 0
Z_Data_Extract_Activity_C06350_1_TXT RAWDATA 8000 779


the query:

select sysobjects.name as TableName,
syscolumns.name as ColumnName,
TYPE_NAME(syscolumns.xtype) AS VariableType,
syscolumns.length AS DefinedLength,
0 as MaxActualLength
into ##tmp
from sysobjects
inner join syscolumns on sysobjects.id=syscolumns.id
where sysobjects.xtype='U'

declare
@isql varchar(2000),
@tbname varchar(64),
@clname varchar(64)

declare c1 cursor for
select TableName,ColumnName from ##tmp where VariableType in('varchar','char','nvarchar','nchar')
open c1
fetch next from c1 into @tbname,@clname
While @@fetch_status <> -1
begin
select @isql = 'UPDATE ##TMP SET MaxActualLength = (SELECT ISNULL(max(DATALENGTH(@clname)),0) FROM @tbname) WHERE TableName =''@tbname'' and ColumnName =''@clname'''
select @isql = replace(@isql,'@tbname',@tbname)
select @isql = replace(@isql,'@clname',@clname)
print @isql
exec(@isql)

fetch next from c1 into @tbname,@clname
end
close c1
deallocate c1

select * from ##tmp
order by TableName,ColumnName



Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

AndrewSQLDBA
AndrewSQLDBA
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1008 Visits: 3427
What I would do is design the database in SQL Server properly, and then pump all the data in. I am sure that you will want to change and modify the design some when you use a much better relational database. Access is not much on this type of thing. You can create the same table structure in SQL, and then pump the data in, without allowing SQL Server to design the tables for you.

In the long run, it will take you much less time than trying to have some dinky app attempt to do this for you, by some automated process, and still get it incorrect. Even if you have say.... 250 tables in Access, that would take you maybe a full day to design that same database in SQL. But now you can rethink the design, solve some of the problems that Access restrictions gave you, and allow SQL Server to create the proper relationships between tables. Take my advice, you will be much further ahead by doing some work by hand.

Andrew SQLDBA
Roger Abram
Roger Abram
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 Visits: 318
Lowell, this is perfect! Thank you so much!

Roger
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search