Generate SQL

Hi. My host cut off all connectivity to MS SQL server via E nterprise Manager, Web Matrix etc. Because of mis use by some of their customers. And They provided a web based db admin title ASP.Net Enterprise Manager for us to use. This program is fine but I need the functionality of MS EM. So I installed MSDE on my workstation to develop and I am going to upload to the server after testing my apps. Now I was wondering if there is a way to generate SQL (like the generate sql function is MS EM) in a SQL statement so that I can replicate my already create db tables in MSDE to build my testing model easily. Or am I just stuck re-creating everything in MSDE again. Also is there a better version of a web based MS SQL db manager out there with compariable functionality as MS EM. Thanks

You would have to write the SQL Statements using DDL.

I understand that if you mean to re-create the tables in my MSDE development db. But how does that apply to generating the Create Table script for my already existing db tables. I am probably missing somthing. Please explain further

Are you wanting to create new objects, such as tables, etc.. through a script that is executed against a database?

Here is the scenario. I have a db created it has a total of 8 tables I am still adding to this db because I am still developing apps. What I want to do is generate the sql for my existing db so I can copy and paste it in Query Analizer to easily re-create my existing in the MSDE development db I am going to use on my workstation. This way I can still design triggers, and maintain the keys and constraints via the db diagram (which are things I cannot do with ASP.NET enterprise manager). Since I cant connect to my db via Enterprise Manager on my workstation I lose the ability to create triggers, diagrams etc. So now I have to develop in my environment and the upload to the server. I hope this explains the situation.

You would have to script out all of the new objects, then write an ASP page that will read those scripts and execute them against the database.

There is no way to write a script that will do the same job as Enterprise Managers "Generate SQL Script" function?

You can write the script yourself.

You would save this to a file (.txt or .sql or whatever).
Then just open the database connection, create an instance of the FileSystemObject, read the file, save the contents to a variable, then execute the variable against the database connection.

Thanks Memnoch I will give it a try

Memnoch as I am trying to get this sp to work I started wondering is this example you gave me to extract the data from my existing db, or to generate the create table, SQL, PK, FK, Constraints etc?

It's just a sample Stored Procedure that would be created within the database.