SQL script to HTML
Python file: Export_MPD.py
# Purpose: Generate an HTML file listing descriptions of tables in a SQL Server database
# from a base creation script (exported from SQL Server Management Studio)
# Input file: BaseTest.SQL
# Output file: BaseTest.HTML
def getHtmlHead():
return """<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
</head>
<body>
<font size="4" color="black"><u><b>"""
def transform(source, target):
inFile = open(source, 'r')
outFile = open(target,'a')
while 1 :
tLine = inFile.readline()
if tLine == '':
break
elif 'CREATE TABLE [dbo].[' in tLine:
tLine = tLine.replace('CREATE TABLE [dbo].[','')
tLine = tLine.replace('](','')
htmlText = getHtmlHead() + 'Table: '+tLine
outFile.write(htmlText + """</b></u></font>
<table border="1">
<col width="150">
<col width="150">
<col width="250">
<col width="200">
<tr>
<th bgcolor="#C0C0C0">Column name</th>
<th bgcolor="#C0C0C0">Column information</th>
<th bgcolor="#C0C0C0">Description</th>
<th bgcolor="#C0C0C0">Comment</th>
</tr> """)
while 1 :
tLine = inFile.readline()
if ') ON [' in tLine or 'CONSTRAINT [' in tLine:
break
else:
tLine = tLine.replace('] [',';')
tLine = tLine.replace(']','')
tLine = tLine.replace('[','')
tLine = tLine.replace('IDENTITY(1,1)','Auto Increment')
tLine = tLine.replace(',','')
tLine = tLine.replace('Auto Increment', 'IDENTITY(1,1)')
tLine = tLine.replace('numeric(18 2)', 'numeric(18,2)')
tLine = tLine.replace('numeric(18 5)', 'numeric(18,5)')
item = tLine.split(';')
try:
columnName = item[0]
columnType = item[1]
columnExtra = ''
except:
columnName = 'Error'
columnType = 'Error'
columnExtra = ''
outFile.write("""<tr>
<td>""" + columnName + """</td>
<td>""" + columnType + """</td>
<td></td>
<td></td>
</tr>""")
outFile.write("""</table>
<br />""")
outFile.write("""</body>
</html>""")
inFile.close()
outFile.close()
# ==============================================================
workPath = 'C:/Code/'
aBase = 'BaseTest'
inFile = workPath + aBase + '.sql'
outFile = workPath + aBase + '.html'
transform(inFile, outFile)
***************************************************************************
Extract of Input file: BaseTest.SQL
...
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Contact](
[ID] [int] NULL,
[Nom] [varchar](50) NULL,
[Prenom] [varchar](50) NULL
) ON [PRIMARY]
GO
/****** Object: Index [ClusteredIndex-20181006-222845] Script Date: 06/10/2018 22:33:45 ******/
CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex-20181006-222845] ON [dbo].[Contact]
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Pays] Script Date: 06/10/2018 22:33:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Pays](
[ID] [int] NULL,
[Nom] [varchar](50) NULL
) ON [PRIMARY]
GO
USE [master]
GO
ALTER DATABASE [Testlou] SET READ_WRITE
GO
***************************************************************************
# Output file: BaseTest.HTML
Table: Contact
Table: Pays
***************************************************************************
Files download links :
# Purpose: Generate an HTML file listing descriptions of tables in a SQL Server database
# from a base creation script (exported from SQL Server Management Studio)
# Output file: BaseTest.HTML
def getHtmlHead():
return """<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
</head>
<body>
<font size="4" color="black"><u><b>"""
def transform(source, target):
inFile = open(source, 'r')
outFile = open(target,'a')
while 1 :
tLine = inFile.readline()
if tLine == '':
break
elif 'CREATE TABLE [dbo].[' in tLine:
tLine = tLine.replace('CREATE TABLE [dbo].[','')
tLine = tLine.replace('](','')
htmlText = getHtmlHead() + 'Table: '+tLine
outFile.write(htmlText + """</b></u></font>
<table border="1">
<col width="150">
<col width="150">
<col width="250">
<col width="200">
<tr>
<th bgcolor="#C0C0C0">Column name</th>
<th bgcolor="#C0C0C0">Column information</th>
<th bgcolor="#C0C0C0">Description</th>
<th bgcolor="#C0C0C0">Comment</th>
</tr> """)
while 1 :
tLine = inFile.readline()
if ') ON [' in tLine or 'CONSTRAINT [' in tLine:
break
else:
tLine = tLine.replace('] [',';')
tLine = tLine.replace(']','')
tLine = tLine.replace('[','')
tLine = tLine.replace('IDENTITY(1,1)','Auto Increment')
tLine = tLine.replace(',','')
tLine = tLine.replace('Auto Increment', 'IDENTITY(1,1)')
tLine = tLine.replace('numeric(18 2)', 'numeric(18,2)')
tLine = tLine.replace('numeric(18 5)', 'numeric(18,5)')
item = tLine.split(';')
try:
columnName = item[0]
columnType = item[1]
columnExtra = ''
except:
columnName = 'Error'
columnType = 'Error'
columnExtra = ''
outFile.write("""<tr>
<td>""" + columnName + """</td>
<td>""" + columnType + """</td>
<td></td>
<td></td>
</tr>""")
outFile.write("""</table>
<br />""")
outFile.write("""</body>
</html>""")
inFile.close()
outFile.close()
# ==============================================================
workPath = 'C:/Code/'
aBase = 'BaseTest'
inFile = workPath + aBase + '.sql'
outFile = workPath + aBase + '.html'
transform(inFile, outFile)
***************************************************************************
Extract of Input file: BaseTest.SQL
...
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Contact](
[ID] [int] NULL,
[Nom] [varchar](50) NULL,
[Prenom] [varchar](50) NULL
) ON [PRIMARY]
GO
/****** Object: Index [ClusteredIndex-20181006-222845] Script Date: 06/10/2018 22:33:45 ******/
CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex-20181006-222845] ON [dbo].[Contact]
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Pays] Script Date: 06/10/2018 22:33:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Pays](
[ID] [int] NULL,
[Nom] [varchar](50) NULL
) ON [PRIMARY]
GO
USE [master]
GO
ALTER DATABASE [Testlou] SET READ_WRITE
GO
***************************************************************************
Table: Contact
| Column name | Column information | Description | Comment |
|---|---|---|---|
| ID | int NULL | ||
| Nom | varchar(50) NULL | ||
| Prenom | varchar(50) NULL |
Table: Pays
| Column name | Column information | Description | Comment |
|---|---|---|---|
| ID | int NULL | ||
| Nom | varchar(50) NULL |
***************************************************************************
Files download links :
Commentaires
Enregistrer un commentaire