Checking Naming Conventions of MSSQL Database Columns

Conventions for columns inside a database project can target:

  • Naming conventions for columns
  • Required occurrences
  • Data types
  • Required foreign key constraints

and others.

So for example, if there is a rule for your database that every table needs to have a column named ID, this requirement can be checked with this query:

SELECT t.name
FROM   sys.objects AS t
LEFT OUTER JOIN sys.columns AS c ON t.object_id = c.object_id
    AND c.name = 'ID'
WHERE  (t.type = 'U')
AND    (c.name IS NULL)
ORDER BY t.name

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

Let’s check the requirement that every table must have a primary key:

SELECT sys.objects.name
FROM   sys.objects
INNER JOIN sys.columns ON sys.objects.object_id = sys.columns.object_id
LEFT OUTER JOIN sys.index_columns ON sys.columns.column_id = sys.index_columns.column_id
    AND sys.columns.object_id = sys.index_columns.object_id
LEFT OUTER JOIN sys.indexes ON sys.objects.object_id = sys.indexes.object_id
    AND sys.indexes.is_primary_key = 1
WHERE  sys.objects.type = 'U'
AND    sys.indexes.object_id is null

Probably the primary key column always has to be an IDENTITY column:

SELECT sys.objects.name, sys.columns.name
FROM   sys.objects
INNER JOIN sys.columns ON sys.objects.object_id = sys.columns.object_id
INNER JOIN sys.index_columns ON sys.columns.column_id = sys.index_columns.column_id
    AND sys.columns.object_id = sys.index_columns.object_id
INNER JOIN sys.indexes ON sys.objects.object_id = sys.indexes.object_id
    AND sys.indexes.index_id = sys.index_columns.index_id
WHERE  (sys.indexes.is_primary_key = 1)
AND    (sys.columns.is_identity = 0)

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: