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!
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).