A very common question which I often receive is –
“How big my table name can be?”
The answer is simple 128. You can figure this out by executing stored procedure sp_server_info on SQL Server.
Execute following stored procedure in SQL Server.
EXEC sp_server_info
It will return following resultset.
attribute_id attribute_name attribute_value 1 DBMS_NAME Microsoft SQL Server 2 DBMS_VER Microsoft SQL Server 2012 - 11.0.3000.0 10 OWNER_TERM owner 11 TABLE_TERM table 12 MAX_OWNER_NAME_LENGTH 128 13 TABLE_LENGTH 128 14 MAX_QUAL_LENGTH 128 15 COLUMN_LENGTH 128 16 IDENTIFIER_CASE MIXED 17 TX_ISOLATION 2 18 COLLATION_SEQ charset=iso_1 sort_order=nocase_iso charset_num=1 sort_order_num=52 19 SAVEPOINT_SUPPORT Y 20 MULTI_RESULT_SETS Y 22 ACCESSIBLE_TABLES Y 100 USERID_LENGTH 128 101 QUALIFIER_TERM database 102 NAMED_TRANSACTIONS Y 103 SPROC_AS_LANGUAGE Y 104 ACCESSIBLE_SPROC Y 105 MAX_INDEX_COLS 16 106 RENAME_TABLE Y 107 RENAME_COLUMN Y 108 DROP_COLUMN Y 109 INCREASE_COLUMN_LENGTH Y 110 DDL_IN_TRANSACTION Y 111 DESCENDING_INDEXES Y 112 SP_RENAME Y 113 REMOTE_SPROC Y 500 SYS_SPROC_VERSION 11.00.3000
In the result set you can notice there is a row with information TABLE_LENGTH and the value is 128. There are few other information related to server is also listed in the result set.
Though, a regular tablename can be 128 character long, a temporary table cannot be longer than 116 character. You can read the detailed blog post over here Maximum Allowable Length of Characters for Temp Objects is 116 – Guest Post by Balmukund Lakhani.
Reference: Pinal Dave (https://blog.sqlauthority.com)
