Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Script Library
 PROC: TableSchemaToXML

Author  Topic 

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-02-22 : 11:31:41
Well I've always wanted the entire table schema (columns, keys) in XML so I can use an XSLT document to render some SQL code that will create procedures for me... thought maybe somebody else would find it useful...

IF OBJECT_ID('TableSchemaToXML') IS NOT NULL AND OBJECTPROPERTY(OBJECT_ID('TableSchemaToXML'), N'IsProcedure') = 1
DROP PROCEDURE TableSchemaToXML
GO

CREATE PROCEDURE TableSchemaToXML(@TABLE_NAME NVARCHAR(256)) AS
SET NOCOUNT ON

-- DECLARE @TABLE_NAME NVARCHAR(256)
DECLARE @COLUMN_NAME NVARCHAR(256)
DECLARE @ORDINAL_POSITION VARCHAR(256)
DECLARE @COLUMN_DEFAULT VARCHAR(256)
DECLARE @IS_NULLABLE VARCHAR(256)
DECLARE @DATA_TYPE VARCHAR(256)
DECLARE @CHARACTER_MAXIMUM_LENGTH VARCHAR(256)
DECLARE @NUMERIC_PRECISION VARCHAR(256)
DECLARE @NUMERIC_SCALE VARCHAR(256)
DECLARE @PRIMARY_KEY VARCHAR(256)
DECLARE @FOREIGN_KEY VARCHAR(256)
DECLARE @TAG_TYPE BIT

-- SELECT @TABLE_NAME = TABLE_NAME
-- FROM INFORMATION_SCHEMA.TABLES
-- WHERE TABLE_TYPE = 'BASE TABLE'
-- AND OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), N'IsUserTable') = 1

PRINT '<table name="' + @TABLE_NAME + '">'

SELECT *
INTO #TABLE_COLUMNS
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TABLE_NAME
--SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME = 'tworkflow'

WHILE (SELECT COUNT(*) FROM #TABLE_COLUMNS) > 0
BEGIN
SELECT @COLUMN_NAME = COLUMN_NAME,
@ORDINAL_POSITION = CONVERT(VARCHAR, ORDINAL_POSITION),
@COLUMN_DEFAULT =
CASE
WHEN COLUMN_DEFAULT IS NULL THEN ''
ELSE CHAR(10) + CHAR(9) + ' default="' + COLUMN_DEFAULT + '"'
END,
@IS_NULLABLE =
CASE UPPER(IS_NULLABLE)
WHEN 'YES' THEN 'true'
ELSE 'false'
END,
@DATA_TYPE = DATA_TYPE,
@CHARACTER_MAXIMUM_LENGTH =
CASE
WHEN CHARACTER_MAXIMUM_LENGTH IS NULL THEN ''
ELSE CHAR(10) + CHAR(9) + ' length="' + CONVERT(VARCHAR, CHARACTER_MAXIMUM_LENGTH) + '"'
END,
@NUMERIC_PRECISION =
CASE
WHEN NUMERIC_PRECISION IS NULL THEN ''
ELSE CHAR(10) + CHAR(9) + ' precision="' + CONVERT(VARCHAR, NUMERIC_PRECISION) + '"'
END,
@NUMERIC_SCALE =
CASE
WHEN NUMERIC_SCALE IS NULL THEN ''
WHEN NUMERIC_SCALE > 0 THEN CHAR(10) + CHAR(9) + ' numeric-scale="' + CONVERT(VARCHAR, NUMERIC_SCALE) + '"'
ELSE ''
END
FROM #TABLE_COLUMNS
WHERE ORDINAL_POSITION = (SELECT MIN(ORDINAL_POSITION) FROM #TABLE_COLUMNS)

SELECT *
INTO #COLUMN_CONSTRAINTS
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
WHERE TABLE_NAME = @TABLE_NAME
AND COLUMN_NAME = @COLUMN_NAME

-- IF (SELECT COUNT(*) FROM #COLUMN_CONSTRAINTS) > 0
-- SET @TAG_TYPE = 0
-- ELSE
SET @TAG_TYPE = 1

IF EXISTS(SELECT * FROM #COLUMN_CONSTRAINTS WHERE COLUMN_NAME = @COLUMN_NAME AND OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_NAME), N'IsPrimaryKey') = 1)
SET @PRIMARY_KEY = CHAR(10) + CHAR(9) + ' primary-key="true"'
ELSE
SET @PRIMARY_KEY = ''

IF EXISTS(SELECT * FROM #COLUMN_CONSTRAINTS WHERE COLUMN_NAME = @COLUMN_NAME AND OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_NAME), N'IsForeignKey') = 1)
SELECT @FOREIGN_KEY = CHAR(10) + CHAR(9) + ' foreign-key-table="' + OBJECT_NAME(sysreferences.rkeyid) + '"' + CHAR(10) +
CHAR(9) + ' foreign-key-column="' + INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME + '"'
FROM sysreferences,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE sysreferences.constid = (SELECT OBJECT_ID(CONSTRAINT_NAME) FROM #COLUMN_CONSTRAINTS WHERE COLUMN_NAME = @COLUMN_NAME AND OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_NAME), N'IsForeignKey') = 1)
AND INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME = (SELECT CONSTRAINT_NAME FROM #COLUMN_CONSTRAINTS WHERE COLUMN_NAME = @COLUMN_NAME AND OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_NAME), N'IsForeignKey') = 1)
ELSE
SET @FOREIGN_KEY = ''

PRINT CHAR(9) + '<column name="' + @COLUMN_NAME + '"' + CHAR(10) +
CHAR(9) + ' position="' + @ORDINAL_POSITION + '"' +
@COLUMN_DEFAULT + CHAR(10) +
CHAR(9) + ' nullable="' + @IS_NULLABLE + '"' + CHAR(10) +
CHAR(9) + ' data-type="' + @DATA_TYPE + '"' +
@CHARACTER_MAXIMUM_LENGTH +
@NUMERIC_PRECISION +
@NUMERIC_SCALE +
@PRIMARY_KEY +
@FOREIGN_KEY +
CASE @TAG_TYPE WHEN 1 THEN '/>' ELSE '>' END

DROP TABLE #COLUMN_CONSTRAINTS

DELETE
FROM #TABLE_COLUMNS
WHERE COLUMN_NAME = @COLUMN_NAME
END

DROP TABLE #TABLE_COLUMNS

PRINT '</table>'
GO

Enjoy!

Edited by - onamuji on 02/22/2002 12:02:42
   

- Advertisement -