SET NOCOUNT ON
go
IF OBJECT_ID('tempdb..#columns') IS NOT NULL
DROP TABLE #columns
go
DECLARE @sql VARCHAR(MAX) = 'OptOutMunicipality DataSupplierId smallint
OptOutMunicipality MuncCd varchar(8)
OptOutMunicipality VTUCanUnOpt bit
OptOutMunicipality UpdateId int
OptOutMunicipality UpdateTimestamp DateTime(2)
OptOutPhone PhoneOptSourceId tinyint
OptOutPhone PhoneNbr varchar(20)
OptOutPhone UpdateId int
OptOutPhone UpdateTimestamp DateTime(2)
OPtOutSource OptSourceId tinyint
OPtOutSource OptSource varchar(10)
OPtOutSource Description varchar(30)
OPtOutSource SourcePriority tinyint
OPtOutSource UpdateId int
OPtOutSource UpdateTimestamp datetime(2)
OptOutAddr OptOutAddrId int
OptOutAddr AddrOptSourceId tinyint
OptOutAddr Comment varchar(80)
OptOutAddr OrigAddr1 varchar(60)
OptOutAddr OrigCityName varchar(40)
OptOutAddr OrigFirstNm varchar(32)
OptOutAddr OrigLastNm varchar(32)
OptOutAddr OrigStCd char(2)
OptOutAddr OrigZipCd varchar(9)
OptOutAddr StdAddr1 varchar(60)
OptOutAddr StdCityName varchar(40)
OptOutAddr StdStCd char(2)
OptOutAddr StdZipCd varchar(9)
OptOutAddr UpdateId int
OptOutAddr UpdateTimestamp datetime(2)'
IF OBJECT_ID('tempdb..#columns') IS NULL
SELECT RowNumber,
'[tCommon].[' + C1 + ']' AS TableFullName,
C1 AS TableName,
C2 AS ColumnName,
C4 AS DataType,
ROW_NUMBER() OVER (PARTITION BY C1 ORDER BY rownumber) AS ColId
INTO #columns
FROM Util.dbo.ParseDelimitedTableColumns32(@sql, ' ', '
')
WHERE C1 <> ''
SELECT DISTINCT
'IF OBJECT_ID(''' + tableFullname + ''') is not null drop table ' + TableFullName + '
GO'
FROM #columns
SELECT @SQL = Util.dbo.StringConcat(SQL, '
')
FROM (SELECT 'CREATE TABLE ' + TableFullName + '(
' + Util.dbo.StringConcat(' ' + ColumnName + ' ' + Datatype + CASE WHEN ColId = 1 THEN ' NOT NULL'
WHEN COLUMNNAME = 'UpdateTimestamp'
AND DATATYPE LIKE '%DateTime%'
THEN ' NULL CONSTRAINT [DF_' + TableName
+ '$' + ColumnName
+ '$getdate] DEFAULT ((GETDATE()))'
WHEN DataType = 'bit'
THEN ' NOT NULL CONSTRAINT [DF_' + TableName
+ '$' + ColumnName + '$0] DEFAULT ((0))'
ELSE ' NULL'
END, ',
') + ')
GO
ALTER TABLE ' + TableFullName + ' ADD CONSTRAINT [' + TableName + '_PKC] PRIMARY KEY CLUSTERED ('
+ Util.dbo.StringConcat(CASE WHEN ColId = 1 THEN '[' + cOLUMNName + ']'
END, ', ')
+ ')WITH (DATA_COMPRESSION = PAGE, SORT_IN_TEMPDB = ON) ON [Common]
GO
' AS SQL
FROM #columns
GROUP BY TableFullName,
tABLEnAME) b
EXEC Util.dbo.PrintLargeText
@SQL
PRINT 'GO
ALTER TABLE [tCommon].[OptOutPhone] ADD CONSTRAINT CK_OptOutPhone_PhoneNbr CHECK (PhoneNbr NOT LIKE ''%[^0-9]%'')'