Create Table Script Template

USE MyDB
GO
/* ***************************************************************************************** */
GO
DECLARE 
	@ObjectName SYSNAME = 'dbo.tblTable01';

IF OBJECT_ID(@ObjectName) IS NOT NULL
BEGIN
	DECLARE @nvcStmt  NVARCHAR(256) = 'DROP TABLE ' + @ObjectName + ';';
	EXEC(@nvcStmt);
	IF OBJECT_ID(@ObjectName) IS NOT NULL
		RAISERROR('<<< FAILED TO DROP TABLE %s >>>', 16, 1, @ObjectName);
	ELSE 
		RAISERROR('<<< DROPPED TABLE %s >>>', 0, 1, @ObjectName);
END
GO 

/* ***************************************************************************************** */
GO

CREATE TABLE dbo.tblTable01
/* ***************************************************************************************** 

 Table        :   dbo.tblTable01 
 Version      :   1.00  
     
 Created      :   01/01/2020
 Author       :   Author
    
 Description  :   Description

 Modification History

 Request    Date        Name                Description
 ---------  ----------  ------------------  -------------------------------------------- 
 JIRA-123   01/01/2020  Author				Initially created

******************************************************************************************** */
(
	[Table01Id] [bigint] IDENTITY(1,1) NOT NULL,
	[Col01] [tinyint] NOT NULL,
	[Col02] [varchar](100) NOT NULL,
	CONSTRAINT PK_tblTable01 PRIMARY KEY CLUSTERED 
	(
		Table01Id ASC
	)
	WITH 
	(
		PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON
	) ON fgData
) ON fgData;
GO
/* ***************************************************************************************** */
GO
/* *** Extend Attribute for Table Description *** */

EXEC sp_addextendedproperty 
	@name = N'MS_Description', @value = N'The tblTable01 table contains my data', 
	@level0type = 'SCHEMA',    @level0name = N'dbo', 
	@level1type = 'TABLE',     @level1name = N'tblTable01', 
	@level2type = NULL,        @level2name = NULL;
GO
/* *** Extend Attribute for Column Descriptions *** */
GO
EXEC sp_addextendedproperty N'MS_Description', N'Identity field for each record (automatically assigned by database)', 'SCHEMA', N'dbo', 'TABLE', N'tblTable01', 'COLUMN', N'Table01Id';
GO
EXEC sp_addextendedproperty N'MS_Description', N'Stores data for field 1', 'SCHEMA', N'dbo', 'TABLE', N'tblTable01', 'COLUMN', N'Col01';
GO

/* *** Extend Attribute for Primary Key Description *** */

EXEC sp_addextendedproperty 
	@name = N'MS_Description',  @value = N'The primary key is Table01Id', 
	@level0type = 'SCHEMA',     @level0name = N'dbo', 
	@level1type = 'TABLE',      @level1name = N'tblTable01', 
	@level2type = 'CONSTRAINT', @level2name = N'PK_tblTable01';
GO
/* ***************************************************************************************** */
GO
DECLARE 
	@ObjectName SYSNAME = 'dbo.tblTable01';

IF OBJECT_ID(@ObjectName) IS NOT NULL
	RAISERROR('<<< CREATED TABLE %s >>>', 0, 1, @ObjectName);
ELSE 
	RAISERROR('<<< FAILED TO CREATE TABLE %s >>>', 16, 1, @ObjectName);
GO
/* ***************************************************************************************** */
GO
GRANT INSERT, SELECT, UPDATE, DELETE ON dbo.tblTable01 TO MyRole;
GO
/* ***************************************************************************************** */
GO