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),
@table_only =
@Table
--
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
SELECT
c.name AS COLUMN_NAME,
st.name + CASE c.is_identity WHEN 1 THEN ' identity' ELSE '' END AS TYPE_NAME,
CASE
WHEN
st.name IN ('nchar','nvarchar','ntext') THEN c.max_length / 2
WHEN
st.name IN ('timestamp') THEN c.max_length
ELSE
c.precision
END AS PRECISION,
CASE
WHEN
st.name IN ('datetime')
THEN
c.max_length *
2
ELSE
c.max_length
END AS LENGTH,
CASE
WHEN
st.name IN ('nchar','nvarchar','ntext')
THEN
NULL
ELSE
c.scale
END AS SCALE,
c.is_nullable
AS NULLABLE,
dc.definition
AS COLUMN_DEF,
CASE
WHEN
c.column_id <
t.max_column_id_used
THEN
0
ELSE
1
END AS last_column,
c.column_id
AS column_number,
ROW_NUMBER() OVER (ORDER BY c.column_id) AS numbered_column,
CASE
WHEN
UPPER(c.name) LIKE '%[^A-Z0-9_]%'
THEN
1
ELSE
0
END AS needs_brackets,
c.is_computed
INTO
#columns_new
FROM
sys.tables
t
INNER JOIN sys.all_columns
c
ON
t.object_id = c.object_id
INNER JOIN sys.types st
ON
c.user_type_id =
st.user_type_id
LEFT JOIN sys.default_constraints
dc
ON
c.default_object_id =
dc.object_id
WHERE
t.name = @table_only
SELECT
@max_ORDINAL_POSITION = MAX(column_number) FROM #columns_new
IF @SortAlpha = -1
BEGIN
SELECT * FROM #columns_new
END
--
create unique index, always
SET @sql = 'CREATE UNIQUE CLUSTERED
INDEX UQ_temp_new ON #columns_new (' +
CASE
@SortAlpha
WHEN
1
THEN
'COLUMN_NAME'
ELSE
'column_number'
END + ')'
EXEC sp_executesql @sql
IF @SortAlpha IN (0,1)
BEGIN
SELECT
COLUMN_NAME,
TYPE_NAME,
PRECISION,
LENGTH,
SCALE,
NULLABLE,
COLUMN_DEF
FROM
#columns_new
WHERE
CHARINDEX(@column, COLUMN_NAME) > 0
OR @column = ''
END
IF @SortAlpha = 2
BEGIN
-- SELECT
SELECT 'SELECT' AS
SELECT_STATEMENT, 0 AS
ORDINAL_POSITION
INTO
#columns_new_2
UNION
-- list all
columns with trailing commas
SELECT
@tab +
CASE
WHEN @column = '' THEN '' ELSE @column + '.' END +
CASE
WHEN needs_brackets =
1 THEN '[' ELSE '' END +
COLUMN_NAME +
CASE
WHEN needs_brackets =
1 THEN ']' ELSE '' END +
CASE
WHEN
column_number < @max_ORDINAL_POSITION
THEN
','
ELSE
''
END
AS COLUMN_NAME,
column_number
FROM
#columns_new
UNION
-- FROM
SELECT 'FROM',
@max_ORDINAL_POSITION + 1
UNION
-- table name
SELECT
@tab + @Table +
' ' + @column, @max_ORDINAL_POSITION +
2
SELECT
SELECT_STATEMENT
FROM
#columns_new_2
ORDER BY
ORDINAL_POSITION
DROP TABLE #columns_new_2
END
-----------------------------------------------------------
IF @SortAlpha IN (3,4)
BEGIN
-- exclude the
columns that we don't insert
DELETE
#columns_new
WHERE
TYPE_NAME
IN ('timestamp','image','text','ntext')
OR TYPE_NAME LIKE '%identity'
OR is_computed
= 1
-- INSERT
SELECT 'INSERT ' + @table AS INSERT_STATEMENT, -1 AS
ORDINAL_POSITION
INTO
#insert_3
UNION
SELECT
@tab + '(', 0
UNION
SELECT
@tab +
CASE
WHEN needs_brackets =
1 THEN '[' ELSE '' END +
COLUMN_NAME +
CASE
WHEN needs_brackets =
1 THEN ']' ELSE '' END +
CASE
WHEN
column_number < @max_ORDINAL_POSITION
THEN
','
ELSE
''
END
AS COLUMN_NAME,
column_number
FROM
#columns_new
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 @SortAlpha =
4 THEN '@' ELSE '' END +
CASE
WHEN @SortAlpha =
3 AND needs_brackets =
1 THEN '[' ELSE '' END +
CASE
WHEN @SortAlpha =
4
THEN
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(COLUMN_NAME,' ','_'),')','_'),'(','_'),'%','_'),'/','_')
ELSE
COLUMN_NAME
END
+
CASE
WHEN @SortAlpha =
3 AND needs_brackets =
1 THEN ']' ELSE '' END +
CASE
WHEN
column_number < @max_ORDINAL_POSITION
THEN
','
ELSE
''
END
AS COLUMN_NAME,
column_number + @max_ORDINAL_POSITION +
3
FROM
#columns_new
UNION
-- FROM
SELECT 'FROM',
@max_ORDINAL_POSITION + 1000
WHERE
@SortAlpha = 3
UNION
-- table name
SELECT
@tab + 'source_of_data_for_insert_to_'
+ @Table ,
@max_ORDINAL_POSITION + 1001
WHERE
@SortAlpha = 3
SELECT
INSERT_STATEMENT,
ORDINAL_POSITION
INTO
#output_3
FROM
#insert_3
UNION
SELECT
INSERT_STATEMENT,
ORDINAL_POSITION
FROM
#select_3
UNION
SELECT 'DECLARE', -9999
WHERE
@SortAlpha = 4
UNION
SELECT '', -2
WHERE
@SortAlpha = 4
UNION
SELECT
@tab +
'@' +
CASE
WHEN @SortAlpha =
3 AND needs_brackets =
1 THEN '[' ELSE '' END +
CASE
WHEN @SortAlpha =
4
THEN
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(COLUMN_NAME,' ','_'),')','_'),'(','_'),'%','_'),'/','_')
ELSE
COLUMN_NAME
END
+
CASE
WHEN @SortAlpha =
3 AND needs_brackets =
1 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
+
CASE
WHEN
column_number < @max_ORDINAL_POSITION
THEN
','
ELSE
''
END
AS COLUMN_NAME,
column_number + @max_ORDINAL_POSITION +
3 - 9999
FROM
#columns_new
WHERE
@SortAlpha = 4
SELECT
INSERT_STATEMENT
FROM
#output_3
ORDER BY
ORDINAL_POSITION
DROP TABLE #insert_3
DROP TABLE #select_3
DROP TABLE #output_3
END
-----------------------------------------------------------
DROP TABLE #columns_new
-----------------------------------------------------------
GO