One such incident involved a friend and colleague of mine who has just returned from an agile for BI course in London. Whilst there he was talking to a delegate from Finland about his work, team, etc. during the course of the discussion my name a was mentioned, which was greeted by the question 'do you mean the guy behind SQL Sith??'. The delegate went on to say how disappointed he was that I hadn't been keeping up with my blog. So; it appears that my fame/infamy has now spread to Europe, so I feel duty bound to try that little harder to keep my blog ticking over. So on to the topic at hand.
I was recently asked to build a mart off of a new operational system to support all MI and reporting requirements. Whilst reviewing the source data it quickly became apparent that the original developers had opted to use their own data type rather than the base SQL types. This presented a small challenge as adopting the same types would have added an unnecessary level of complication to my mart, something that I was eager to avoid.
Below is a piece of code that I wrote to address this problem. It queries information_schema.columns to determine the base types of the fields in a target table, and then constructs a create table script for an equivalent table using only base types.
@TableName varchar(128),
@TableSchema varchar(128),
@DestinationSchema varchar(128)
Select @TableFullName = 'dbo.UDF_Test'
Select @TableName = parsename(@TableFullName, 1),
@TableSchema = parsename(@TableFullName, 2),
@DestinationSchema = isnull(@DestinationSchema, @TableSchema)
Declare @FieldList varchar(8000)
Declare @CreateTableSQL varchar(8000)
Select @FieldList = cast(
(Select column_name + ' ' + Data_Type
+ case when Data_Type not IN ('datetime2','datetime','date','smallint','int','bigint','tinyint') then isnull('(' + cast(isnull(Character_Maximum_Length,Numeric_Precision) as varchar(4)) + isnull(',' + cast(Numeric_Scale as varchar(2)),'') + ')','')
else ''
end
+ ','
from information_Schema.columns
where table_Name = @TableName
and table_Schema = @TableSchema
order by ordinal_position
for xml path ('')
)
as varchar(8000))
Select @CreateTableSQL = 'Create Table ' + @DestinationSchema + '.' + @TableName + '(' + left(@FieldList, len(@FieldList) - 1) + ')'
Print @CreateTableSQL
GO