IF OBJECT_ID('dbo.wm_columns') IS NOT NULL

DROP PROC dbo.wm_columns

GO

 

CREATE PROC dbo.wm_columns

       (

       @Table nvarchar(257),

       @SortAlpha int = 0,

       @column sysname = ''

       )

AS

 

DECLARE

       @dot int,

       @max_ORDINAL_POSITION int,

       @schema sysname,

       @sql nvarchar(4000),

       @tab nvarchar,

       @table_only sysname

 

SELECT

       @dot = CHARINDEX('.',@Table),

       @schema = 'dbo',

       @tab = CHAR(9)

 

-- if table name has a schema prefix, break apart

IF @dot > 0

BEGIN

       SELECT

              @schema = LEFT(@Table, @dot - 1),

              @table_only = RIGHT(@Table, LEN(@Table) - @dot)

END

 

SET NOCOUNT ON

 

IF OBJECT_ID(N'tempdb..#columns', N'U') IS NOT NULL

DROP TABLE #columns

 

CREATE TABLE #columns

       (

       TABLE_QUALIFIER sysname,

       TABLE_OWNER sysname,

       TABLE_NAME sysname,

       COLUMN_NAME sysname,

       DATA_TYPE smallint,

       [TYPE_NAME] sysname,

       [PRECISION] int,

       [LENGTH] int,

       SCALE smallint,

       RADIX smallint,

       NULLABLE smallint,

       REMARKS varchar(254),

       COLUMN_DEF nvarchar(4000),

       SQL_DATA_TYPE smallint,

       SQL_DATETIME_SUB smallint,

       CHAR_OCTET_LENGTH int,

       ORDINAL_POSITION int,

       IS_NULLABLE varchar(254),

       SS_DATA_TYPE tinyint

       )

 

INSERT INTO #columns

EXEC sp_columns @table_only, @schema

 

SELECT @max_ORDINAL_POSITION = MAX(ORDINAL_POSITION) FROM #columns

 

SET @sql = 'CREATE UNIQUE CLUSTERED INDEX UQ_temp ON #columns (' +

       CASE @SortAlpha

              WHEN 1

              THEN 'COLUMN_NAME'

              ELSE 'ORDINAL_POSITION'

       END + ')'

 

EXEC sp_executesql @sql

 

 

IF @SortAlpha = 2

BEGIN

       -- SELECT

       SELECT 'SELECT' AS SELECT_STATEMENT, 0 AS ORDINAL_POSITION

       INTO #select_2

 

       UNION

 

       -- list all columns with trailing commas

       SELECT

              @tab +

              CASE WHEN @column = '' THEN '' ELSE @column + '.' END +

              CASE WHEN UPPER(COLUMN_NAME) LIKE '%[^A-Z0-9_]%' THEN '[' ELSE '' END +          

              COLUMN_NAME +

              CASE WHEN UPPER(COLUMN_NAME) LIKE '%[^A-Z0-9_]%' THEN ']' ELSE '' END +

              CASE

                     WHEN ORDINAL_POSITION < @max_ORDINAL_POSITION

                     THEN ','

                     ELSE ''

              END AS COLUMN_NAME,

              ORDINAL_POSITION

       FROM

              #columns

 

       UNION

      

       -- FROM

       SELECT 'FROM', @max_ORDINAL_POSITION + 1

 

       UNION

 

       -- table name

       SELECT @tab + @Table + ' ' + @column, @max_ORDINAL_POSITION + 2

 

       -- list in logical order

       SELECT

              SELECT_STATEMENT

       FROM

              #select_2

       ORDER BY

              ORDINAL_POSITION

 

       DROP TABLE #select_2

END

 

IF @SortAlpha IN ( 3, 4 )

BEGIN

       -- INSERT

       SELECT 'INSERT ' + @table AS INSERT_STATEMENT, -1 AS ORDINAL_POSITION

       INTO #insert_3

 

       UNION

 

       SELECT @tab + '(', 0

      

       UNION

 

       SELECT

              @tab +

              CASE

                     WHEN [TYPE_NAME] IN ('timestamp','image')

                     THEN '--'

                     WHEN [TYPE_NAME] LIKE '%identity%'

                     THEN '--'

                     ELSE ''

              END +

              CASE WHEN UPPER(COLUMN_NAME) LIKE '%[^A-Z0-9_]%' THEN '[' ELSE '' END +

              COLUMN_NAME +

              CASE WHEN UPPER(COLUMN_NAME) LIKE '%[^A-Z0-9_]%' THEN ']' ELSE '' END +

              CASE

                     WHEN ORDINAL_POSITION < @max_ORDINAL_POSITION

                     THEN ','

                     ELSE ''

              END AS COLUMN_NAME,

              ORDINAL_POSITION

       FROM

              #columns

 

       UNION

 

       SELECT @tab + ')', @max_ORDINAL_POSITION + 1

 

 

       -- SELECT

       SELECT 'SELECT' AS INSERT_STATEMENT, @max_ORDINAL_POSITION + 2 AS ORDINAL_POSITION

       INTO #select_3

 

       UNION

 

       -- list all columns with trailing commas

       SELECT

              @tab +

              CASE

                     WHEN [TYPE_NAME] IN ('timestamp','image')

                     THEN '--'

                     WHEN [TYPE_NAME] LIKE '%identity%'

                     THEN '--'

                     ELSE ''

              END +

              CASE WHEN UPPER(COLUMN_NAME) LIKE '%[^A-Z0-9_]%' THEN '[' ELSE '' END +

              COLUMN_NAME +

              CASE WHEN UPPER(COLUMN_NAME) LIKE '%[^A-Z0-9_]%' THEN ']' ELSE '' END +

              CASE

                     WHEN ORDINAL_POSITION < @max_ORDINAL_POSITION

                     THEN ','

                     ELSE ''

              END +

              CASE

                     WHEN @SortAlpha = 4

                     THEN ' -- ' + [TYPE_NAME] +

                           CASE

                                  WHEN [TYPE_NAME] LIKE '%char%'

                                  THEN ' (' + CAST([PRECISION] AS nvarchar) + ') '

                                  ELSE ''

                           END

                     ELSE ''

              END AS COLUMN_NAME,

              ORDINAL_POSITION + @max_ORDINAL_POSITION + 3

       FROM

              #columns

 

       UNION

      

       -- FROM

       SELECT 'FROM', @max_ORDINAL_POSITION + 1000

 

       UNION

 

       -- table name

       SELECT @tab + 'source_of_data_for_insert_to_' + @Table , @max_ORDINAL_POSITION + 1001

 

 

       SELECT

              INSERT_STATEMENT,

              ORDINAL_POSITION

       INTO

              #output_3

       FROM

              #insert_3

      

       UNION

      

       SELECT

              INSERT_STATEMENT,

              ORDINAL_POSITION

       FROM

              #select_3

      

       SELECT

              INSERT_STATEMENT

       FROM

              #output_3

       ORDER BY

              ORDINAL_POSITION

 

 

       DROP TABLE #insert_3

       DROP TABLE #select_3

       DROP TABLE #output_3

END

 

IF @SortAlpha IN ( 0, 1 )

BEGIN

       SELECT

              COLUMN_NAME,

              [TYPE_NAME],

              [PRECISION],

              [LENGTH],

              SCALE,

              NULLABLE,

              COLUMN_DEF

       FROM

              #columns

       WHERE

              (

              @column = ''

              OR

              CHARINDEX(@column, COLUMN_NAME) > 0

              )

END

 

DROP TABLE #columns

 

GO