GRANT VIEW DEFINITION TO [user]; will allow the user to see the definitions of structures in the database, including tables, views, stored procedures, etc.
You'll need to do that for every database on the instance. There is no server-wide equivalent, out of the box, that limits permissions to just the database level.
If you need to limit it to just a single schema, then you'd do:
GRANT VIEW DEFINITION ON schema::[name_of_schema] TO [user];
If you only want them to be able to see the definitions of tables and views (and not stored procs etc), then you'll need to do a single grant for each object you want them to see the definition for. You could generate the
GRANT statements for just tables and views at the database level with the following query:
ObjectType = o.type_desc
, ObjectName = o.name
, GrantStatement = N'GRANT VIEW DEFINITION ON ' + QUOTENAME(o.name) + N' TO [user];'
FROM sys.objects o
WHERE o.type = N'U'
OR o.type = N'V'
ORDER BY o.type
See the Microsoft Docs for more info at https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-database-principal-permissions-transact-sql?view=sql-server-ver15
If you're comfortable assigning a lot more permissions to the login, at the server level, you could do:
GRANT VIEW ANY DEFINITION TO [name_of_login];
That will allow them to see the definition of any object in any database they have access to, and also will allow them to see definitions of things like endpoints, at the server level.