Search all tables for a column AND return max length for the columns

Question

I need to make sure all columns that involve usernames have a max length of 140 characters. I'm fairly new to SQL, and I'm curious how you would do this.

I'm starting by returning a list of all tables that contain relevant column names. At the same time, I'd like to also see the character limits for those columns. Here's what I've got so far, except I don't know how to make a variable that will fill in both the Column and Length parts of the query. I'm using SQL Server 2008.

SELECT 
    t.name AS 'Table Name', 
    c.name AS 'Column Name', 
    max(len(%COLUMN_NAME%)) AS 'Max Length'
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%COLUMN_NAME%'
ORDER BY 'Table Name'

Solved. Thanks!

If you're new to sql i wonder why you need such an apporach. Why don't you select the correct column(s) of the appropriate table(s) instead of using `sys.tables`? — Tim Schmelter, Oct 20 '14 at 14:37
Do you need the maximum possible length of the column value (which is also present in `sys.columns`) or the length of the longest actual value stored (which requires constructing a dynamic query)? — Jeroen Mostert, Oct 20 '14 at 14:39
@TimSchmelter We have a lot of tables and quite a few possible variations of column names throughout them. I need to find ever instance of 'userid', for example, and also every instance of 'changedby', 'modifiedby', '%verbed%by'... I regularly use the query to find suitable tables, but I'm taking this as a learning experience to add the character count max to the query. I think maybe the technique will be useful for other types of queries in the future. — Defpotec2020, Oct 20 '14 at 14:41
@JeroenMostert I need to know max possible. There was a task to make sure all applicable fields allow 140 characters and I'm trying to figure out a way to test whether they do, without yet knowing where or what all of the applicable columns are. — Defpotec2020, Oct 20 '14 at 14:44

score 0 · Answer 1 · answered Oct 20 '14 at 14:48

0

If the task is "Make sure all such columns have a max length of 140", then you want your result set to be actionable items. The Max function here doesn't need to come into play, but you will want the where clause to filter items that are in no need of action.

SELECT 
    t.name AS 'Table Name', 
    c.name AS 'Column Name', 
    c.max_length AS 'Max Length'
FROM sys.tables AS t
    INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.max_length != 140
    AND c.name LIKE '%COLUMN_NAME%'
ORDER BY t.name

This checks inequality, but if you're looking for "at least 140 characters", make the first condition < 140 rather than != 140. The criteria are in this order to help make the query less costly, as equality operators on integers are faster than any LIKE operator.

answered Oct 20 '14 at 14:48

Jaaz Cole

3,119
1
15
20

The order of clauses in the `WHERE` is not significant -- the optimizer will reorder them anyway (and this holds true in general, with the only exception being `CASE`, since this forces an evaluation order). You can verify that the execution plans for both versions are the same. – Jeroen Mostert Oct 20 '14 at 14:54
I understand that academically it shouldn't, but I've got two query plans with a cost of 0.64.. and 0.42.. that might say otherwise. These were from a SQL 2008 R2 (SP2) instance. – Jaaz Cole Oct 20 '14 at 15:02
I'd be interested to see them, but unfortunately that goes beyond the scope of this question. Sufficive to say that in this particular case, it demonstrably does not matter, and I really wouldn't want to micro-optimize every query in the off chance that it does matter in particular cases -- that's best left to those cases. – Jeroen Mostert Oct 20 '14 at 15:09

score 0 · Accepted Answer · answered Oct 20 '14 at 14:50

The data in question is max_length in sys.columns, with the additional twist that we need to handle the MAX and Unicode types specially. You also want to leave out system tables.

SELECT 
    t.name AS 'Table Name', 
    c.name AS 'Column Name', 
    CASE WHEN tp.[name] IN ('nchar', 'nvarchar') AND c.max_length <> -1 THEN c.max_length / 2 ELSE c.max_length END AS 'Max Length'
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
INNER JOIN sys.types tp ON c.system_type_id = tp.system_type_id
WHERE t.is_ms_shipped = 0 AND c.name LIKE '%name%'
ORDER BY t.name

If max_length = -1, the type is one of the CHAR(MAX) types (and for this particular problem, that means it's long enough).

Search all tables for a column AND return max length for the columns

2 Answers2