--***************************************************************************
--
--     	        Script Name:	SBE001_STP01_CT_Q.SQL
--
--     	        Object Name:	up_CT_Q (CT stands for Cut Typing)
--
-- 		Object Type:	Stored Procedure
--
--		Author:		Yakov Shlafman
--
--		Written:	08/20/2005
--
--		Language:	Transact-SQL
--
--		Platform:	Windows NT, SQL Server 2000
--
--		Database:       SBE
--
-----------------------------------------------------------------------------
--
--
--   Content:      Used to speed up the writting and checking processes, for 
Reverse Engineering and
--                 standards
--
--   Project Name: SBE (Standards by examples)
--   Subject area: EnforceStandards
-----------------------------------------------------------------------------
--     MODIFIED BY :     DATE : 		REASON:
-----------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'dbo.up_CT_Q')
   and OBJECTPROPERTY(id, N'IsProcedure') = 1)
   drop procedure dbo.up_CT_Q
GO
create proc up_CT_Q
-- Input parameters:
(

  @TableName         varchar(100) = null,
  @FormatNumber      int          = null,
  @TypeNumber        int          = null,
  @Alies             varchar(100) = null

)

AS

-- Standards note: each line of code should fit the width of the screen

BEGIN
SET NOCOUNT ON

-- is used to control indent
declare @ChangeIndent    int
declare @MaxFormatNumber int

set @ChangeIndent    = 5
set @MaxFormatNumber = 4

-- Produce a report for @FormatNumber = 1
if @FormatNumber = 1 and @TypeNumber is null
BEGIN
     select
	case
	       when ordinal_position = 1             then (' ' + column_name +
	            space( (select max(len(column_name)) + @ChangeIndent
	                      from INFORMATION_SCHEMA.COLUMNS
	                     where table_name = @TableName) - len(column_name)))
	       when ordinal_position > 1             then (',' + column_name +
	            space( (select max(len(column_name)) + @ChangeIndent
	                      from INFORMATION_SCHEMA.COLUMNS
	                     where table_name = @TableName) - len(column_name)))
	END as ColumnName
	, case
        -- The logic is based on the data type of a column
	-- Exact Numerics
	-- Integers
	       when data_type        = 'bigint'          then data_type
	       when data_type        = 'int'             then data_type
	       when data_type        = 'smallint'        then data_type
	       when data_type        = 'tinyint'         then data_type
	       when data_type        = 'bit'             then data_type
	-- decimal and numeric
	       when data_type        = 'decimal'
                    then data_type + '(' + rtrim(CAST(numeric_precision AS 
CHAR(7))) + ','
                                   + rtrim(CAST(numeric_scale AS CHAR(7))) 
+')'
	       when data_type        = 'numeric'
                    then data_type + '(' + rtrim(CAST(numeric_precision AS 
CHAR(7))) + ','
                                   + rtrim(CAST(numeric_scale AS CHAR(7))) 
+')'
	-- money and smallmoney
	       when data_type        = 'money'           then data_type
	       when data_type        = 'smallmoney'      then data_type
	-- Approximate Numerics
	       when data_type        = 'float'           then data_type
	       when data_type        = 'real'            then data_type
	-- datetime and smalldatetime
	       when data_type        = 'datetime'        then data_type
	       when data_type        = 'smalldatetime'   then data_type
	-- Character Strings
	       when data_type        = 'char'
                    then data_type + '(' + 
rtrim(CAST(character_maximum_length AS CHAR(7))) + ')'
	       when data_type        = 'varchar'
                    then data_type + '(' + 
rtrim(CAST(character_maximum_length AS CHAR(7))) + ')'
	       when data_type        = 'text'            then data_type
	-- Unicode Character Strings
	       when data_type        = 'nchar'
                    then data_type + '(' + 
rtrim(CAST(character_maximum_length AS CHAR(7))) + ')'
	       when data_type        = 'nvarchar'
                    then data_type + '(' + 
rtrim(CAST(character_maximum_length AS CHAR(7))) + ')'
	       when data_type        = 'ntext'           then data_type
	-- Binary Strings
	       when data_type        = 'binary'
                    then data_type + '(' + 
rtrim(CAST(character_maximum_length AS CHAR(7))) + ')'
	       when data_type        = 'varbinary'
                    then data_type + '(' + 
rtrim(CAST(character_maximum_length AS CHAR(7))) + ')'
	       when data_type        = 'image'           then data_type

	       else                                           data_type
	  END as DataType
      from INFORMATION_SCHEMA.COLUMNS
      where table_name = @TableName
      order by ordinal_position
END
else
-- Produce a report for @FormatNumber = 1 and @TypeNumber = 1
if @FormatNumber = 1 and @TypeNumber = 1
BEGIN
     select
	case
	       when ordinal_position = 1             then (' ' + column_name +
	            space( (select max(len(column_name)) + @ChangeIndent
	                      from INFORMATION_SCHEMA.COLUMNS
	                     where table_name = @TableName) - len(column_name)))
	       when ordinal_position > 1             then (',' + column_name +
	            space( (select max(len(column_name)) + @ChangeIndent
	                      from INFORMATION_SCHEMA.COLUMNS
	                     where table_name = @TableName) - len(column_name)))
	END as ColumnName
	, case
        -- The logic is based on the data type of a column
	-- Exact Numerics
	-- Integers
	       when data_type        = 'bigint'          then data_type
	       when data_type        = 'int'             then data_type
	       when data_type        = 'smallint'        then data_type
	       when data_type        = 'tinyint'         then data_type
	       when data_type        = 'bit'             then data_type
	-- decimal and numeric
	       when data_type        = 'decimal'
                    then data_type + '(' + rtrim(CAST(numeric_precision AS 
CHAR(7))) + ','
                                   + rtrim(CAST(numeric_scale AS CHAR(7))) 
+')'
	       when data_type        = 'numeric'
                    then data_type + '(' + rtrim(CAST(numeric_precision AS 
CHAR(7))) + ','
                                   + rtrim(CAST(numeric_scale AS CHAR(7))) 
+')'
	-- money and smallmoney
	       when data_type        = 'money'           then data_type
	       when data_type        = 'smallmoney'      then data_type
	-- Approximate Numerics
	       when data_type        = 'float'           then data_type
	       when data_type        = 'real'            then data_type
	-- datetime and smalldatetime
	       when data_type        = 'datetime'        then data_type
	       when data_type        = 'smalldatetime'   then data_type
	-- Character Strings
	       when data_type        = 'char'
                    then data_type + '(' + 
rtrim(CAST(character_maximum_length AS CHAR(7))) + ')'
	       when data_type        = 'varchar'
                    then data_type + '(' + 
rtrim(CAST(character_maximum_length AS CHAR(7))) + ')'
	       when data_type        = 'text'            then data_type
	-- Unicode Character Strings
	       when data_type        = 'nchar'
                    then data_type + '(' + 
rtrim(CAST(character_maximum_length AS CHAR(7))) + ')'
	       when data_type        = 'nvarchar'
                    then data_type + '(' + 
rtrim(CAST(character_maximum_length AS CHAR(7))) + ')'
	       when data_type        = 'ntext'           then data_type
	-- Binary Strings
	       when data_type        = 'binary'
                    then data_type + '(' + 
rtrim(CAST(character_maximum_length AS CHAR(7))) + ')'
	       when data_type        = 'varbinary'
                    then data_type + '(' + 
rtrim(CAST(character_maximum_length AS CHAR(7))) + ')'
	       when data_type        = 'image'           then data_type

	       else                                           data_type
	  END as DataType
,  case when COLUMNPROPERTY( OBJECT_ID(@TableName),column_name,'IsIdentity') 
= 1
             then 'identity(' + ltrim(cast(IDENT_SEED(@TableName) as 
varchar(5))) + ','
                     + ltrim(cast(IDENT_INCR(@TableName) as varchar(5))) + 
')'
        else space(1)
        END as IdentityProperty
,  case when COLUMNPROPERTY( OBJECT_ID(@TableName),column_name,'AllowsNull') 
= 1
             then 'NULL'
        else 'NOT NULL'
        END as NullIdentification
      from INFORMATION_SCHEMA.COLUMNS
      where table_name = @TableName
      order by ordinal_position
END
-- Produce a report for @FormatNumber = 2
else
if @FormatNumber = 2
BEGIN
     select
	case
	       when ordinal_position = 1             then ('Declare ' + '@' + 
column_name +
	            space( (select max(len(column_name)) + @ChangeIndent
	                      from INFORMATION_SCHEMA.COLUMNS
	                     where table_name = @TableName) - len(column_name)))
	       when ordinal_position > 1             then ('Declare ' + '@' + 
column_name +
	            space( (select max(len(column_name)) + @ChangeIndent
	                      from INFORMATION_SCHEMA.COLUMNS
	                     where table_name = @TableName) - len(column_name)))
	END as ColumnName
	, case
        -- The logic is based on the data type of a column
	-- Exact Numerics
	-- Integers
	       when data_type        = 'bigint'          then data_type
	       when data_type        = 'int'             then data_type
	       when data_type        = 'smallint'        then data_type
	       when data_type        = 'tinyint'         then data_type
	       when data_type        = 'bit'             then data_type
	-- decimal and numeric
	       when data_type        = 'decimal'
                    then data_type + '(' + rtrim(CAST(numeric_precision AS 
CHAR(7))) + ','
                                   + rtrim(CAST(numeric_scale AS CHAR(7))) 
+')'
	       when data_type        = 'numeric'
                    then data_type + '(' + rtrim(CAST(numeric_precision AS 
CHAR(7))) + ','
                                   + rtrim(CAST(numeric_scale AS CHAR(7))) 
+')'
	-- money and smallmoney
	       when data_type        = 'money'           then data_type
	       when data_type        = 'smallmoney'      then data_type
	-- Approximate Numerics
	       when data_type        = 'float'           then data_type
	       when data_type        = 'real'            then data_type
	-- datetime and smalldatetime
	       when data_type        = 'datetime'        then data_type
	       when data_type        = 'smalldatetime'   then data_type
	-- Character Strings
	       when data_type        = 'char'
                    then data_type + '(' + 
rtrim(CAST(character_maximum_length AS CHAR(7))) + ')'
	       when data_type        = 'varchar'
                    then data_type + '(' + 
rtrim(CAST(character_maximum_length AS CHAR(7))) + ')'
	       when data_type        = 'text'            then data_type
	-- Unicode Character Strings
	       when data_type        = 'nchar'
                    then data_type + '(' + 
rtrim(CAST(character_maximum_length AS CHAR(7))) + ')'
	       when data_type        = 'nvarchar'
                    then data_type + '(' + 
rtrim(CAST(character_maximum_length AS CHAR(7))) + ')'
	       when data_type        = 'ntext'           then data_type
	-- Binary Strings
	       when data_type        = 'binary'
                    then data_type + '(' + 
rtrim(CAST(character_maximum_length AS CHAR(7))) + ')'
	       when data_type        = 'varbinary'
                    then data_type + '(' + 
rtrim(CAST(character_maximum_length AS CHAR(7))) + ')'
	       when data_type        = 'image'           then data_type

	       else                                           data_type
	  END as DataType
      from INFORMATION_SCHEMA.COLUMNS
      where table_name = @TableName
      order by ordinal_position
END
else
if @FormatNumber = 3  and @TypeNumber = 2 and @Alies is not null
BEGIN

select
  case
       when ordinal_position = 1
           then (' ' + @Alies + '.' + column_name +
                space( (select max(len(column_name))
                          from INFORMATION_SCHEMA.COLUMNS
                         where table_name = @TableName) - len(column_name)))
       when ordinal_position > 1
           then (',' + @Alies + '.' + column_name +
                space( (select max(len(column_name))
                         from INFORMATION_SCHEMA.COLUMNS
                        where table_name = @TableName) - len(column_name)))
  END as ColumnName
from INFORMATION_SCHEMA.COLUMNS
where table_name = @TableName
order by ordinal_position

END


else
if @FormatNumber = 3 and @TypeNumber = 1
BEGIN

select
  case
       when ordinal_position = 1
            then (' ' + column_name +
                 space( (select max(len(column_name))
                           from INFORMATION_SCHEMA.COLUMNS
                          where table_name = @TableName) - len(column_name))
                       )
       when ordinal_position > 1             then (',' + column_name +
                 space( (select max(len(column_name))
                           from INFORMATION_SCHEMA.COLUMNS
                          where table_name = @TableName) - len(column_name))
                       )
  END as ColumnName
from INFORMATION_SCHEMA.COLUMNS
where table_name = @TableName
order by ordinal_position

END


else
if @FormatNumber = 3  and @TypeNumber = 3 and @Alies is not null
BEGIN

	select
	  case
	       when ordinal_position = 1
                    then (' ' + @Alies + '.' + column_name +
	                  space( (select max(len(column_name))
                                    from INFORMATION_SCHEMA.COLUMNS
                                   where table_name = @TableName) - 
len(column_name)))
	                + space(5) + 'as ' + '[' + column_name + ']'
	       when ordinal_position > 1
                    then (',' + @Alies + '.' + column_name +
	                  space( (select max(len(column_name))
                                    from INFORMATION_SCHEMA.COLUMNS
                                   where table_name = @TableName) - 
len(column_name)))
	                 + space(5) + 'as ' + '[' + column_name + ']'
	  END as ColumnName
	from INFORMATION_SCHEMA.COLUMNS
	where table_name = @TableName
	order by ordinal_position
END
else
if @FormatNumber = 4
BEGIN

	select
	  case
	       when ordinal_position = 1
                    then (' ' + column_name +
	                  space( (select max(len(column_name))
                                    from INFORMATION_SCHEMA.COLUMNS
                                   where table_name = @TableName) - 
len(column_name)))
	                + space(5) + '= ' + '@' + column_name
	       when ordinal_position > 1
                    then (',' + column_name +
	                  space( (select max(len(column_name))
                                    from INFORMATION_SCHEMA.COLUMNS
                                   where table_name = @TableName) - 
len(column_name)))
	                 + space(5) + '= ' + '@' + column_name
	  END as ColumnName
	from INFORMATION_SCHEMA.COLUMNS
	where table_name = @TableName
	order by ordinal_position
END

else
-- Check if the requested table exists in the current database
if @TableName is not null and not exists ( select * from 
INFORMATION_SCHEMA.TABLES where table_name = @TableName )
   BEGIN
        select 'The table or view ' + @TableName + ' does not exist in the 
current catalog.' +
               'Please check the table name spelling'
        as user_message
   END
else
-- Check that the input parameter @FormatNumber less or equal 6
if @FormatNumber > @MaxFormatNumber
   BEGIN
        select 'The input parameter @FormatNumber should be less or equal ' 
+ cast(@MaxFormatNumber as char(1))
        as user_message
   END
else
if @TableName is null
BEGIN
Print '----------------------------'
Print 'DOCUMENTATION and USAGE'
Print '----------------------------'
Print 'The user defined stored procedure up_CT_Q is used for Reverse 
Engineering and'
Print 'for simplifying the script writing and debugging processes.'
Print 'The first input parameter is the table name'
Print 'The second input parameter is the Format Number that represents the 
format of the output.'
Print 'FormatNumber = 1, column name and its datatype list is returned.'
Print 'Usefull as input parameters list of a stored procedure'
Print 'FormatNumber = 1 Type 1, defines column with identity property, its 
seed and increment'
Print 'shows if column allow null or not'
Print 'FormatNumber = 2, column name prefixed by @ sign.'
Print 'Usefull for declaring variables in a stored procedure'
Print 'FormatNumber = 3 Type 1, returns all column names, in vertical, one 
column table format.'
Print 'In this format a Select or Insert statement is easier to read and 
manage if there is a '
Print 'need to comment out a column'
Print 'FormatNumber = 3 Type 2, in addition to type 1, all column names are 
prefixed by alias name.'
Print 'FormatNumber = 3 Type 3, makes easier the column renaming coding.'
Print 'FormatNumber = 4, is used in "where" clause of Select, Update or 
Delete statement.'
Print 'If table name is not supplied the procedure returns the description 
and usage examples of this procedure.'
Print 'Examples, using table Customers in Northwind database'
Print 'Examples:'
Print 'Example 1, Format 1, Type 1              exec up_CT_Q Customers,1'
Print 'Example 1, Format 1,                     exec up_CT_Q Customers,1,1'
Print 'Example 2, Format 2,                     exec up_CT_Q Customers,2'
Print 'Example 3, Format 3, Type 1              exec up_CT_Q Customers,3,1'
Print 'Example 3, Format 3, Type 2              exec up_CT_Q 
Customers,3,2,Cust'
Print 'Example 3, Format 3, Type 3              exec up_CT_Q 
Customers,3,3,Cust'
Print 'Example 4, Format 4,                     exec up_CT_Q Customers,4'
Print 'Example 5, to get procedure description: exec up_CT_Q'
Print 
'------------------------------------------------------------------------------------------------------------'
END



SET NOCOUNT OFF

END
GO

----------------------------
-- USAGE and TEST AREA
----------------------------
-- Test in the Northwind database
-- exec up_CT_Q 'Customers',1
-- exec up_CT_Q 'Categories',1, 1
-- exec up_CT_Q 'Customers',2
-- exec up_CT_Q 'Customers',3,1
-- exec up_CT_Q 'Customers',3,2,'Cust'
-- exec up_CT_Q 'Customers',3,3,'Cust'
-- exec up_CT_Q 'Customers',4
-- exec up_CT_Q 'Customers',7
-- exec up_CT_Q