How to stop SSMS from adding database name to scripts

Question

Post reply

How to stop SSMS from adding database name to scripts

tshad

SSCertifiable

Points: 5905
More actions
April 18, 2013 at 2:31 pm

#276688

Is there an option to prevent SSMS from adding the Database Name to scripts it generates?
For example, if I do a right click on a table in the Object Explorer then select "Script Table as"INSERT To", it will create the insert statement with the database on it. That has caused me headaches when I create it and use it in my stored procedure and don't notice it. I would never want to do that.
I have the same issue with Stored Procedures. When you do a modify, it always puts a "USE Database" at the top. I need to get rid of that one to for the same reason.
Thanks,
Tom

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

just about everything is an option when it comes to scripting.

in SQL2008 SSMS, go to Tools>>Options>>expand SQL Server Object Explorer>>Scripting.

one of the options is "Script USE <database>

in your case, set it to false.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

tshad SSCertifiable Points: 5905 More actions · Answer 2

That handled that issue.

Is there a setting for the adding the database to the Insert or Update scripts:

INSERT INTO [MyDEV].[dbo].[District]

([DistrictCode]

,[DistrictName]

VALUES

(<DistrictCode, varchar(32),>

,<DistrictName, varchar(128),>)

GO

I don't want the "[MyDev]." to be added when generating this script (or update and delete).

I can't seem to find it in the section you mention.

Thanks,

Tom

Lowell SSC Guru Points: 323504 More actions · Answer 3

Ive never seen that in the options no.

You can do a find and replace very easy though

[INSERT INTO ] --> [INSERT INTO MYDB.]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

tshad SSCertifiable Points: 5905 More actions · Answer 4

I have no problem getting rid of it.

My problem is that I don't ever want the scripter to add the database name. It never should. Otherwise you couldn't move it from database to database (Dev to Stage to Production).

This is my problem.

If you don't get rid of it or miss one then your programs will start crashing. In my case, I missed one and it looked like it was working in Dev and in QA (the database referenced was DEV). And since the Dev database was on the same server as stage, it still worked (or seemed to). We didn't see any errors and the data was similar.

But when we moved our data to the customer and they put it on their database, they didn't have the Dev database so the programs crashed.

I absolutely cannot see a reason to script the database name onto the table name. If you need to access a different database, then you add it.

Thanks,

Tom

Ray M SSC-Insane Points: 21093 More actions · Answer 5

Looks like that functionality is SSMS 2008.

I started using SSMS 2012 a while back and it does not do this. SSMS 2012 is fully compatible with 2008 instances, I'd advise using it.

Cracked open 2008 again, and sure enough it adds db name.

I could not find anything to turn this off.

Additionally I've been using SQL Prompt for Scripting and it doesn't do this either.