SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to stop SSMS from adding database name to scripts


How to stop SSMS from adding database name to scripts

Author
Message
tshad
tshad
Old Hand
Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)

Group: General Forum Members
Points: 344 Visits: 454
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
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28034 Visits: 39925
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
tshad
Old Hand
Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)

Group: General Forum Members
Points: 344 Visits: 454
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),>Wink
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
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28034 Visits: 39925
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
tshad
Old Hand
Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)

Group: General Forum Members
Points: 344 Visits: 454
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
Ray M
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2129 Visits: 1076
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search