IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'GetTableColumnsXML')
BEGIN
DROP PROCEDURE GetTableColumnsXML
END
GO
/*
name: GetTableColumnsXML
type: XMNL stored procedure
parameters:
@TABLE_NAME VARCHAR(8000) -- table name
scope: returns columns information for @TABLE_NAME table
*/
CREATE PROCEDURE GetTableColumnsXML(
@TABLE_NAME VARCHAR(8000) -- table name
)
AS
DECLARE @BASE VARCHAR(100)
SET @BASE = ''
IF EXISTS(SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = @TABLE_NAME
AND INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME IN ('INSUSR', 'INSDT', 'UPDUSR', 'UPDDT'))
SET @BASE = 'Log'
IF EXISTS(SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = @TABLE_NAME
AND INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME = 'code')
SET @BASE = 'Code'
IF EXISTS(SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = @TABLE_NAME
AND INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME IN ('DTINI', 'DTFIN'))
SET @BASE = 'Historic'
SELECT
1 AS TAG,
NULL AS PARENT,
@TABLE_NAME AS [table!1!name],
UPPER(SUBSTRING(@TABLE_NAME, 1, 1)) + LOWER(SUBSTRING(@TABLE_NAME, 2, LEN(@TABLE_NAME) - 1)) AS [table!1!friendly_name],
@BASE AS [table!1!base],
'' AS [table!1!namespace],
'' AS [table!1!abbreviation],
NULL AS [column!2!column_name],
NULL AS [column!2!column_friendly_name],
NULL AS [column!2!ordinal_position],
NULL AS [column!2!column_default],
NULL AS [column!2!is_nullable],
NULL AS [column!2!data_type],
NULL AS [column!2!character_maximum_length],
NULL AS [column!2!character_octet_length],
NULL AS [column!2!numeric_precision],
NULL AS [column!2!numeric_precision_radix],
NULL AS [column!2!numeric_scale],
NULL AS [column!2!datetime_precision],
NULL AS [column!2!identity],
NULL AS [column!2!constraint_type],
NULL AS [column!2!referenced_table],
NULL AS [column!2!column_description]
UNION
SELECT
2 AS TAG,
1 AS PARENT,
NULL,
NULL,
NULL,
NULL,
NULL,
INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME,
INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME,
INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION,
INFORMATION_SCHEMA.COLUMNS.COLUMN_DEFAULT,
INFORMATION_SCHEMA.COLUMNS.IS_NULLABLE,
INFORMATION_SCHEMA.COLUMNS.DATA_TYPE,
INFORMATION_SCHEMA.COLUMNS.CHARACTER_MAXIMUM_LENGTH,
INFORMATION_SCHEMA.COLUMNS.CHARACTER_OCTET_LENGTH,
INFORMATION_SCHEMA.COLUMNS.NUMERIC_PRECISION,
INFORMATION_SCHEMA.COLUMNS.NUMERIC_PRECISION_RADIX,
INFORMATION_SCHEMA.COLUMNS.NUMERIC_SCALE,
INFORMATION_SCHEMA.COLUMNS.DATETIME_PRECISION,
COLUMNPROPERTY(OBJECT_ID(@TABLE_NAME),
CASE INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION WHEN 1 THEN INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME ELSE '' END,
'IsIdentity'),
isnull(INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE, ''),
isnull(REF_TABLES.TABLE_NAME, ''),
dbo.fnGetExtendedProperty(N'MS_Description', @TABLE_NAME, INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE
ON INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME = INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME
AND INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS ON INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME = INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME
LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS ON INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME = INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS.CONSTRAINT_NAME
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS REF_TABLES ON INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS.UNIQUE_CONSTRAINT_NAME = REF_TABLES.CONSTRAINT_NAME
WHERE
INFORMATION_SCHEMA.COLUMNS.TABLE_NAME LIKE @TABLE_NAME
--AND INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME NOT IN ('INSUSR', 'INSDT', 'UPDUSR', 'UPDDT', 'FKCMY')
ORDER BY [column!2!ordinal_position]
FOR XML EXPLICIT
GO