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 Wednesday, April 03, 2013 10:23 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 9:32 AM
Points: 299, Visits: 1,195
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".

Post #1438465
Posted Wednesday, April 03, 2013 10:27 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:50 PM
Points: 22,504, Visits: 30,218
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1438468
Posted Wednesday, April 03, 2013 10:27 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:24 PM
Points: 11,990, Visits: 11,007
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 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 #1438469
Posted Wednesday, April 03, 2013 10:32 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 9:32 AM
Points: 299, Visits: 1,195
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.

Post #1438472
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse