Living example SQL script of database standards. See https://andypalmer.info/posts/a-different-take-on-database-standards-documentation/

database-standards-example.sql

--Start with this file, rename and remove as required.
--Use PascalCase for table names and column names.

-- create new table with composite PK; this is OK if there is a clear and simple PK from one or two other columns
if not exists(select * from sys.tables t where t.name = 'TableName')
begin
    create table TableName
    (
        CompositeId1 int not null,
        CompositeId2 int not null,

        CreatedDate datetime not null constraint DF_TableName_CreatedDate default (getutcdate()),
        UpdatedDate datetime null, -- leave null on first creation of row

        constraint PK_TableName primary key clustered (CompositeId1, CompositeId2) -- PK should be clustered if this is the main way you expect to select rows from the table
    )
end
go

-- create new table with identity PK; this is advisable if there is not a clear simple PK from other columns, e.g. a user might have a unique row for every distinct date
if not exists(select * from sys.tables t where t.name = 'TableName')
begin
    create table TableName
    (
        --Primary key named after the table
        TableNameId int not null identity constraint PK_TableName primary key nonclustered, -- consider if your PK should be clustered or not? will most queries be by this ID or by some other index?

        --Any table that saves user data:
        UserId int not null,
        AnotherId int not null,

        --For user related dates, pass through the user's local date, and use SQL "date" type if the local time is not relevant:
        XxxDate date not null, --the name should reflect the use of the column (e.g. "TableName.ActivityDate" or "TableName.DateAchieved")

        --For saving system data), the user Id's or the relevant user should be saved:
        CreatedBy int not null,
        UpdatedBy int null,

        --Each table should have the following columns, should always be saved as UTC:
        CreatedDate datetime not null constraint DF_TableName_CreatedDate default (getutcdate()), --Explicitly name constraint
        UpdatedDate datetime null, -- leave null on first creation of row

        --Unique Constraint:
        constraint UX_TableName_UserId_XxxDate unique (UserId, XxxDate)
    )
end
go

-- add new index to existing table
-- if the index is for a very specfic purpose,  then indicate that purpose in the name, e.g. IX_TableName_ColumnA_ColumnB_SpecificScenario
if not exists (select * from sys.indexes where name = 'IX_TableName_UserId_AnotherId' and object_id = object_id('TableName'))
begin
    create 
    unique -- consider whether your indexes ought to be unique
    nonclustered -- make this clustered if it will be used more often than your PK (and make the PK nonclustered)
    index IX_TableName_UserId_AnotherId on TableName (UserId, AnotherId) -- the order of columns is important! in this example, searches by only UserId can still use the index, but searches by only AnotherId can not
end
go

-- add a column to an existing table
if not exists(select * from sys.tables t join sys.columns c on t.object_id = c.object_id where t.name = 'TableName' and c.name = 'ColumnId')
begin
    alter table TableName add ColumnId int null -- new column should be nullable so it does not break old website code (use X script to enforce not null)
end
go

-- add a default constraint to an existing table
if not exists (select * from sys.tables t join sys.columns c on t.object_id = c.object_id join sys.default_constraints d on c.default_object_id = d.object_id where t.name = 'TableName' and c.name = 'ColumnId')
begin
    alter table TableName add constraint DF_TableName_ColumnId default (0) for ColumnId -- change (0) to whatever default value you need e.g. getutcdate()
end
go

-- add a unique constraint to an existing table
if object_id('UX_TableName_MemberId_ActivityDate', 'UQ') is null
begin
    alter table TableName add constraint UX_TableName_MemberId_ActivityDate unique (MemberId, ActivityDate)
end
go

-- rename an existing table; be careful not to do this if the table is actively being used by live code
if not exists(select * from sys.tables t join sys.columns c on t.object_id = c.object_id where t.name = 'TableName1')
begin
    exec sp_rename 'TableName', 'TableName1'
end
go