Import SQL feature

[ondras]

From j...@iilab.org on February 03, 2015 19:18:39

What steps will reproduce the problem? 1. Click on Save/Load
2. Paste SQL Schema from clipboard into Input/Output text area.
3. Click on (unexisting) Load SQL (or Load Input as SQL). What is the expected output? What do you see instead? The schema is loaded and ready for being manually laid out. What version of the product are you using? On what operating system? The online demo. Please provide any additional information below.

Original issue: http://code.google.com/p/wwwsqldesigner/issues/detail?id=187

[ondras]

From ondrej.zara on February 03, 2015 23:20:15

This feature sounds cool. It implies, however, having a dedicated SQL parser for every SQL dialect/database type available. Until someone contributes such (complex) code, I am afraid this is not going to happen, based on my spare time.

[bkswindell]

I could suggest using this snip it i created for now to generate a XML out of the tables only for MSSQL.

SELECT CASE WHEN C.ORDINAL_POSITION = 1 THEN CASE WHEN ROW_NUMBER() OVER(ORDER BY T.TABLE_NAME, C.ORDINAL_POSITION) > 1 THEN ''
ELSE ''
END + '

'
ELSE ''
END + CASE WHEN (C.ORDINAL_POSITION = 1 AND C.COLUMN_NAME LIKE '%id') OR K.COLUMN_NAME IS NOT NULL THEN '' + C.COLUMN_NAME + ''
+ ''
ELSE ''
END
+ '' + C.DATA_TYPE + CASE WHEN C.CHARACTER_MAXIMUM_LENGTH > -1 THEN '(' + CAST(C.CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + ')' ELSE '' END + ''
+ ISNULL('' + CAST(C.COLUMN_DEFAULT AS VARCHAR) + '','')
+ ''
+ CASE WHEN ROW_NUMBER() OVER(ORDER BY T.TABLE_NAME DESC, C.ORDINAL_POSITION DESC) = 1 THEN '' ELSE '' END AS 'XML'
,T.TABLE_NAME
,C.ORDINAL_POSITION
,C.COLUMN_NAME
,C.DATA_TYPE
,C.IS_NULLABLE
,C.CHARACTER_MAXIMUM_LENGTH
,C.COLUMN_DEFAULT
,CASE WHEN (C.ORDINAL_POSITION = 1 AND C.COLUMN_NAME LIKE '%id') OR K.COLUMN_NAME IS NOT NULL THEN 'YES' ELSE 'NO' END AS 'PK'
FROM INFORMATION_SCHEMA.TABLES T
LEFT JOIN INFORMATION_SCHEMA.COLUMNS C ON C.TABLE_NAME = T.TABLE_NAME
LEFT JOIN (SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE CONSTRAINT_NAME LIKE 'PK%' GROUP BY TABLE_NAME, COLUMN_NAME) K ON T.TABLE_NAME = K.TABLE_NAME AND C.COLUMN_NAME = K.COLUMN_NAME
WHERE T.TABLE_TYPE = 'Base Table'
ORDER BY T.TABLE_NAME, C.ORDINAL_POSITION