Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Find Max Field Length Multiple Tables and Multiple Fields Expand / Collapse
Author
Message
Posted Saturday, December 26, 2009 4:59 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 11:31 AM
Points: 133, Visits: 309
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
Post #839202
Posted Saturday, December 26, 2009 7:37 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:14 PM
Points: 12,952, Visits: 32,476
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #839207
Posted Saturday, December 26, 2009 7:38 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, November 7, 2014 2:00 PM
Points: 979, Visits: 3,364
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
Post #839208
Posted Saturday, December 26, 2009 9:05 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 11:31 AM
Points: 133, Visits: 309
Lowell, this is perfect! Thank you so much!

Roger
Post #839214
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse