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