-- Gen input parameter list

SELECT ', @' + Column_name + ' '+ SPACE(40-LEN(COLUMN_NAME)) 
+ SUBSTRING(Data_Type
+ CASE  WHEN Data_type in ('decimal') 
	THEN '('+convert(varchar(25),Numeric_Precision)+','+convert(varchar(25),Numeric_Scale)+ ')'
	WHEN Data_type in ('char','varchar') 
	THEN '('+convert(varchar(25),CHARACTER_MAXIMUM_LENGTH)+')'
	ELSE ''
  END,1,30) AS Datatype
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_NAME = 'Approver'
ORDER BY ORDINAL_POSITION

-- Gen OPENXML

SELECT ', [' + Column_name + '] '
+ SUBSTRING(Data_Type
+ CASE  WHEN Data_type in ('decimal') 
	THEN '('+convert(varchar(25),Numeric_Precision)+','+convert(varchar(25),Numeric_Scale)+ ')'
	WHEN Data_type in ('char','varchar') 
	THEN '('+convert(varchar(25),CHARACTER_MAXIMUM_LENGTH)+')'
	ELSE ''
  END,1,30) AS Datatype
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_NAME = 'StartUp'
ORDER BY ORDINAL_POSITION

--Gen XML Update 
SELECT ' , a.[' + Column_name + ']=  b.[' + Column_name + '] '
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_NAME = 'FutureRent'
ORDER BY ORDINAL_POSITION



--Generate Update

SELECT ' , ' + Column_name + SPACE(40-LEN(COLUMN_NAME)) + '= @' + Column_name + ' '
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_NAME = 'Approver'
ORDER BY ORDINAL_POSITION

--Generate SELECT

SELECT ' , p.[' + Column_name + ']'+ SPACE(40-LEN(COLUMN_NAME)) + ' -- ' + TABLE_NAME
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_NAME IN ('PIF')
-- 'FutureRent'
--,'Startup'
--,'Financials'
--,'Disposition'
--,'PIF_MEP'
--,'MEPAllocation'
--,'ProjectCost'
--,'NewLease'
--,'MEP')
ORDER BY TABLE_NAME, ORDINAL_POSITION

--Generate SELECT Parameters

SELECT ' , @' + Column_name
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_NAME = 'MEPContact'
ORDER BY ORDINAL_POSITION

--Generate INSERT INTO

SELECT ' , [' + Column_name + ']'
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_NAME = 'MEPContact'
ORDER BY ORDINAL_POSITION