0

I want to dump one SQL Server database - get all SQL code necessary to create a similar database. I have full online rights to DatabaseA, I can feed it with SQL and get the results back in rows in a table.

I do not have the possibility to use Enterprise Manager, any applications, utilities or the like. I can only feed it with pure SQL.

What I am after is SQL code, like CREATE TABLE and so on. So that I just can paste this into a query and voila - tables, procedures, functions are created in DatabaseB.

I will not copy the data.

This partly does what I want, it gives me procedures and functions:

Select object_Name(object_ID),definition from sys.SQL_Modules

But not for tables.

cadrell0
  • 17,109
  • 5
  • 51
  • 69
user1221510
  • 11
  • 1
  • 1
    You will need to use either enterprise manager, ssms, or some other tool like SMO through powershell or another framework. Table definitions are too complicated to try to parse out with pure TSQL, as are indexes. – JNK Feb 20 '12 at 16:39
  • 1
    The Enterprise Manager also uses plain T-SQL to get all the schema information. So you could start the SQL Profiler and investigate which queries the Enterprise Manager generates. This will give you some clues about the queries you need. – Elian Ebbing Feb 20 '12 at 16:41
  • Thanks, as I wrote, I **cannot** use the Enterprise Manager. I do not have any acces to the machine where the database server is running. I only have access through a web page where I can enter SQL code and the get the results in rows in a table. – user1221510 Feb 20 '12 at 16:54
  • @Elian - good idea. It would be nice if someone already has this code – user1221510 Feb 20 '12 at 16:59
  • 1
    The best way to do this is to auto-gen the DDL you want through a tool. Why the moratorium on tools? – John Dewey Feb 20 '12 at 17:33
  • @John Dewey agreed entirely. Let's spend 80 man-hours re-engineering something that's been solved and QA'd and handles dozens of scenarios we haven't even dreamed of yet by a $300 tool. (And some cheaper / free ones that do a half decent job too.) http://www.onetooneglobal.com/otocorporate-posts/2009/06/01/the-cost-of-reinventing-the-wheel/ – Aaron Bertrand Feb 20 '12 at 17:50
  • Considering that you were given access by Web only I guess you weren't meant to get access to database structure.. and be able to recreate it somewhere else? Seems a bit fishy. – MadBoy Feb 20 '12 at 17:59
  • @AaronBertrand lol. 80 hrs is nothing. I've seen entire man-years go up in smoke on similar misadventures. – John Dewey Feb 20 '12 at 18:05
  • Hi everyone, I really appreciate all your help! I just want to say that I have some of the tools you mention. The reason I posted this question, the reason I come here, is that it is a special case, I **do not** have access to the machine, so I cannot use any tool at all. I am not after the data, just the schema and the SQL code to create all tables, procedures and functions. – user1221510 Feb 20 '12 at 20:25

2 Answers2

0

You can use the command line or you can create a stored procedure to create a back up, then use that backup to create a new database. I have used the command line often. Here is a previous Stack question that has a command line example and a link to a stored procedure example.

Community
  • 1
  • 1
northpole
  • 10,244
  • 7
  • 35
  • 58
  • Hi, the command line will not help me because I have no access to the server machine at all. As I wrote, I can get fix and ready code to create functions and procedures using this: Select object_Name(object_ID),definition from sys.SQL_Modules So I thougth there has to be a way to do the same for tables – user1221510 Feb 20 '12 at 16:55
  • take a look at the stored procedure example then, will that be an option? – northpole Feb 20 '12 at 17:00
-1

You can generate scripts in SQL Server Management Studio for an entire database or specific database objects.

To do this, right click the database then select Tasks then select Generate Scripts.

It will then open a wizard which will give you the option to choose to script the full database or just specific database objects.

Vince Pergolizzi
  • 6,396
  • 3
  • 19
  • 16
  • Thanks, but did you read my post? I cannot use any "Studio" or "Manager", since I only have access to the SQL Server via a web page. On this page I can post any SQL I want, since I have all user privileges. – user1221510 Feb 20 '12 at 19:11