Technical Article

Create a view to Alter a Table and Add three Columns

,

I am teaching a database class at Queens College. The project entails the truncation of all of the data and loading new data into the  existing database "BIClass" from a single of the flattened data. One of the tasks of the project was to add these three additional columns to each of the tables (AlterTableAddColumnsUserAuthorizationKeyAndTwoTimeStamps):

  1. [UserAuthorizationKey] [int] not null DEFAULT (-99)
  2. [DateAdded] [datetime2](7)] not null DEFAULT (sysdatetime())
  3. [DateOfLastUpdate] [datetime2](7)] not null DEFAULT (sysdatetime())

The goal was to leverage the TSQL topics being taught:

  1. Built-in functions such as concat to create the alter table command
  2. Metadata views to find fully qualified table names using either INFORMATION_SCHEMA.TABLES (easier) or sys.tables
  3. Create a query using  a set operation such as union
  4. Create schema names such as Utils (Utility objects)
  5. Create a view with our convention in this case Utils.uvw_UtilityObjectName. In our case, Utils.uvw_AlterTableAddColumnsUserAuthorizationKeyAndTwoTimeStamps

Sample code:

Execution of this query returned 66 rows since there were 22 tables.

SELECT v.SchemaName,

v.FullyQualifiedTableName,

v.SortOrder,

v.AlterFullyQualifiedTableNameAddingColumn

FROM Utils.uvw_AlterTableAddColumnsUserAuthorizationKeyAndTwoTimeStamps AS v

ORDER BY v.FullyQualifiedTableName,

v.SortOrder;

-- Before Adding the three columns

CREATE TABLE [dbo].[Digits](

[digit] [INT] NOT NULL

) ON [PRIMARY]

GO

-- Add the three columns

ALTER TABLE dbo.Digits add [UserAuthorizationKey] [int] not null DEFAULT(-99)

ALTER TABLE dbo.Digits add [DateAdded] [datetime2](7) not null DEFAULT(sysdatetime())

ALTER TABLE dbo.Digits add [DateOfLastUpdate] [datetime2](7) not null DEFAULT(sysdatetime())

-- After Adding the three columns

CREATE TABLE [dbo].[Digits](

[digit] [INT] NOT NULL,

[UserAuthorizationKey] [INT] NOT NULL,

[DateAdded] [DATETIME2](7) NOT NULL,

[DateOfLastUpdate] [DATETIME2](7) NOT NULL

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Digits] ADD DEFAULT ((-99)) FOR [UserAuthorizationKey]

GO

ALTER TABLE [dbo].[Digits] ADD DEFAULT (SYSDATETIME()) FOR [DateAdded]

GO

ALTER TABLE [dbo].[Digits] ADD DEFAULT (SYSDATETIME()) FOR [DateOfLastUpdate]

GO

It teaches a simple way to automate a repetitive task as well as leveraging the use of metadata.

ALTER TABLE FullyQualifiedTableName add [UserAuthorizationKey] [int] not null DEFAULT(-99)
ALTER TABLE FullyQualifiedTableName add [DateAdded] [datetime2](7)   not null DEFAULT(sysdatetime())
ALTER TABLE FullyQualifiedTableName add [DateOfLastUpdate] [datetime2](7)   not null DEFAULT(sysdatetime())

select concat(t.TABLE_SCHEMA,'.',t.TABLE_NAME) as FullyQualifiedTableName
     , t.TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES as t
where t.TABLE_SCHEMA in ('CH01-01-Dimension','CH01-01-Fact') and t.TABLE_TYPE = 'BASE TABLE'



CREATE VIEW [Utils].[uvw_AlterTableAddColumnsUserAuthorizationKeyAndTwoTimeStamps]
/*
ALTER TABLE FullyQualifiedTableName 
ADD [NewColumnName] [Datatype | User Defined Datatype] 
[not null | null] 
DEFAULT(Default Value for NewColumnName )
*/AS
SELECT t.TABLE_SCHEMA AS SchemaName,
       CONCAT(t.TABLE_SCHEMA, '.', t.TABLE_NAME) AS FullyQualifiedTableName,
       1 AS SortOrder,
       CONCAT(
                 'ALTER TABLE ',
                 t.TABLE_SCHEMA,
                 '.',
                 t.TABLE_NAME,
                 ' add [UserAuthorizationKey] [int] not null DEFAULT(-99)'
             ) AS AlterFullyQualifiedTableNameAddingColumn
FROM INFORMATION_SCHEMA.TABLES AS t
WHERE t.TABLE_TYPE = 'BASE TABLE'
UNION
SELECT t.TABLE_SCHEMA AS SchemaName,
       CONCAT(t.TABLE_SCHEMA, '.', t.TABLE_NAME) AS FullyQualifiedTableName,
       2 AS SortOrder,
       CONCAT(
                 'ALTER TABLE ',
                 t.TABLE_SCHEMA,
                 '.',
                 t.TABLE_NAME,
                 ' add [DateAdded] [datetime2](7) not null DEFAULT(sysdatetime())'
             ) AS AlterFullyQualifiedTableNameAddingColumn
FROM INFORMATION_SCHEMA.TABLES AS t
WHERE t.TABLE_TYPE = 'BASE TABLE'
UNION
SELECT t.TABLE_SCHEMA AS SchemaName,
       CONCAT(t.TABLE_SCHEMA, '.', t.TABLE_NAME) AS FullyQualifiedTableName,
       3 AS SortOrder,
       CONCAT(
                 'ALTER TABLE ',
                 t.TABLE_SCHEMA,
                 '.',
                 t.TABLE_NAME,
                 ' add [DateOfLastUpdate] [datetime2](7) not null DEFAULT(sysdatetime())'
             ) AS AlterFullyQualifiedTableNameAddingColumn
FROM INFORMATION_SCHEMA.TABLES AS t
WHERE t.TABLE_TYPE = 'BASE TABLE'
GO



SELECT   v.SchemaName
       , v.TableName
       , v.FullyQualifiedTableName
       , v.SortOrder
       , v.AlterFullyQualifiedTableNameAddingColumn
FROM            Utils.uvw_AlterTableAddColumnsUserAuthorizationKeyAndTwoTimeStamps AS v
ORDER BY FullyQualifiedTableName, SortOrder

----Sample before and after of the execution

------ Before Adding the three columns

----CREATE TABLE [dbo].[Digits](
----[digit] [INT] NOT NULL
----) ON [PRIMARY]
----GO

------ Add the three columns

----ALTER TABLE dbo.Digits add [UserAuthorizationKey] [int] not null DEFAULT(-99)
----ALTER TABLE dbo.Digits add [DateAdded] [datetime2](7) not null DEFAULT(sysdatetime())
----ALTER TABLE dbo.Digits add [DateOfLastUpdate] [datetime2](7) not null DEFAULT(sysdatetime())

------ After Adding the three columns

----CREATE TABLE [dbo].[Digits](
----[digit] [INT] NOT NULL,
----[UserAuthorizationKey] [INT] NOT NULL,
----[DateAdded] [DATETIME2](7) NOT NULL,
----[DateOfLastUpdate] [DATETIME2](7) NOT NULL
----) ON [PRIMARY]
----GO

----ALTER TABLE [dbo].[Digits] ADD  DEFAULT ((-99)) FOR [UserAuthorizationKey]
----GO

----ALTER TABLE [dbo].[Digits] ADD  DEFAULT (SYSDATETIME()) FOR [DateAdded]
----GO

----ALTER TABLE [dbo].[Digits] ADD  DEFAULT (SYSDATETIME()) FOR [DateOfLastUpdate]
----GO

Rate

3 (3)

You rated this post out of 5. Change rating

Share

Share

Rate

3 (3)

You rated this post out of 5. Change rating