Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to stop SSMS from adding database name to scripts Expand / Collapse
Author
Message
Posted Thursday, April 18, 2013 2:31 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 13, 2016 11:49 AM
Points: 186, Visits: 444
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
Post #1444139
Posted Thursday, April 18, 2013 2:37 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:46 PM
Points: 14,469, Visits: 38,074
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!
Post #1444143
Posted Thursday, April 18, 2013 4:45 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 13, 2016 11:49 AM
Points: 186, Visits: 444
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
Post #1444188
Posted Friday, April 19, 2013 4:29 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:46 PM
Points: 14,469, Visits: 38,074
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!
Post #1444300
Posted Friday, April 19, 2013 10:44 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 13, 2016 11:49 AM
Points: 186, Visits: 444
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
Post #1444516
Posted Friday, April 19, 2013 11:14 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Tuesday, August 16, 2016 3:59 PM
Points: 1,487, Visits: 1,076
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.

Post #1444533
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse