0

I have a huge database. I got script of my database schema using sql server management studio.

Script contains 176,000 lines. When I copied script to new query window and executed it. It says

1. Incorrect syntax near 'GO' => This error repeats after 90% of error lines 
2. Must declare the scalar variable "@abc"
3. The variable name '@sql' has already been declared. Variable names must
be unique within a query batch or stored procedure
4. Foreign key 'FK_FAVOURITES_DETAIL_FAVOURITES' references invalid table
 'dbo.FAVOURITES'
5. Cannot find the object "dbo.LIC_INFO" because it does not exist or you
do not have permissions

According to my expectation database could not have existed with invalid objects/relations or a procedure having synntax error in its definition

Is management studio limited in capability to generate a particular length of script correctly or to run query batch of particular length or it can fail against particular script (e.g) dynamic sql in procedures or user defined datatypes

Or what could be something wrong with the process I followed?

Sami
  • 8,168
  • 9
  • 66
  • 99

1 Answers1

0

SQL Server Management Studio (SSMS) is not great at producing scripts, in particular it does not do a very good job of determining the order in which objects must be created in order to satisfy dependencies. As soon as you have one error the chance of further errors increases dramatically.

With regards to your expectation that "database could not have existed with invalid objects/relations or a procedure having syntax error in its definition" - this is not correct. There are a number of ways in which invalid objects can exist in a database.

Depending on how you created your script you might want to take a look at the Tools menu, Options, SQL Server Object Explorer, Scripting and review the settings there.

Rhys

Rhys Jones
  • 5,348
  • 1
  • 23
  • 44