?
Solved

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

Posted on 2014-03-30
9
Medium Priority
?
383 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 39965887
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 70

Expert Comment

by:Éric Moreau
ID: 39966186
0
 

Author Comment

by:mike1142
ID: 39966701
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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 70

Accepted Solution

by:
Qlemo earned 2000 total points
ID: 39966830
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
 

Author Comment

by:mike1142
ID: 39966864
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
ID: 39967565
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
ID: 39967916
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 70

Expert Comment

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

Featured Post

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In threads here at EE, each comment has a unique Identifier (ID). It is easy to get the full path for an ID via the right-click context menu. However, we often want to post a short link within a thread rather than the full link. This article shows a…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
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.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

770 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