Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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: Yesterday @ 9:52 AM
Points: 147, Visits: 359
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: Yesterday @ 2:08 PM
Points: 12,903, Visits: 31,972
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
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: Yesterday @ 9:52 AM
Points: 147, Visits: 359
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: Yesterday @ 2:08 PM
Points: 12,903, Visits: 31,972
Ive never seen that in the options no.
You can do a find and replace very easy though

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


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
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: Yesterday @ 9:52 AM
Points: 147, Visits: 359
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: Wednesday, August 13, 2014 12:03 PM
Points: 1,480, Visits: 1,028
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