Dynamically generated Create Table Statement SQL Server

— Modified the script to capture and include column default values

It has been a while since I last added to the googleverse, been taking more than I’ve been giving lately, time to give a little back.

In this short post you will find a query that builds a CREATE TABLE statement given a valid table name. The script only handles a single Primary Key Constraint and does not handle foriegn key contraints, but even with it’s limitations I hope this helps someone out there.

declare @SqlStart nvarchar(max) = ''
declare @sql nvarchar(max) = ''
declare @sqlEnd nvarchar(max) = ''
declare @table varchar(max) = 'T_ESB_Submission_Template'

set @SqlStart = 'CREATE TABLE [' + @table + '] (' + Char(13) 
				
Select @sql = @sql + a.Column_Name + ' ' 
+ Data_Type 
+ CASE WHEN CHARACTER_MAXIMUM_LENGTH  IS NULL or DATA_TYPE = 'xml' THEN '' 
ELSE '(' + 
CASE WHEN CHARACTER_MAXIMUM_LENGTH>0 THEN 
CAST(CHARACTER_MAXIMUM_LENGTH as varchar(10))
ELSE 'max' END +')' END
+ CASE WHEN NUMERIC_PRECISION IS NULL OR DATA_TYPE in ('Int', 'tinyint', 'bigint', 'smallint') THEN '' 
ELSE '(' + CAST(NUMERIC_PRECISION as varchar(10)) +','+CAST(NUMERIC_SCALE as varchar(10)) +')' END 
+ CASE when exists (  select id from syscolumns where object_name(id)=@table 
		and name=a.column_name 
		and columnproperty(id,name,'IsIdentity') = 1  ) 
THEN 
' IDENTITY(' +  
cast(ident_seed(@table) as varchar) + ',' +  
cast(ident_incr(@table) as varchar) + ')' ELSE '' END 
+ CASE WHEN b.default_value is not null THEN ' DEFAULT ' + 
substring(b.default_value, 2, Len(b.default_value)-2) + ' ' ELSE '' END
+ CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE ' NULL ' END + CHAR(13) + ','
from  Information_Schema.COLUMNS a
join 
(  SELECT so.name AS table_name, 
          sc.name AS column_name, 
          sm.text AS default_value
     FROM sys.sysobjects so
     JOIN sys.syscolumns sc ON sc.id = so.id
 left JOIN sys.syscomments SM ON sm.id = sc.cdefault
    WHERE so.xtype = 'U' 
      AND SO.name = @table) b on b.column_name = a.COLUMN_NAME and b.table_name = a.TABLE_NAME
where a.Table_Name = @table

if((select count(1) from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where TABLE_NAME = @table) >0)
BEGIN
     select @sqlEnd =char(13) + 'CONSTRAINT [PK_' + @table + '_1] PRIMARY KEY NONCLUSTERED' +char(13)
	+'(' +char(13) + Column_Name + ' ASC ' + char(13)+')WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,         IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]'
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE  where TABLE_NAME = @table					

set @Sql = @SqlStart + SUBSTRING(@sql, 0, len(@sql)-1) + @sqlEnd
END
else
BEGIN
     set @Sql = @SqlStart + SUBSTRING(@sql, 0, len(@sql)-1) + ')'
END

print @sql				
			

Performance Point Services Excel Like Stacked Bar Chart

My favorite thing about excel is that you can do just about anything in it, like create a stacked bar chart representing the growth of a measure over time and represent each time period increment as a distinct segment in the each bar like the chart below.

My least favorite thing about excel is being are asked to replicate ‘simple’ excel charts using a PerformancePoint Analytical Chart. Easy right; just create a Quarter-To-Date Calculated Measure in your Cube (Contoso Cube used this example) throw it and your quarters into a chart and you’re done. Well the chart displays the data correctly, measure grows over time but the colors aren’t as nice and the chart does not stack by the value attributed to each quarter

Ok so what happens if we move the QTD measure to the bottom axis and quarters to the series, again the data is correct but this time although the chart identifies each segment attributed to each quarter the chart is no longer displays columns for each quarter.

So what we need is the second chart but with some new calculated measures on the bottom axis.

Each of the measures on the bottom axis represent a time period we are interested in and only returns the value for that period and prior periods. Let’s have a look at the MDX that I used to accomplish the visualization in this chart.

/*Q1 Measure 
Use iif to check if the currentmember of the fiscal quarter attribute = “Q1”, 
True – build string to represent the quarter member, use sttomember function to convert to member, complete --tuple with measure
False - null*/

CREATE MEMBER CURRENTCUBE.[measures].[Q1]
as
iif(LEFT([fiscal quarter].currentmember.membervalue, 2) = "Q1",
(strtomember("[fiscal quarter].&["+
RIGHT([Fiscal Year].currentmember.name, 4)+
RIGHT([Fiscal Quarter].currentmember.name, 1) + "]"),
[Measures].[Inventory On Hand Quantity]), null),
FORMAT_STRING = "Standard", 
VISIBLE = 1; 

/*Q2 Measure 
Use iif to check if the number of the period represented by the currentmember of the fiscal quarter attribute <=2, 
True – Check to see if empty, set to null, else build string to represent the quarter member, use strtomember function to convert to member
complete tuple with measure
False - null*/

CREATE MEMBER CURRENTCUBE.[measures].[Q2] as
iif( cint(right(left([fiscal quarter].currentmember.membervalue,2),1)) <= 2,
iif( cint(right(left([fiscal quarter].currentmember.membervalue,2),1)) <= 2 and
isEMPTY((strtomember("[fiscal quarter].&["+
RIGHT([Fiscal Year].currentmember.name, 4)+
RIGHT([Fiscal Quarter].currentmember.name, 1) + "]"),
[Measures].[Inventory On Hand Quantity])),
null,
(strtomember("[fiscal quarter].&["+
RIGHT([Fiscal Year].currentmember.name, 4)+
RIGHT([Fiscal Quarter].currentmember.name, 1) + "]"),
[Measures].[Inventory On Hand Quantity])),null),/*)*/
FORMAT_STRING = "Standard", 
VISIBLE = 1; 

/*Q3 and Q4 are the same as Q2 just increment the <= number*/

CREATE MEMBER CURRENTCUBE.[measures].[Q3] as
iif( cint(right(left([fiscal quarter].currentmember.membervalue,2),1)) <= 3,
iif( cint(right(left([fiscal quarter].currentmember.membervalue,2),1)) <= 3 and
isEMPTY((strtomember("[fiscal quarter].&["+
RIGHT([Fiscal Year].currentmember.name, 4)+
RIGHT([Fiscal Quarter].currentmember.name, 1) + "]"),
[Measures].[Inventory On Hand Quantity])),
null,
(strtomember("[fiscal quarter].&["+
RIGHT([Fiscal Year].currentmember.name, 4)+
RIGHT([Fiscal Quarter].currentmember.name, 1) + "]"),
[Measures].[Inventory On Hand Quantity])),null),/*)*/
FORMAT_STRING = "Standard", 
VISIBLE = 1; 

CREATE MEMBER CURRENTCUBE.[measures].[Q4] as
iif( cint(right(left([fiscal quarter].currentmember.membervalue,2),1)) <= 4,
iif( cint(right(left([fiscal quarter].currentmember.membervalue,2),1)) <= 4 and
isEMPTY((strtomember("[fiscal quarter].&["+
RIGHT([Fiscal Year].currentmember.name, 4)+
RIGHT([Fiscal Quarter].currentmember.name, 1) + "]"),
[Measures].[Inventory On Hand Quantity])),
null,
(strtomember("[fiscal quarter].&["+
RIGHT([Fiscal Year].currentmember.name, 4)+
RIGHT([Fiscal Quarter].currentmember.name, 1) + "]"),
[Measures].[Inventory On Hand Quantity])),null),/*)*/
FORMAT_STRING = "Standard", 
VISIBLE = 1; 

/*Use scope statements to set the value for the all member of the quarter attribute when combined with the calculated members to null*/

SCOPE(([Fiscal Quarter].[All],{[Measures].[Q1]})); 
THIS =  null; 
END SCOPE; 

SCOPE(([Fiscal Quarter].[All],{[Measures].[Q2]})); 
THIS =  null; 
END SCOPE; 

SCOPE(([Fiscal Quarter].[All],{[Measures].[Q3]})); 
THIS =  null; 
END SCOPE; 

SCOPE(([Fiscal Quarter].[All],{[Measures].[Q4]})); 
THIS =  null; 
END SCOPE; 

Generating multiple rows from a single record with start and end columns

Working on a project where the reporting requirement is to take a single record containing the overall total, the start and end period and generate multiple rows including all periods between the start and end. I knew that I could get the results that I needed with a common table expression (CTE) but time was of the essence so I turned to my trusty friend the ‘Cursor’. I know Cursors are for the weak and the lazy, but i needed to prototype something really fast and for me this was the easiest way. For those of you wondering why is he blogging about being weak and lazy, I did return to the CTE method and was rewarded with the required results using a fraction of the SQL and produced a stored procedure which is easier to modify and tweak as reporting requirements change.

Below you will find some sample code used to generate multiple rows from a single datarow containing a start and an end column. In this example I show both using integers and dates to denote the beginning and end and end values.

/*using integers as starting and ending values*/
create table #cteTempInt
(
id int identity (1,1)
,amount numeric(18,2)
, Divisor int
, starting int
, ending int
)

insert into #cteTempInt Values(1200, 6, 1, 6)
insert into #cteTempInt Values(4300, 10, 2, 11)
insert into #cteTempInt Values(2400, 4, 3, 6)

print 'results from integer sample'
;with t(id, periodAmount, period, ending) AS
(
select id, amount/divisor as periodAmount, starting as period, ending from #cteTempInt
union all
select id, periodAmount, period +1 as period , ending from t
where period+1 <= ending
)

select id, periodamount, period,
row_number() over(partition by id order by id,period) as paymentNumber from t order by id, period

DROP TABLE #cteTempInt

/*using dates as starting and ending values*/

create table #cteTempDate
(
id int identity (1,1)
,amount numeric(18,2)
, Divisor int
, starting date
, ending date
)

insert into #cteTempDate Values(1200, 6, '2012-01-01','2012-06-01')
insert into #cteTempDate Values(4300, 10,'2012-02-01','2012-11-01')
insert into #cteTempDate Values(2400, 4, '2012-05-01','2012-08-01')

print 'results from date sample'
;with t(id, periodAmount, period, ending) AS
(
select id, amount/divisor as periodAmount, starting as period, ending from #cteTempDate
union all
select id, periodAmount, dateadd(mm, 1, period) as period , ending from t
where dateadd(mm,1,period) <= ending
)
select id, periodamount, period,
row_number() over(partition by id order by id,period) as paymentNumber from t order by id, period

drop table #cteTempDate
results from integer sample
id          periodamount                            period      paymentNumber
----------- --------------------------------------- ----------- --------------------
1           200.0000000000000                       1           1
1           200.0000000000000                       2           2
1           200.0000000000000                       3           3
1           200.0000000000000                       4           4
1           200.0000000000000                       5           5
1           200.0000000000000                       6           6
2           430.0000000000000                       2           1
2           430.0000000000000                       3           2
2           430.0000000000000                       4           3
2           430.0000000000000                       5           4
2           430.0000000000000                       6           5
2           430.0000000000000                       7           6
2           430.0000000000000                       8           7
2           430.0000000000000                       9           8
2           430.0000000000000                       10          9
2           430.0000000000000                       11          10
3           600.0000000000000                       3           1
3           600.0000000000000                       4           2
3           600.0000000000000                       5           3
3           600.0000000000000                       6           4

results from date sample
id          periodamount                            period     paymentNumber
----------- --------------------------------------- ---------- --------------------
1           200.0000000000000                       2012-01-01 1
1           200.0000000000000                       2012-02-01 2
1           200.0000000000000                       2012-03-01 3
1           200.0000000000000                       2012-04-01 4
1           200.0000000000000                       2012-05-01 5
1           200.0000000000000                       2012-06-01 6
2           430.0000000000000                       2012-02-01 1
2           430.0000000000000                       2012-03-01 2
2           430.0000000000000                       2012-04-01 3
2           430.0000000000000                       2012-05-01 4
2           430.0000000000000                       2012-06-01 5
2           430.0000000000000                       2012-07-01 6
2           430.0000000000000                       2012-08-01 7
2           430.0000000000000                       2012-09-01 8
2           430.0000000000000                       2012-10-01 9
2           430.0000000000000                       2012-11-01 10
3           600.0000000000000                       2012-05-01 1
3           600.0000000000000                       2012-06-01 2
3           600.0000000000000                       2012-07-01 3
3           600.0000000000000                       2012-08-01 4