Check out the solution in the video TSQL: Get Maximum Length of Every Column On A Table. This can be helpful if we require strict limits in downstream tables, assuming that we have a data flow where we initially want to extract data from one set of tables (or table) to another set of tables. On the initial part of any data acceptance, I tend to prefer flexible development.
Check out the highest-rated Automating ETL course on Udemy, if you're interested in data.
We should be careful about allowing anything, however. This could open us to a security breach, so we still want to qualify data on entry or input. Hackers will test any free form where anything is allowed and for good reason: if developers accept anything, this can be exploited.