March 29, 2013 at 2:34 pm
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.
March 29, 2013 at 2:57 pm
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
April 1, 2013 at 10:55 am
That's seems like an interesting task. What do you hope to gain from it?
April 1, 2013 at 12:30 pm
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
April 2, 2013 at 9:41 am
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)
April 2, 2013 at 2:01 pm
bery cool. i will take that and use it for my own. a little more intense on the system but much less code
April 2, 2013 at 3:37 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
April 3, 2013 at 8:45 am
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?
April 3, 2013 at 9:19 am
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
April 3, 2013 at 10:13 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 3, 2013 at 10:23 am
Sean Lange (4/3/2013)
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
Did this code run for you? Even specifying a table in the declare, I got errors:
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@table".
Msg 137, Level 15, State 2, Line 6
Must declare the scalar variable "@table".
April 3, 2013 at 10:27 am
erikd (4/3/2013)
Sean Lange (4/3/2013)
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
Did this code run for you? Even specifying a table in the declare, I got errors:
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@table".
Msg 137, Level 15, State 2, Line 6
Must declare the scalar variable "@table".
I was able to run the code unmodified without errors. May I ask what version of SQL Server you are running and are you running the code just as posted or did you include it in a procedure of some sort.
April 3, 2013 at 10:27 am
erikd (4/3/2013)
Sean Lange (4/3/2013)
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
Did this code run for you? Even specifying a table in the declare, I got errors:
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@table".
Msg 137, Level 15, State 2, Line 6
Must declare the scalar variable "@table".
Since you are using 2005 you have to separate the declaration and the assignment.
declare @thing nvarchar(max), @table sysname
set @table = 'SomeTable'
Then it should work for you.
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 3, 2013 at 10:32 am
Sean Lange (4/3/2013)
erikd (4/3/2013)
Sean Lange (4/3/2013)
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
Did this code run for you? Even specifying a table in the declare, I got errors:
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@table".
Msg 137, Level 15, State 2, Line 6
Must declare the scalar variable "@table".
Since you are using 2005 you have to separate the declaration and the assignment.
declare @thing nvarchar(max), @table sysname
set @table = 'SomeTable'
Then it should work for you.
That did it. Thank you.
July 30, 2017 at 10:39 pm
For anyone else who stumbles across this thread looking for answers, this blog post details the code to perform similar functionality.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply