Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
bsethi24
Starting Member
25 Posts |
 Posted - 2012-03-12 : 09:38:55
|
| Dear All,Hi! I have a DB which has many tables, views, SPs, Functions, Triggers etc. Now, In many of them I have used fully qualified name (i.e. DBNAME.DBO.ObjectName).If I restore this db on any other server with some other name then the OBJECTS having fully qualified name will show me errors.So, how can I find the objects those have DBName & replace the DBName with the NEW DBName?For Example,We have a DB named "TEST" & in more than 150 SPs used the tables with fully qualified name pattern (TEST.DBO.M_Item).I took the backup of this DB & restored it on another server by name "EXAM".Now, First I need to Find how many objects have "TEST.DBO." & then replace "TEST.DBO." with "EXAM.DBO.".How can I achieve the same?Thanks & Regards,BSethi24 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-03-12 : 09:51:47
|
| If all the objects referenced in the code only refer to the database they belong to, then don't use 3-part names, just use 2 parts. If you have cross-database references that need to be maintained, then your SQL servers should maintain the same database names. You do not want to test code in a separate environment and then have to modify it to run on another, it defeats the purpose of testing it.One option you should look at is using synonyms. You can find more information here: http://msdn.microsoft.com/en-us/library/ms177544.aspxThese allow you to create permanent alias names for any object, and can span databases or even different servers. The synonym would exist in the same database as the procedure code. This way, you can change the definition of the synonym without modifying your code at all. |
 |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2012-03-12 : 15:17:05
|
| If you need to identify those procedures so you can fix them - download and install the free SQL Search utility from Redgate. It will perform the search and you can then find all of the procedures that need to be fixed.I would also recommend not using 3-part naming where the object is accessed in the same database as the procedure. For those where you need to access another database or linked server, I would definitely recommend looking at synonyms. One advantage to synonyms is that on server A your code could be referencing a local database and on server B it could be accessing a linked server - and the code actually never needs to change. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2012-03-12 : 15:41:59
|
No need to download a tool for this. This query will identify all of the objects:SELECT object_name(object_id)FROM sys.sql_modulesWHERE definition like '%TEST.%' |
|
|
|
|
|
|
|
|
Find & Replace DatabaseName in all DB OBJECTS