[Python] How do I generate a table's DDL programmatically in SqlServer?

I am trying to generate a DDL of a table in SQL Server using Python. I tried to run the statement: show create table schema.table which didn't work. So I explored online for some help and found out that we can run the proc: sp_help as exec sp_help schema.tablename. One of the reason I am trying to generate the DDL is to get the DDL and create them in my target DB, (it is in snowflake & I am writing code to create the tables as well) get the PRIMARY & UNIQUE KEYS from the table and use them in my code (some requriement).

When I run the show create table as below, I get syntax error as below:

import pyodbc
cnxn = pyodbc.connect("Driver={SQL Server Native Client 11.0};"
"Server=servername;"
"Database=dbname;"
"uid=user;pwd=pwd")
cursor = cnxn.cursor()
cursor.execute('show create table dbname.tablename')


Error:

pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near 'tablename'. (102) (SQLExecDirectW)")


So I ran sp_help in the Sql Server management studio as below:

exec sp_help 'dbo.tablename';


The result:



In the image the proc: sp_help returns lot of data and column names are present in the second table of the result & the key constraints are present in the last table of the result.

All of this result is found in Sql Server Studio. So I ran the proc in python as below:

>>> import pymssql
>>> conn = pymssql.connect(host='Server\\Server',user='user', password='pwd',database='dbname')
>>> cur = conn.cursor()
>>> cursor.execute("exec sp_help 'dbo.tablename'")


But if I iterate the result from cursor as below:

>>> for r in cursor:
... print(r)
...


I only see the first table in the result:

('tablename', 'dbo', 'user table', datetime.datetime(2020, 8, 27, 9, 51, 11, 213000))


This result is the first table from the result in the image given above.

The loop doesn't print other details that are shown from the image and I don't understand how can I implement loop properly to print/access the other data that was shown in the image above. Is there anyway I can get the DDL from SQL Server in Python ? Any help is appreciated.

Continue reading...