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

One Response to Checking Naming Conventions of MSSQL Database Objects

  1. […] Of course, you can restrict these checks to tables that match your table naming conventions. […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: