I agree.
FWIW we do:
Table is named SSS_MMM_TableName where SSS=SubSystem e.g. MEM=Members, STK=Stock and MMM=Mnemonic e.g. STK_PROD_Product - Stock Products, MEM_REG_Registration - Member Registrations, MEM_ADR_Address - Member Address
The SSS_MMM is unique within the system. The "_TableName" bit is, I suppose, redundant, but helps with remembering which-is-which
Columns start with "sss_mmm_" (lower case) matching the table prefix.
sss_mmm_ID - Identity value
sss_mmm_Name, sss_mmm_Description, sss_mmm_Age and so on
Where one table references another then:
mem_adr_ID - Unique ID for an address
mem_adr_mem_reg_ID - corresponding (parent) ID for the Member Registration
SELECT mem_reg_XXX, ..., mem_adr_YYY, ...
FROM dbo.MEM_REG_Registration
JOIN dbo.MEM_ADR_Address
ON mem_adr_mem_reg_ID = mem_reg_ID
To me, in the event that I accidentally did
JOIN dbo.MEM_ADR_Address
ON mem_adr_mem_reg_ID = stk_prod_ID
the mis-match of entities would trigger an alarm bell - a.k.a. Defensive Programming. Its a throw-back to Hungarian Notation, of sorts.
For us, we put the column corresponding to the table being joined on the LEFT. Thus if there are multiple columns involved the LEFT side becomes a list of all the values required to identify the rows, which we think reduces bugs compared to them being on the RIGHT
...
FROM dbo.MEM_REG_Registration
JOIN dbo.MEM_ADR_Address
ON mem_adr_mem_reg_ID = mem_reg_ID
AND mem_adr_mem_reg_Territory = mem_reg_Territory
AND mem_adr_IsActive = 1
We only alias tables if they are referenced multiple times in a statement because all column names are unique within the data (by virtue of their prefixes)
For Foreign Key names we would use
FK_MEM_ADR_mem_reg_ID_MEM_REG
using the (unique) abbreviations - Child Table, Child Column, Parent Table