How to create SQL Server 2005 Database, Tables and Fields


Can anybody give me some sample on how to create the following by code in Delphi:
- add SQL Server 2005 Database
- add a table
- add different types of fields to the table

I'm using Delphi 2007 Enterprise. Connection to SQL Server is currently done via ADOConnection.
I also heared that there is a possibility to use scripts to populate a database.

Stef MerlijnDeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

An easiest way to do this is by using SQL statements that you can generate from the Microsoft SQL Server Management Studio.
Design the database (and tables) in Mgmt Studio then right click on the DB (or table) and choose 'Script Database as'&> 'Create To'&> 'Clipboard'. Then paste that SQL code into your Program and assign the text to the SQL property of a TQuery component. You can do the same with a Table.
Here's a code snippet I use to add or create a table and add fields. The fields I added in this example are Recd which is a DateTime field and a primary key, Who which is Text at 20 characters and CD Text which is 50 characters.

Again in the exmaple you must be already connected to the SQl Server and to the database.



Oh, and to create an empty database you need an ADO connection to a server and generally the command is:

 ADOCommand1.CommandText := 'Create DataBase MyDatabase';

procedure TFCDLibDBUpdate.Button1Click(Sender: TObject);
 ADOCommand1.CommandText := 'Create Table CheckOut (Recd DateTime Primary Key, Who Text(20), CD Text(50))';
   on E:Exception do
 ShowMessage('Your database was successfully updated.');

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
If I were you i would create the database using MS Enterprise Manager or other free tool and ship that with my code and then attach the database using the code I provided earlier. I would also have the empty database fully populated with all of fields, my database schema, and ready to go. I would only add tables should my application require it.

Just my preference... a lot of guys do create DBs and all that stuff on the fly form their application.


Geert GOracle dbaCommented:
SQL Server has an online help system in which these methods are described with the Enterprise manager and with Transact SQL
if you search for Transact SQL Reference you will find the msdn site at the top of the list

and for creating databases :

creating a table:

Transact-SQL is the language used by MSSQL Database
It takes a bit of reading to get used to the language, but once you master this, the world of MSSQL databases lays at your feet :)

Stef MerlijnDeveloperAuthor Commented:
Thank you all.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.