Hi I'm new at SQL, I must have your thinking regarding guidelines to consider in SQL scripting to set up an information Base.
PROBLEM A) During my script I've several Batches to produce Tables. Tables have numerous Foreign Secrets to every others, right now I have to arranges batches within the right to avoid conflict with FK Tables. I must determine if might be a sound practice create Tables and all sorts of posts without FK first, and also at the finish from the script ALTER such tables adding FK.
PROBLEM B) My script ought to be use to produce different DB on different Servers. Database might have different title on every installation. Now during my script I produce a Database using:
CREATE DATABASE NameX
for doing things.
Because I'd need update by hand the script for each installation. I believed could be great to possess a CENTRALIZED method for naming the information Base in the the script. In by doing this altering an easy variable would produce the Database with my title and all sorts of USE claims. I attempted to make use of LOCAL VARIABLES, but with no success because after GO claims they walk out scope. I have no experience of using sqlcmd and variables there.
Any idea how you can solve it within my script? PS: I personally use MS SQL 2008 and that i will load my script in MS SMS
Thanks men for the help, this community is excellent :-)
- stay away from "USE DATABASE"
- separate the database creating script and data object creating scripts
- apply certain code (Setup, Deploy) to complete creating database script by changing @database_title with real title
- apply certain alternative tool to organize scripts before deploy (it simply replace your @@@database_title@@@ with real title)
- use softball bat file to organize scripts
- use Database Project within the Visual Studio. Versus can generate some variables that setup projects can alter within the deploy process..
Normally one begins with scripting all of the tables, then the FK scripts, index scripts and also the relaxation. This really is normal practice, while you can't add associations to tables that aren't there...
For your next problem - there's not a way I know of for centralizing this. The best choice is really a global search/replace from the database title on open files in SSMS.