Last active
February 20, 2023 12:51
-
-
Save Alex-Yates/15cdee41ecafe861db5889f12d4bceac to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SET ANSI_NULLS ON | |
GO | |
SET QUOTED_IDENTIFIER ON | |
GO | |
-- If you want to use a different name for the table, update this variable | |
DECLARE @TABLENAME AS NVARCHAR(100)=N'flyway_schema_history'; | |
-- DO NOT EDIT ANYTHING BELOW THIS LINE | |
-- *************************************************************************************************************************************** | |
-- Naming convention: PK name should include name of table name (provided above) | |
DECLARE @PKNAME AS NVARCHAR(100)=@TABLENAME + N'_pk'; | |
-- Using dynamic SQL to create table because you cannot pass variable directly to CREATE TABLE | |
-- Saving CREATE TABLE script as variable | |
DECLARE @CREATETABLE AS NVARCHAR(MAX)=N'CREATE TABLE [dbo].['+@TABLENAME+']( | |
[installed_rank] [int] NOT NULL, | |
[version] [nvarchar](50) NULL, | |
[description] [nvarchar](200) NULL, | |
[type] [nvarchar](20) NOT NULL, | |
[script] [nvarchar](1000) NOT NULL, | |
[checksum] [int] NULL, | |
[installed_by] [nvarchar](100) NOT NULL, | |
[installed_on] [datetime] NOT NULL, | |
[execution_time] [int] NOT NULL, | |
[success] [bit] NOT NULL, | |
CONSTRAINT ['+@PKNAME+'] PRIMARY KEY CLUSTERED | |
( | |
[installed_rank] ASC | |
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] | |
) ON [PRIMARY] | |
ALTER TABLE [dbo].['+@TABLENAME+'] ADD DEFAULT (getdate()) FOR [installed_on] | |
'; | |
-- Running the dynamic SQL | |
exec sp_executesql @CREATETABLE; | |
GO |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment