Solved

Search SSMS script for references to any tables in the database.

Posted on 2014-03-30
9
369 Views
Last Modified: 2014-03-31
I hope this is an easy one. I can execute the procedure sp_tables on a database and get a list of all possible tables that could be used in a script. I want to take this list and compare it against a SQL script that has 5000+ lines so that I can essentially change the references in the script from

TableName to  

[database].[dbo].[TableName] or other combinations

I really need to do it just like this. There may be other ways to accomplish "the same thing" but I really want to do this particular thing. List A has keywords List B has text be searched on.

I am not adverse to buying a power editor. I already have Ultraedit so that or free would be preferred. If you know of a product don't just point me to a link. Show me how it works or where to find the information to make it work.

Thanks
0
Comment
Question by:mike1142
9 Comments
 
LVL 35

Expert Comment

by:David Todd
Comment Utility
Hi,

SSMS has a find and replace function that is under ctrl+H or the edit menu (Edit | Fund and Replace | Quick Replace)

Now you may be wanting to do something a bit more complex than that, but taking what you said and face value, you can do a search and replace over a selection, open document, all open documents, etc ) and replace tablename1 with somedatabase.dbo.tablename1

HTH
  David
0
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
0
 

Author Comment

by:mike1142
Comment Utility
No, not even close. I know search and replace even across files. The problem is that the sp I am looking to edit is written for its dbo. I want to execute it in another db so I need to change references from 'TableName' or 'dbo.TableName' to a fully qualified table name.

Thanks
0
 
LVL 68

Accepted Solution

by:
Qlemo earned 500 total points
Comment Utility
Are you open to PowerShell scripts?
Do you really enforce having to change the SP? There are means to execute a stored procedure within a different DB or schema.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:mike1142
Comment Utility
I am looking for portability. I work with this vendors database product with many customers and the database name is slightly different at some and I want an easy way to convert from one to the other. Once the script(s) are changed to use fully qualified a simple search and replace is possible.

The scripts as well as enhancements and modifications use a separate database as best practice to keep the original db structure intact. So read-only to the main database and queries from the custom.

There may be another way and I am interested but this is not the only use case. There is also a "missing table" scenario and a "must have these tables" scenario. which are similar.
0
 
LVL 25

Expert Comment

by:jogos
Comment Utility
A change all is always tricky, which order you want to change? What if there is an Order-table do you concider variations like dbo.order, dbo.[order], [dbo].[order] or will you also change every string 'order'.  You say table names but are views, procedures or UDF also possible?
Combinations are infinite if you ask me.

A safer way depending on in which circumstances you want to use this
a)
Concider the sql cmd utility and using a variable (commandline or :setvar ) to inject the variable part in your sql.   Then make a script that prefixes every object-reference with the db-reference
http://technet.microsoft.com/en-us/library/ms188714.aspx

b) Work with the content of sys;sql_modules
You can get the procedure definition of the reference-db and create it in the other database so everey reference will be to objects in the same database. The execution will need rights to create/delete procedures in the  db's you want to query.

An example on how it is used just to query for strings in a procedure
http://sqlserverquestions.mssqltips.com/8704/to-know-the-procedures-in-which-insert-into-tablename-which-is-used/
0
 

Author Closing Comment

by:mike1142
Comment Utility
http://tangodude.wordpress.com/2013/04/15/powershell-multiple-find-replace-in-files-with-lookup-list/

Thanks for the point in the right direction did not thinking of powerscript. Did exactly what I wanted.
0
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
Great find, and exactly what I had in mind ;-).
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Viewers will learn how the fundamental information of how to create a table.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now