Naming Conventions for Tables, Columns and Constraints

One query joins Customers to tblOrder and filters on cust_id. Simple naming conventions remove that translation work and make reviews easier. Pick one readable pattern, check it with metadata, and rename existing objects only after tracing their callers.

Start Naming Conventions With a Small Rule Set

Use singular table names such as Customer and Invoice for this example. Plural names also work when the whole team uses them consistently. A naming standard needs a decision, not a debate that returns every quarter. Use PascalCase for tables and columns, meaningful words, and an ID suffix for identifiers. Skip spaces, punctuation, and tbl prefixes.

I spend more time resolving inconsistent names than choosing between singular and plural. What would a new teammate type without opening three other tables first? That is a useful test. Avoid abbreviations that require private knowledge. UTC belongs in a timestamp name when it states the time basis. A shortened word that nobody recognizes saves little.

Make Relationships Visible in Constraint Names

Use PK_Table for the primary key. Use FK_Child_Parent_Column for a foreign key when the column helps distinguish relationships. Use CK_Table_Rule for checks and DF_Table_Column for defaults. For an index, use IX_Table_LeadingColumn and extend it when needed to distinguish another access path. Names should remain within SQL Server's identifier length limit.

CREATE TABLE dbo.Customer
(
    CustomerID int NOT NULL CONSTRAINT PK_Customer PRIMARY KEY,
    CustomerName nvarchar(100) NOT NULL
);
CREATE TABLE dbo.SalesOrder
(
    OrderID int NOT NULL CONSTRAINT PK_SalesOrder PRIMARY KEY,
    CustomerID int NOT NULL,
    TotalCents bigint NOT NULL CONSTRAINT DF_SalesOrder_TotalCents DEFAULT(0),
    CONSTRAINT FK_SalesOrder_Customer_CustomerID
        FOREIGN KEY(CustomerID) REFERENCES dbo.Customer(CustomerID),
    CONSTRAINT CK_SalesOrder_TotalCents CHECK(TotalCents>=0)
);
CREATE INDEX IX_SalesOrder_CustomerID ON dbo.SalesOrder(CustomerID);

Run this setup in a disposable database, so the sample names never collide with real tables. The rule describes a convention, not a required engine syntax. A foreign key continues to work with an unhelpful generated name. The cost arrives when a deployment error prints that name and somebody has to identify the relationship.

Flag Tables and Columns That Break Naming Conventions

The following scanner checks characters, initial capitals, and an obsolete table prefix. A binary collation makes character ranges behave predictably rather than following a case insensitive linguistic comparison. UNION ALL keeps table and column findings in one result. A label tells you which kind of object needs review.

SELECT N'Table' AS ObjectKind,s.name AS SchemaName,o.name AS ObjectName,
       CAST(NULL AS sysname) AS ColumnName
FROM sys.objects AS o
JOIN sys.schemas AS s ON s.schema_id=o.schema_id
WHERE o.type=N'U' AND o.is_ms_shipped=0
  AND (o.name COLLATE Latin1_General_100_BIN2 LIKE N'%[^A-Za-z0-9]%'
       OR LEFT(o.name,1) COLLATE Latin1_General_100_BIN2 NOT LIKE N'[A-Z]'
       OR o.name LIKE N'tbl%')
UNION ALL
SELECT N'Column',s.name,o.name,c.name
FROM sys.objects AS o
JOIN sys.schemas AS s ON s.schema_id=o.schema_id
JOIN sys.columns AS c ON c.object_id=o.object_id
WHERE o.type=N'U' AND o.is_ms_shipped=0
  AND (c.name COLLATE Latin1_General_100_BIN2 LIKE N'%[^A-Za-z0-9]%'
       OR LEFT(c.name,1) COLLATE Latin1_General_100_BIN2 NOT LIKE N'[A-Z]')
ORDER BY SchemaName,ObjectName,ColumnName;

This finds mechanical exceptions, not poor meanings. CustomerThing passes the character test and still deserves a conversation. Add a maintained exception list for vendor schemas and established contracts. Never generate automatic rename commands directly from these findings. A naming scanner has no knowledge of a dashboard sitting outside the database.

Check Prefixes Without Pretending They Prove Meaning

Constraint metadata gives you a second inexpensive check. Foreign keys, checks, defaults, and primary keys are objects. Ordinary indexes live in sys.indexes instead. Check them separately and exclude indexes that back named key constraints. Unique constraints deserve their own agreed prefix, such as UQ_, even though this sample sticks to five prefixes.

SELECT s.name AS SchemaName,t.name AS TableName,o.name AS ConstraintName,o.type
FROM sys.objects AS o
JOIN sys.tables AS t ON t.object_id=o.parent_object_id
JOIN sys.schemas AS s ON s.schema_id=t.schema_id
WHERE t.is_ms_shipped=0
  AND ((o.type=N'PK' AND o.name NOT LIKE N'PK[_]%')
    OR (o.type=N'F' AND o.name NOT LIKE N'FK[_]%')
    OR (o.type=N'C' AND o.name NOT LIKE N'CK[_]%')
    OR (o.type=N'D' AND o.name NOT LIKE N'DF[_]%'));
SELECT s.name AS SchemaName,t.name AS TableName,i.name AS IndexName
FROM sys.indexes AS i
JOIN sys.tables AS t ON t.object_id=i.object_id
JOIN sys.schemas AS s ON s.schema_id=t.schema_id
WHERE t.is_ms_shipped=0 AND i.index_id>0
  AND i.is_primary_key=0 AND i.is_unique_constraint=0
  AND i.name NOT LIKE N'IX[_]%';

The bracketed underscore matches a literal underscore. Without brackets, LIKE treats it as a wildcard. That small detail can turn a supposedly strict report into a generous one. Prefixes aid diagnosis. They do not replace looking at the indexed keys, included columns, filter, or constraint definition.

Locate Callers Before Renaming

Start with sys.sql_expression_dependencies for recorded references and sys.sql_modules for stored text. Add application queries, reports, jobs, ETL, and deployment files to the search. Dynamic SQL and cross database callers are not fully represented by dependency metadata. Encrypted module text and restricted catalog visibility also leave holes.

SELECT OBJECT_SCHEMA_NAME(d.referencing_id) AS ReferencingSchema,
       OBJECT_NAME(d.referencing_id) AS ReferencingObject,
       d.referenced_entity_name,d.referenced_minor_id
FROM sys.sql_expression_dependencies AS d
WHERE d.referenced_id=OBJECT_ID(N'dbo.Customer');
SELECT OBJECT_SCHEMA_NAME(m.object_id) AS SchemaName,
       OBJECT_NAME(m.object_id) AS ModuleName
FROM sys.sql_modules AS m
WHERE m.definition LIKE N'%Customer%';

I ask for the caller inventory before accepting a cosmetic rename. The expensive part is compatibility, not the stored procedure call. Save object definitions and permissions before the migration. Arrange a release where every affected caller changes together, or provide an explicitly designed compatibility layer. A synonym helps selected table references, but it does not rewrite column names.

Use sp_rename in a Disposable Rehearsal

Try the rename on the sample first. The COLUMN object type requires the qualified table and old column name. The new name is the new identifier alone. The OBJECT type handles the table. The example renames the column before the table so each command names the object that exists at that step.

EXEC sys.sp_rename
    @objname=N'dbo.Customer.CustomerName',
    @newname=N'DisplayName',@objtype=N'COLUMN';
EXEC sys.sp_rename
    @objname=N'dbo.Customer',
    @newname=N'Account',@objtype=N'OBJECT';
SELECT CustomerID,DisplayName FROM dbo.Account;

The object retains its identity, but stored SQL text is not rewritten. References using the old names need edits. Modules using SELECT star can retain stale metadata and need appropriate refresh after review. Renaming a procedure or view also does not rewrite its name inside the stored definition. For those objects, use reviewed recreation or ALTER scripts instead.

Test the Contract, Not Just the New Name

A SELECT from the renamed table proves very little about the application. Exercise inserts, updates, reporting, background jobs, and any ORM mapping. Check result column names consumed by clients. Review permissions and error messages. Keep the reverse migration ready when the deployment depends on coordinated releases across several applications.

Names can break a client even when data and types remain identical. A grid that binds CustomerName will not learn DisplayName from enthusiasm. Record which public names remain supported and for how long. If a rename provides little practical benefit, document the existing exception and improve new objects first.

Check generated scripts that depend on constraint and index names as well. A maintenance task can reference an index explicitly, while a deployment can drop a default by its known name. Those callers need the same inventory and rehearsal as application queries. Recheck the scanner after the migration, then compare its findings with the approved changes. Successful renaming should remove the intended exception without creating a new compatibility problem.

Keep Naming Conventions Easy to Follow

Put the rules in a short document with examples from your own schema. Use naming conventions during design review and run the scanner during deployments. Review exceptions rather than counting them as failures without context. Consistent new work gradually lowers confusion without requiring one disruptive cleanup of the entire database.

I favor a small standard that teammates can remember. Explain what each prefix contributes, then stop. The purpose is readable queries and understandable relationships. When a proposed name makes a common join harder to read, simplify it. A convention earns its place through the work it saves every week.

Related reading on this blog: Renaming Index: Index Naming Conventions and Rename View.

A naming convention is not a cleanup project, it is a shared vocabulary.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.