Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / DB Engine / SQL Server / July 2008

Tip: Looking for answers? Try searching our database.

script all database objects including table without data in sql 20

Thread view: 
Enable EMail Alerts  Start New Thread
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
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.