Exporting MS SQL Schema and Data

Question

I'm used to MySQL and PHPMyAdmin - I had to switch over to MSSQL for an ASP.net project, and I'm having tons of trouble. I'm using the express version of SQL 2008, with SQL Server Management Studio. The following are 2 questions I've been struggling with for a while:

1) How do I export the DB schema for the database? The table structure, etc.?

2) How do I export all the data in the database?

Ideally I'd like to have a .sql file that can be run wherever I need the schema or data duplicated, for example a co-worker's computer for a shared project, or online when the project is being hosted.

Thanks!

Answer 1

1) How do I export the DB schema for the database? The table structure, etc.?

INFORMATION_SCHEMA is your friend

 SELECT * FROM INFORMATION_SCHEMA.TABLES

http://www.mssqltips.com/tutorial.asp?tutorial=179
http://weblogs.asp.net/jgalloway/archive/2006/07/07/455797.aspx
http://preetul.wordpress.com/2009/06/09/sql-server-information_schema/

Otherwise, if you want something pretty looking, download the 14 day trial of SQL Doc (part of SQL Toolbelt) here: http://www.red-gate.com/products/SQL_Professional_Toolbelt/index.htm

"2) How do I export all the data in the database?"

In what form? .bak files are typically the most useful. http://www.sqlteam.com/article/backup-and-restore-in-sql-server-full-backups Or were you looking to move the data into MYSQL or Excel or some other program? If you want to move data to MYSQL check here: http://www.google.com/search?q=mssql+to+mysql