text: ﻿<SQLBuilder_Common>
  
  DECLARE @SQL_Work VARCHAR(MAX)
  DECLARE @FileGroup VARCHAR(50) = '' --QUOTENAME(@SourceSchema + 'FG')
  DECLARE @CreateTable VARCHAR(200) = 'CREATE TABLE ' + QUOTENAME(@TargetSchema) + '.' + QUOTENAME(@SourceTable) + ' ('
  DECLARE @CreateTable_Work VARCHAR(200) = 'CREATE TABLE [work].' + QUOTENAME(@TargetSchema + '_' + @SourceTable) + ' ('
  DECLARE @PK VARCHAR(200), @EP VARCHAR(MAX), @UQ VARCHAR(200), @EPFK VARCHAR(MAX), @FK VARCHAR(MAX)
  DECLARE @IX_SourceCTid VARCHAR(200) = 'CREATE NONCLUSTERED INDEX [IX_' + @SourceTable + '_CTid] ON [' + @TargetSchema + '].[' + @SourceTable + ']([CTid] ASC)' + IIF(@FileGroup <> '', ' ON ' + @FileGroup, '')
  DECLARE @PKColumnName VARCHAR(50) = REPLACE(@SourceTable,'dim','') + 'Key'
  
  -- Main Table
  
  SET @SQL = '-- Generated by CreateDWScripts.tt - SQLBuilder_Tables_Stg.sql'	-- No datetime because it will only overwrite if different and date guarantees this
  SET @SQL = @SQL + @CR + @CreateTable
  
  SELECT @Sql = @SQL + @CR + SPACE(5) + QUOTENAME(@PKColumnName) + SPACE(@MaxColLen + 5 - LEN(QUOTENAME(@PKColumnName))) + 'INT               NOT NULL IDENTITY(1,1),'		-- Pk
  SET @PK = 'CONSTRAINT [PK_' + @SourceTable + '] PRIMARY KEY CLUSTERED (' + QUOTENAME(@PKColumnName) + ' ASC)' + IIF(@FileGroup <> '', ' ON ' + @FileGroup, '') + ','
  	
  SET @iCount = 1
  WHILE (SELECT COUNT(1) FROM @Cols AS c) >= @iCount	
  BEGIN
  	SELECT @Sql = @SQL + @CR + SPACE(5) + QUOTENAME(c.ColName) + SPACE(@MaxColLen + 5 - LEN(QUOTENAME(c.ColName))) + c.DataType + SPACE(18 - LEN(c.DataType)) + IIF(c.Nullable = 'NO', 'NOT NULL', 'NULL') + ','
  	FROM @Cols AS c
  	WHERE c.Id = @iCount
  	
  	SET @iCount = @iCount + 1
  END	
  
  SET @UQ = 'CONSTRAINT [UIX_' + @SourceTable + '] UNIQUE NONCLUSTERED ('
  SELECT @EP = '', @EPFK = '', @FK = ''
  SELECT @iCount = MAX(Id) FROM @Cols AS c WHERE c.PKOrdinal = 1
  WHILE (SELECT MAX(Id) FROM @Cols AS c WHERE c.PKOrdinal > 0) >= @iCount	
  BEGIN
  	SELECT @UQ = @UQ + IIF((SELECT MAX(Id) FROM @Cols AS c WHERE c.PKOrdinal = 1) < @iCount, ', ', '') + QUOTENAME(c.ColName) + ' ASC'
  	FROM @Cols AS c
  	WHERE c.Id = @iCount
  	
  	--SELECT @EP = @EP + 'EXECUTE sp_addextendedproperty @name = N''SCD'', @value = N''BK'', @level0type = N''SCHEMA'', @level0name = N''' + @SourceSchema + ''', @level1type = N''TABLE'', @level1name = N''' + @SourceTable + ''', @level2type = N''COLUMN'', @level2name = N''' + c.ColName + ''';'
  	--FROM @Cols AS c
  	--WHERE c.Id = @iCount
  	
  	--SET @EP = @EP + @CR + 'GO' + @CR
  
  	SET @iCount = @iCount + 1
  END	
  
  SELECT @iCount = 1
  WHILE (SELECT MAX(Id) FROM @Cols AS c WHERE c.LkupTable IS NOT NULL) >= @iCount	
  BEGIN
  	SELECT @FK = @FK + SPACE(5) + 'CONSTRAINT [FK_' + @TargetSchema + '_' + @SourceTable + '_' + c.ColName + '] FOREIGN KEY ([' + c.ColName + ']) REFERENCES [' + @TargetSchema + '].[' + c.LkupTable + '] ([' + c.FKColumn + ']),' + @CR
  	FROM @Cols AS c
  	WHERE c.Id = @iCount
  		AND c.LkupTable IS NOT NULL
  	
  	SET @iCount = @iCount + 1
  END
  
  SET @SQL = @SQL + @CR + SPACE(5) + @PK
  SET @SQL = @SQL + @CR + SPACE(5) + @UQ + ')' + IIF(@FileGroup <> '', ' ON ' + @FileGroup, '') + ','
  SET @SQL = @SQL + @CR + @FK
  SET @SQL = @SQL + @CR + ')'
  IF ((SELECT COUNT(1) FROM @Cols WHERE TextImageFGRequired = 1) > 0) AND @FileGroup <> ''
  	SET @SQL = @SQL + 'TEXTIMAGE_ON ' + @FileGroup
  SET @SQL = @SQL + ';'
  SET @SQL = @SQL + @CR + 'GO' + @CR
  SET @SQL = @SQL + @CR + @IX_SourceCTid
  SET @SQL = @SQL + @CR + 'GO' + @CR
  SET @SQL = @SQL + @CR + @EP
  SET @SQL = @SQL + @CR + 'GO' + @CR
  SET @SQL = @SQL + @CR + 'ALTER TABLE ' + QUOTENAME(@TargetSchema) + '.' + QUOTENAME(@SourceTable) + ' ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);' + @CR
  --SET @SQL = @SQL + @CR + @EPFK
  
  -- Work Table
  
  SET @SQL_Work = '-- Generated by CreateDWScripts.tt - SQLBuilder_Tables_Stg.sql'	-- No datetime because it will only overwrite if different and date guarantees this
  SET @SQL_Work = @SQL_Work + @CR + @CreateTable_Work
  
  SET @iCount = 1
  WHILE (SELECT MAX(Id) FROM @Cols AS c) >= @iCount	
  BEGIN
  	SELECT @SQL_Work = @SQL_Work + @CR + SPACE(5) + QUOTENAME(c.ColName) + SPACE(@MaxColLen + 5 - LEN(QUOTENAME(c.ColName))) + c.DataType + SPACE(18 - LEN(c.DataType)) + IIF(c.Nullable = 'NO', 'NOT NULL', 'NULL') 
  		+ CASE WHEN c.DefaultConstraint IS NOT NULL THEN ' CONSTRAINT [DF_' + @TargetSchema + '_' + @SourceTable + '_' + c.ColName + '] DEFAULT (' + c.DefaultConstraint + ')' ELSE '' END	
  		+ IIF((SELECT MAX(Id) FROM @Cols AS c) > @iCount, ',', '')
  	FROM @Cols AS c
  	WHERE c.Id = @iCount
  	
  	SET @iCount = @iCount + 1
  END	
  
  --SET @SQL_Work = @SQL_Work + @CR + SPACE(5) + @PK + '),' -- No Primary keys on work table as it helps debugging PK violations
  
  SET @SQL_Work = @SQL_Work + @CR + ') ' --ON [workFG]'
  
  --IF ((SELECT COUNT(1) FROM @Cols WHERE TextImageFGRequired = 1) > 0)
  --	SET @SQL_Work = @SQL_Work + ' TEXTIMAGE_ON [workFG]'
  
  --SET @SQL_Work = @SQL_Work + ';'
  
  SET @SQL_Work = @SQL_Work + @CR + 'GO' + @CR
   
  SELECT @SourceTable + '.sql', @TargetSchema + '\Tables', @SQL
  UNION ALL
  SELECT @TargetSchema + '_' + @SourceTable + '.sql', 'work\Tables', @SQL_Work
  