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 12»»

i need the max length value of every column in every table in a database Expand / Collapse
Author
Message
Posted Friday, March 29, 2013 2:34 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Yesterday @ 7:56 AM
Points: 144, Visits: 355
I'm creating some dynamic sql to loop through each table and column and basically generate some dynamis sql that will get the max length of each column in each table in a database. Then store it locally into a table like the below.


create table TableColLengths
(
id int identity(1,1)
, tablename varchar(255)
, colName varchar(255)
, MaxColLength bigint
)

I'm going to write it myself however didnt know if somewone has already done it so i dont have to go though the hassle.
Post #1437095
Posted Friday, March 29, 2013 2:57 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:58 AM
Points: 12,877, Visits: 31,791
something like this? also, max_length only makes sense for char types, so you mean like varchar/nvarchars right?
SELECT 
OBJECT_NAME(OBJECT_ID) AS tablename,
name AS colName,
TYPE_NAME(system_type_id),
CASE
WHEN TYPE_NAME(system_type_id) IN ('NCHAR','NVARCHAR')
THEN max_length / 2
ELSE max_length
END AS MaxColLength
FROM sys.columns
WHERE TYPE_NAME(system_type_id) IN ('NCHAR','NVARCHAR','CHAR','VARCHAR')



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 #1437099
Posted Monday, April 1, 2013 10:55 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, July 18, 2014 3:36 PM
Points: 541, Visits: 1,044
That's seems like an interesting task. What do you hope to gain from it?
Post #1437531
Posted Monday, April 1, 2013 12:30 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Yesterday @ 7:56 AM
Points: 144, Visits: 355
I’m converting a client’s db to our internal structure. In doing so I need to bring their files down. Import them into sql then convert them to my internal structure. There are about 200 files ranging in all sizes. the first time I bring in all the files I usually set the cols to a data type that will give the lease amount of error. In some cases I make em all varchar(max) just to get the file loaded. I then go through and create the schema based on the data types the client sends. However in this instance it is not only outdated however but on a PDF and not from a sql server database so the types are all different.

Nonetheless, I get the data loaded into the dummy table w/ varchar(max) cols. then I go back and manually create the table based on the clients schema spec. Then I try and insert the dummie table into the newly create table. When I do so I find I get a lot of truncation errors. Some of these tables have 50+ columns so it's impossible to see where the truncation is occurring. Hence the below sql I generated shortly after I posted this. Rather than doing it for every table I simply made it to run for a single table. it has paid off and now I can get through all my truncation issues when they occur relatively quickly. When i have mor etime i''ll play w doing what i originally planned

DECLARE @outtersql VARCHAR(max)

SET @outtersql=''

DECLARE @innersql VARCHAR(max)

SET @innersql=''

DECLARE @tablename VARCHAR(1000)

SET @tablename = 'put_Table_name_here'

DECLARE @colname VARCHAR(1000)

SET @colname = ''

DECLARE @sql VARCHAR(max)

SET @sql=''

SELECT st.name tablename
,sc.name colname
,0 processed
INTO #temp
FROM sys.tables st
JOIN sys.columns sc
ON sc.object_id = st.object_id
WHERE st.name LIKE @tablename
ORDER BY st.name
,sc.column_id

WHILE EXISTS (SELECT TOP 1 *
FROM #temp
WHERE processed = 0)
BEGIN
SET @sql= ''
SET @innersql = ''
SET @outtersql = ''

SELECT TOP 1 @tablename = tablename
FROM #temp
WHERE processed = 0

SET @outtersql = ' select ''' + @tablename + ''' tableName, '
SET @innersql = ' from ( SELECT '

WHILE EXISTS (SELECT TOP 1 *
FROM #temp
WHERE processed = 0
AND tablename = @tablename)
BEGIN
SELECT TOP 1 @colname = colname
FROM #temp
WHERE processed = 0
AND tablename = @tablename

SET @outtersql = @outtersql + ' max([' + @colname + ']) '
+ '[MaxLength_' + @colname + '], '
SET @innersql = @innersql + ' len([' + @colname + ']) ' + '['
+ @colname + '], '
UPDATE #temp
SET processed = 1
WHERE processed = 0
AND tablename = @tablename
AND @colname = colname
END
SET @innersql = @innersql + 'from ' + @tablename
+ '(nolock) ) tt'
SET @innersql = Replace(@innersql, ', from', ' from ')
SET @outtersql = Reverse(Substring(Reverse(@outtersql), 2, 100000))
SET @sql= @outtersql + @innersql
SET @sql = Replace(@sql, ', from ', ' from ')

execute( @sql)

UPDATE #temp
SET processed = 1
WHERE processed = 0
AND @tablename = tablename
END

UPDATE #temp
SET processed = 0

DROP TABLE #temp



Post #1437565
Posted Tuesday, April 2, 2013 9:41 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 9:43 AM
Points: 382, Visits: 1,506
i run into truncation issues a lot. i've been using this to check the max length of raw table column values when they won't load to my processing table:

declare @thing nvarchar(max), @table sysname
select @table='j3688723', @thing=''
select @thing=@thing+'select '''+@table+''' as table_name,'''+column_name+''' as column_name,
max(len('+column_name+')) as column_length from '+@table+' union all '
from information_schema.columns where table_name=''+@table+'' and data_type <> 'ntext'
set @thing=left(@thing,len(@thing)-9)
exec(@thing)

Post #1437975
Posted Tuesday, April 2, 2013 2:01 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Yesterday @ 7:56 AM
Points: 144, Visits: 355
bery cool. i will take that and use it for my own. a little more intense on the system but much less code
Post #1438077
Posted Tuesday, April 2, 2013 3:37 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:26 AM
Points: 1,951, Visits: 2,886
It's a huge waste to do a separate SELECT from the table for each column.

You can do a MAX(LEN(column_name)) for all columns in one SELECT statement, and hopefully thus do only one scan (or at least fewer scans) of the table.


SQL DBA,SQL Server MVP('07, '08, '09)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1438117
Posted Wednesday, April 3, 2013 8:45 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 9:43 AM
Points: 382, Visits: 1,506
ScottPletcher (4/2/2013)
It's a huge waste to do a separate SELECT from the table for each column.

You can do a MAX(LEN(column_name)) for all columns in one SELECT statement, and hopefully thus do only one scan (or at least fewer scans) of the table.


Would you mind showing me what you mean?
Post #1438398
Posted Wednesday, April 3, 2013 9:19 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:58 AM
Points: 12,877, Visits: 31,791
I believe he means a single query, getting all the max lens in one query:
SELECt
Max(len(col1)) As m1,
Max(len(col2)) As m2,
Max(len(col3)) As m3
FROM yourTable



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 #1438428
Posted Wednesday, April 3, 2013 10:13 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:19 AM
Points: 13,064, Visits: 11,897
Using your dynamic code as an example this will produce the same results but as a single query instead of 1 query for each column.

declare @thing nvarchar(max), @table sysname = 'SomeTable'

select top 1 @thing = 'SELECT ''' + @table + ''' as TableName, ' + STUFF(
(
select ', MAX(LEN(' + COLUMN_NAME + ')) as ' + COLUMN_NAME
from information_schema.columns where table_name=''+@table+'' and data_type <> 'ntext'
for XML path('')), 1, 1, '') + ' FROM [' + @table + ']'
from information_schema.columns where table_name=''+@table+'' and data_type <> 'ntext'

exec sp_executesql @thing



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1438461
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse