Character limit exceeding DB limit

Vijay Kumar 2,016 Reputation points
2021-02-09T01:33:00.857+00:00

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?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,852 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,559 questions
{count} votes

1 answer

Sort by: Most helpful
  1. MelissaMa-MSFT 24,176 Reputation points
    2021-02-16T06:08:53.603+00:00

    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.

    0 comments No comments