Character limit exceeding DB limit

Question

Currently we encountered issues like Character limit exceeding DB limit on one of our SQL Server 2016 Databases.

Now i am planning to check on entire DB that Character limit exceeding.

Is there any T-SQL available?

Answer 1

Hi @Vijay Kumar ,

This thread has been around for several days and we did not recevie any update from your side.

SQL maximum column name length limitation is 128 characters. If we create more than 128 characters, it shows an error.

If you would like to find out all the columns whose maxinum length is larger than definition, you could refer below query and check whether it is helpful to you.

DECLARE @id varchar(36)  
DECLARE @sql varchar(200)  
declare @receiver table(theCount int)  
  
DECLARE length_cursor CURSOR  
    FOR SELECT ID, SQLText FROM @results WHERE MaxLength != 'NA'  
OPEN length_cursor  
FETCH NEXT FROM length_cursor  
INTO @id, @sql  
WHILE @@FETCH_STATUS = 0  
BEGIN  
    INSERT INTO @receiver (theCount)  
    exec(@sql)  
  
    UPDATE @results  
    SET Longest = (SELECT theCount FROM @receiver)  
    WHERE ID = @id  
  
    DELETE FROM @receiver  
  
    FETCH NEXT FROM length_cursor  
    INTO @id, @sql  
END  
CLOSE length_cursor  
DEALLOCATE length_cursor  
  
  
SELECT   
    TableName,   
    ColumnName,   
    DataType,   
    MaxLength,   
    Longest   
FROM   
    @results  

Reference:List all SQL columns with max length AND greatest length

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.