SQL Server Forum / DB Engine / SQL Server / July 2008
script all database objects including table without data in sql 20
|
|
Thread rating:  |
sg - 28 Jul 2008 18:31 GMT How to script all objects in sql 2000 without data?
thanks, Sarah
Roy Harvey (SQL Server MVP) - 28 Jul 2008 20:41 GMT >How to script all objects in sql 2000 without data? Open Enterprise Manager and locate the database in the tree. Right-click the database, choose All Tasks... Generate SQL Script... That opens the Generate SQL Scripts window.
On the first tab press Show All, then select Script all objects. On the Formatting and Options tabs make the appropriate choices for what you want to do. Then click OK.
Roy Harvey Beacon Falls, CT
Aaron Bertrand [SQL Server MVP] - 28 Jul 2008 20:46 GMT Heh, I read the question a little more literally... I thought the OP wanted to tell Management Studio that they wanted a script that included only those tables/views that did not contain any rows. :-)
>> How to script all objects in sql 2000 without data? Roy Harvey (SQL Server MVP) - 28 Jul 2008 20:51 GMT >Heh, I read the question a little more literally... I thought the OP wanted >to tell Management Studio that they wanted a script that included only those >tables/views that did not contain any rows. :-) > >>> How to script all objects in sql 2000 without data? Of course you we are probably BOTH wrong. 8-)
Russell Fields - 28 Jul 2008 20:50 GMT Sarah,
If you are using SQL Server 2000 Enterprise Manager, expand the database list, right click on the database you want to script, and choose:
All Tasks / Generate SQL Script ...
Answer the questions according to your needs and go.
If you are using SQL Server 2005 Management Studio do the following two steps after right clicking on the database: 1. Script Database As / Create to ... 2. Tasks / Generate Scripts ...
Again, there are questions to answer to get the objects and settings that you want.
Also, FWIW, I use Red-Gate tools which do an excellent job on this sort of thing. There are some other tools, APEX, SQL Delta, xSQL, etc.
RLF
> How to script all objects in sql 2000 without data? > > thanks, > Sarah sg - 29 Jul 2008 13:41 GMT Thanks Roy, Aaron and Russell for your response.
I tried both 2000 and 2005 method. After I got the script, I ran it on another server (same version of sql), it gave me so many errors. Should I create the database before running the script?
Thanks, Sarah
> Sarah, > [quoted text clipped - 22 lines] > > thanks, > > Sarah Roy Harvey (SQL Server MVP) - 29 Jul 2008 14:22 GMT >I tried both 2000 and 2005 method. After I got the script, I ran it on >another server (same version of sql), it gave me so many errors. Should I >create the database before running the script? You asked about scripting database objects. Objects only exist within the context of a database, a database is assumed.
The script generation tool in Enterprise Manager (SQL Server 2000) has an option on the third tab to script the database creation. (I did not go look for the exact location in 2005 but I am sure it is there.) Personally I would not include it because such things as file locations have to be considered individually by server.
Roy Harvey Beacon Falls, CT
Russell Fields - 29 Jul 2008 14:23 GMT Sarah,
Using SS2000 EM, you need to select the CREATE DATABASE check box to include that in the script. Using SS2005 MS, you need to run the CREATE DATABASE script (step 1) first, then the objects script (step 2).
Or, of course, you can create the database first.
RLF
> Thanks Roy, Aaron and Russell for your response. > [quoted text clipped - 32 lines] >> > thanks, >> > Sarah sg - 29 Jul 2008 14:46 GMT Hello Russell and Roy,
Thanks so much, now I have better understanding of it. Is there a script or method that I can use to scripts all the stored procedures out on regular basis? This is for archive the development.
Thanks and sorry for too many silly questions:( Sarah
> Sarah, > [quoted text clipped - 43 lines] > >> > thanks, > >> > Sarah Russell Fields - 30 Jul 2008 17:01 GMT Sarah,
It is possible to write some code using one of the APIs (DMO, SMO) to do this, but a simple way is to create a script that uses a curson to run sp_helptext against each stored procedure (and function?) in the database. You could run it from SQL Agent as an operating system step:
SQLCMD -S ServerName -E -i ScriptFile.sql -o OutputFile.txt
Of course, this produces a single long text file with all of the output, not files for each discrete object. The script would look something like this:
DECLARE @ObjectName NVARCHAR(128) DECLARE @Command NVARCHAR(500)
DECLARE ObjectToScript CURSOR STATIC FOR SELECT name FROM master.dbo.sysobjects -- SS2000 compat -- All members created today. WHERE type = 'P'
OPEN ObjectToScript ;
WHILE (1=1) BEGIN FETCH NEXT FROM ObjectToScript INTO @ObjectName ;
IF @@FETCH_STATUS <> 0 BEGIN PRINT 'ALL DONE' BREAK; -- No more data, exit the loop END IF @ObjectName LIKE '%\%' -- Example test when not to create this login BEGIN CONTINUE ;-- Nothing to do here, back to top of the loop END -- We have a valid object, script it out SET @Command = 'sp_helptext [' + @ObjectName + '] ' EXEC (@Command) END
CLOSE ObjectToScript DEALLOCATE ObjectToScript
Red-Gate, Apex, xSQL, etc all have command line tools (which you have to buy) that would go a better job of scripting out the objects (including security settings) than something that you make yourself. In addition, if you created a reference database with the last state of the objects, you could compare the current database against your reference database and get only the objects that have changed. (You can do all of this yourself, but why bother when you can buy it and have vendor support.)
RLF
> Hello Russell and Roy, > [quoted text clipped - 59 lines] >> >> > thanks, >> >> > Sarah Russell Fields - 30 Jul 2008 19:51 GMT Hmm, looks like I pasted an early uncleaned up version. Edited to reduce confusion:
DECLARE @ObjectName NVARCHAR(128) DECLARE @Command NVARCHAR(500)
DECLARE ObjectToScript CURSOR STATIC FOR SELECT name FROM master.dbo.sysobjects -- SS2000 compat -- All stored procedures WHERE type = 'P'
OPEN ObjectToScript ;
WHILE (1=1) BEGIN FETCH NEXT FROM ObjectToScript INTO @ObjectName ;
IF @@FETCH_STATUS <> 0 BEGIN PRINT 'ALL DONE' BREAK; -- No more data, exit the loop END IF @ObjectName LIKE 'dt[_]%' -- Example test when not to script an object BEGIN CONTINUE ;-- Nothing to do here, back to top of the loop END -- We have a valid object, script it out SET @Command = 'sp_helptext [' + @ObjectName + '] ' EXEC (@Command) END
CLOSE ObjectToScript DEALLOCATE ObjectToScript
> Sarah, > [quoted text clipped - 114 lines] >>> >> > thanks, >>> >> > Sarah
|
|
|