DACBuilder – Data Access objects generation tool based on XML and XSL templates transformation

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