Re: Sql Management Studio - Script Db creates objects out of ourder

Andrew Robinson (nemoby@xxxxxxxxxxxxx) writes:
Don't know if this is the best place to post this but I have a db with
about 100 tables and a number of views and sprocs. When scripting the
entire db using SMS, a number of the views and sprocs are created before
their associated underlying tables are. Can anyone shine a bit of light
on this issue? I am guessing that it is a common one since I have seen
it a number of times.

I have also tried the new Database Deployment Wizard RC and it generates
scripts with the same issue. Guessing that maybe this has something to do
with my db?

Scripting depends on the dependency information in the database. Given the
flexible nature of SQL Server, this information may be incomplete. If you
create a table nissetbl, and the a stored procedure kalle_sp that refers
to nissetbl, SQL Server with store information about this. But if you
now drop and recreate nissetbl, this information is lost. When you recreate
nissetbl, it would be kind of expensive to examine 5000 stored procedures
to see if they might refer to nissetbl. Also, keep in mind that SQL Server
to permits you to create kalle_sp before nissetbl exists at all.

Thus, the only way to get you generated script correctly is to generate
the database correctly from the beginning. ...or at least it was up to
SP2 of SQL 2005, which introduces a system procedure, sp_refreshsqlmodule
which updates the dependency information for a stored procedure or
similar.

SP2 for SQL 2005 has not been released yet, but a preview (aka CTP) is
available.


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: How to Create Local Temporary Table
    ... I have worked with both Oracle and SQL Server for some time and have grown to appreciate how much more mature Oracle is than SQL Server. ... When most think of scripting in Windows, they think of DOS command line scripting. ...
    (comp.databases.oracle.server)
  • Re: Scripting Performance after SP2 applied?
    ... I agree that there is a lot of time spent setting up the scripting in the ... As far as memory configuration, I have always set my max server memory set ... now with forced parameterization, ... I then restarted SQL Server, and set the database to forced ...
    (microsoft.public.sqlserver.tools)
  • Re: SMSS SP2 Script Database objects error
    ... Determining objects in database 'NameHost' that will be scripted. ... query using ANSI outer join operators (LEFT OUTER JOIN, ... In the future versions of SQL Server, ... scripting, I find it difficult to believe that it would be that bad. ...
    (microsoft.public.sqlserver.tools)
  • Re: Any good, free tools for writing simple stored procedures?
    ... I use SQL Server Query Analyzer most of the time. ... but it does have a debugger, object browser, scripting, graphical query ... from the object browser into the editing window. ...
    (microsoft.public.sqlserver)
Quantcast