Checking Naming Conventions of MSSQL Database Objects

If you work on a project that has strict naming conventions on tables and views etc., it may be a good idea to check the database object names for that convention.

This can easily done by selecting the object names from sys.objects which do not match the naming convention:

SELECT Name
FROM   sys.objects
WHERE  schema_id = 1
AND    type IN ('U')
AND    name NOT LIKE '%Naming Convention for Tables%'
AND    name NOT LIKE 'aspnet%'
AND    name NOT IN ('sysdiagrams', ... other exclusions...)
ORDER BY name

In the WHERE condition, exclusions for the naming convention are added as NOT LIKE and NOT IN clauses. (e.g. sysdiagrams, aspnet tables etc.) If you use a schema other than dbo, you need to adjust the schema_id clause.

The naming check can by performed for any other type of database objects by adjusting the comparison of sys.objects.type. See here for the list of SQL Server types.

Occasionally you need to split these checks into several SELECT statements. If you require a project-specific prefix, and tables are to be named in plural, the first check (as above) would check for the prefix, the second check verifies the plural S:

SELECT Name
FROM   sys.objects
WHERE  schema_id = 1
AND    type IN ('U')
AND    name LIKE 'MyPrefix[_]%'
AND    name NOT LIKE '%S'
AND    name NOT IN (... list of known exclusions ...)
ORDER BY name

Advertisements