I am currently trying to write a script that copies certain tables and procedures from my prod db instance to my dev db instance (I do not want to copy the entire db from prod to dev). I am using SQL Server. Assume that the masking of sensitive data will be handled properly. I want to copy only the following from prod to dev:
- The entire db definition/schema including all constraints for the specified tables.
- I also want to copy all of the procedures from prod to dev.
I was able to achieve part 2 by using the built-in sp_helptext '<procedure-name>' stored procedure like so:
prod_cursor.execute("sp_helptext '<procedure-name>'")
rows = prod_cursor.fetchall()
definition = ''.join(row[0] for row in rows)
dev_cursor.execute(definition)
dev_cursor.commit()
I know how I can copy the data from prod to dev but I am kind of stuck on how to copy the entire table definition/schema. Ideally, I want to be able to reproduce the text that is outputted when you right click on a table in Azure Data Studio and then click "Script as Create" like so:

Ideally, I want to be able to write a script in python to handle all of this functionality for me since I only want to replace certain tables and procedures but not the entire db instance. Let me know if I am going about this wrong or if there are any tools out there that already exist tat can help me automate this process.